I already have an exam database which I use in the school where I teach. It works fine and I am trying to add an extra functionality. (To be able to produce a template in the form of a report for students to fill in how many marks they get in each question and guiding them towards extra support for questions which they didn't get right)
The problem is as follows:
There are (given the infiniteness of time) an infinite number of test papers we will use as we use the most up to date ones we can.
I'd like to store the following in the database.
Exam paper number (integer) unique (this is actually already stored)
How many marks each question is out of (integer)
What topic each question covers (string)
Where students can access assistance to revisit the topic
I thought the best layout might be as follows
TblPaperBreakdown
PaperNumber
Q1Marks
q1TopicID
Q2Marks
Q2TopicID
q3marks
q3topicID
(up to a total of 25 questions)
TblAssistance
TopicID
TopicAssistance
tblTopic
TopicID
Topic name
It seems fine but the first table having so many fields whichare so similarwith so many primary keys linked to Topic ID bothers me
I wondered whether a table for each paper might be more suitable.
Can anyone advise please?