Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Variable value in a join table

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Add a Year column to the Score table, and make it part of the key?
    Code:
    StudentID Year Score
    ========= ==== -----
    1234      2009    88
    1234      2010    92

  3. #3
    Join Date
    Sep 2010
    Posts
    3
    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?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Does grade relate to score in some way? e.g. scores between 71-80 lead to a grade of 5?

  5. #5
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by jeshurun View Post
    But then the table wouldn't be in third normal form as grade becomes a transitional dependency on student in the score table.
    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.

  6. #6
    Join Date
    Sep 2010
    Posts
    3

    Smile

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •