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.
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)