I have three tables, a student table, a grade (as in grade 1, 2, etc) table and a score table. The score table has student id as a foreign key and the student table has grade code as a foreign key. Now when the student takes a test, a record is inserted in the score table. The problem is, the next year, the student's grade changes. If the student takes the same test and gets the same score, this would result in two rows with the same data. How should I go about solving this problem? One solution is to add the grade code to the score table. But then the table wouldn't be in third normal form as grade becomes a transitional dependency on student in the score table.

I'm no genius at data modeling but any suggestions are welcome.

Add a Year column to the Score table, and make it part of the key?

Yeah but what happens if I need to run a query like "select student_id from scores where year=2008 and grade=5"?
Would I just re-calculate that in the application, like figure out what each student's grade would have been in 2008 and then filter the results?

Does grade relate to score in some way? e.g. scores between 71-80 lead to a grade of 5?

No. The problem is you're attempting to model time-dependent table.

Student does not determine grade, because over time, a student can be in multiple grades. At the very least, you need a separate table to hold valid student-grade combinations. At its simplest, this table will simply record a student and a grade, but if you take into account students skipping or repeating grades, this table will look something like:

student, school year -> grade

Similarly, a student can take a test multiple times. Presumably, a student can also take multiple tests. So your determinant for a test score is:

student, test, date -> score

This leaves you with three options, depending on your requirements:

1. Simply record the date a test is taken. You can then do a time-based join to determine what tests where taken in which grade.
2. Take the key of the student-grade association table as part of the key in your score table. This will enforce that a test can only be taken while a student is in some grade.
3. Do both. This will allow a student to take the same test multiple times while in one grade.

Thanks, I took the third option. Time based joins can be expensive, so even though I'm storing the dates, I'm not using it in my query. I also added a unique constraint between the student id and student_grades_id to make sure there is only one record per student per grade.

Thanks again.