Hello i am facing the following scenario. I have a student table with the following structure.
Student id(PK), matriculation number, first_name, last_name, email
A student can have more than one Majors(BSc mechanical engineering, MSc mechanical engineering, etc...)
Major id(PK), major
At the same time a major may be assigned to more than one students, therefore the relationship between student and major is many to many. I implemented it using the following mapping table.
MajorStudent id(PK), student_id(FK), major_id(FK)
Every student however can sit for many exams. But for every exam it is important to know his Major. That is he can sit for the same exam as BSc mechanical engineering and as MSc mechanical engineering, but this should count as two different exams. Meanwhile every exam is assigned a grade, which can have one of four values. My exam and grade tables looks like this.
Exam id(PK), major_student_id(FK), grade_id(FK) Grade id(PK), grade(value1, value2, value3, value4)
What i am trying to get at the end is the following
View student_id | first_name | last_name |... | major | exam_id | grade 1 test1 testtest1 BSc M.engin 1 value1 1 test1 testtest1 MSc M.engin 2 value3
Do you think my desing is correct. And in general how would you go about modeling a many to many to many relationship? (If it helps i am using MySQL and cakephp to implement it)