Referencing a Join table


I have a relationship that I am trying to represent in a database. Imagine I have series of many to many relationships…

Say student to proffesor, and professor to classes…enter image description here

A student can many professors and professors can obviously have many students. A student also has many classes, each taught by a different or similar professor, and professors can teach many classes.

To relate those many to many relationships to each other, you obviously need a join table between each.

But, if you relate a student to a professor, you are automatically linking him to all the classes that he teaches. Which doesn't necessarily have to be the case. You could take one class with one professor, and have another class with another professor, or you could have two classes with one professor and another with another professor.

Obviously you could just link the student directly to the classes and then link the professors to the classes. But Imagine, for the sake of this question, that for some reason, you can only link students to classes. Imagine this is a legacy database for instance.

Would it be good form to instead of linking directly to the professor, link instead a link-table of the professor… So I would have a link table to a link table of say Prof ID 2, class ID 3… That would have a link id of lets say 2. I would then have a link table with student 3 linked to Prof->Class ID 3. So that student is now linked to professor ID 2 and class ID 3. Think of that relationship like this…

enter image description here

Is that a good way to represent that relationship, or is there a better way to do this?

Best Answer

To model the classical student - class - prof situation, you need to reverse your model. class can implement the n:m relationship between student and prof. To be precise:

[student] m -- n [class] n -- 1 [prof]

The n:m relationship between student and class would be implemented by another table. Like [participant]:

[student] 1 -- n [participant] n -- 1 [class] n -- 1 [prof]

A prof has many classes. A class has many participants. A student is participant in many classes. Student and prof are only linked by participation in classes in this model.

Generally, just because a student is linked to a professor in some way does not mean the student participates in all classes of the professor. That's an unwarranted over-interpretation. What a relationship between two entities actually means is defined by additional meta-data, usually indicated by the column names used.