# Referencing a Join table

relational-theory

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…

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.

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

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.