Hey,

I'm at the stage of planning for a database to store scores of test marks for English, Math and Science for a class of students.

Among the tables I've planned are three tables corresponding to the three subjects to store the test id, the score, the question numbers in which wrong answers were given and the date the test is taken: (note that the member_id is a foreign key in the three tables below but a primary key in a student_tbl):

english_tbl
Member_id (FK)
Test_id
Score
Wrong question IDs
Date Taken

math_tbl
Member_id (FK)
Test_id
Score
Wrong question IDs
Date Taken

science_tbl
Member_id (FK)
Test_id
Score
Wrong question IDs
Date Taken

Please pardon me if my question sounds elementary. I'm new to databases and although I've read a couple of books on the subject, I'm still groping in the dark when it comes database design.

And what question, pray tell, is that?

I'll guess that you're asking if that's a good way of doing it??

If it were me, I'd have a table for courses, a table for students, and a table called student_test that held the info for each test taken, whatever subject it was in.

Table StudentTest

studentID (fk)
testID (fk)
mark
dateTaken

I would make a unique key on studentID, testID and dateTaken (unless resits are not applicable, in which case just testID and studentID).

Regards,

Matt.

I realised I didn't have a question in my message.

But you figured what I was asking...

Thanks for advice!

Test_stats
-Member_id
-Subject_id
-Test_id
-Score
-Wrong question numbers
-Date Taken

Subjects
-Subject_id
-Subject

Is the above a better table structure?