sqlrelational-databasedatabase-normalization

relational database design (normalizing many-to-many mappings)


Students can be in many different classes. Each class has many different student. A student can have many grades but only 1 grade per class--every (student, class) pair has one grade.

Should I layout the (MySQL) database like:

students (student_id, student_name)
classes (class_id, class_name)
students_classes (student_class_id, student_id, class_id)
grades (student_class_id, grade)

Or this:

students (student_id, student_name)
classes (class_id, class_name)
grades (student_id, class_id, grade)

Or something else?

Option 2 is simpler now, but in the future I might need other statistics related to each (student_id, class_id) pair, in which case option 1 may be better, though it is overly complicated.


Solution

  • Option 3)

    students (student_id, student_name)
    classes (class_id, class_name)
    students_classes (student_class_id, student_id, class_id, grade)
    

    Unless grade has the possibility of becoming a full-fledged entity. In which case:

    Option 4)

    students (student_id, student_name)
    classes (class_id, class_name)
    students_classes (student_class_id, student_id, class_id)
    grades (grade_id, grade, student_class_id)