Database Many to Many Relationship with Examples
We cannot characterize a many-to-many association directly in a relation scheme, because two tables cannot be children of each other. When you design your ERD, you will be challenged by lots of possibilities for many to many relationships.
You would have learned that we do not want to draw many-to-many relationships in our ERD. We want to break them up by putting a junction-entity in between. Instead of a many-to-many relationship, we then get two one-to-many relationships with the junction entity in the middle.
1. Student-professor. A student will have one or more professors. The same professor will have lots of students.
2. At a hospital a patient will be assigned to a coupld of nurses. A specific nurse will be assigned to 1 or many patients.
3. A student will have lots of subjects and the same subject can be taken by lots of students.
Lets look at the Student - Professor
Now, because many-to-many are not allowed, we will change this to add a junction-entity (StudentProfessor) in the middle.
The three entities could translate into the following three tables:
In the student table, the StudentNumber will be the primary key.
In the Professor table, the ProfNumber will be the primary key.
In the junction table - StudentProfessor, the StudentNumber and the ProfNumber are both foreign keys. As a combination they will form the primary key.
The information in the tables could look as follows: