Not sure of my use of foreign keys

Question

I'm building a system whereby students can view their results once they are out. The system is supposed to keep record of the student's marks over the course period when they progress to the next year. I'm not sure about my use of foreign keys in the Module_tests table though. I have two foreign keys in this table to identify a test's mark for a specific student and specific module. I guess this pair of foreign keys are the primary key of Module_tests table. Is this a sensible design? Is there a more efficient one?

Thanks in advance

Student table has three columns: StudentID, first name, surname. Module table has three columns: ModuleID, ModuleName, Year. Module_tests table has five columns: StudentID, ModuleID, Assignment, PracticalTest, FinalExam. (hope it's clear as text as I can't attach images in my posts)

score 0 · Answer 1 · answered Sep 28 '14 at 20:31

I think you should add an additional column to Module_tests that can be primary and auto-increment.

In the situation whereby the students have to retake a module, the integrity of your database might get compromised. Other than that, your tables seem fine.

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

A junction table that uniquely identifies rows by two foreign keys has been asked in this question, and the answers pretty much apply here.

edited May 23 '17 at 10:26

Community

1
1

answered Sep 29 '14 at 10:05

Walter Mitty

18,205
2
28
58

But Module_tests has another three columns. I understand that a joint table has two foreign keys only otherwise it's an entity table. – twobits Sep 29 '14 at 19:24
Relationships can have attributes. (some experts disagree) – Walter Mitty Sep 30 '14 at 01:26

Not sure of my use of foreign keys

2 Answers2