4

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)

Improve this question
3
  • 3
    The design seems ok. Does cakephp has this limitation, requiring all tables to have a primary key named id and not handling composite PKs and FKs? Otherwise, you could use (student_id, major_id) as the PK of MajorStudent
    – ypercubeᵀᴹ
    Commented Mar 5, 2014 at 13:04
  • @ ypercube Yes and no. You can if you want use another primary key but you will lose access to a lot of its "automagical" functionality. To the best of my knowledge however it does not support composite keys
    – Sotiris
    Commented Mar 5, 2014 at 13:25
  • The design looks good.
    – Michael Green
    Commented Apr 14, 2014 at 13:16

1 Answer 1

Reset to default
2

The tables student and major are good.

However following improvements can/should be made.

  • Merge the FKs in MajorStudent to a PK, getting rid of the id
  • Save a StudentFK and MajorFK rather than a MajorStudentFK in Exam, well one can discuss about this though but it'll be faster and in my consideration NOT redundant

  • Grade is not normalized. All attributes are supposed to be atomic and grade is not. Either make 4 columns (bad) or use the following

    Exam(id[PK], studentId[FK], MajorId[FK]) Grades(id[PK], examId[FK], value)

I don't understand why one exam comes with four grades though.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.