Many-to-many

What is Many-to-many?

Many-to-many defines a type of relationship between two related things, it is not a data specific concept. Teacher and student at a school is a good example of this in a social setting. One student will have multiple teachers, likely one for a distinct subject; at the same time, one teacher will likely have multiple students within one subject class.

How does Many-to-many work?

In relational database context, many-to-many relationships is a bit of a tricky situation and requires a bit of workaround. Continue with the teacher and student example: Now imagine the school is trying to create a relational database for all of its members. Teacher will clearly be one entity, with perhaps each Teacher’s name or staff ID as primary key. Student will be another entity, with student ID being the primary key. Now suppose we want to find all the email addresses of students that have been taught by one teacher, because she would like to send them a Christmas card. She can’t find all her student names in the Teacher entity, because there is only one record for herself in there, if the Teacher table is used to record all students, then there will be one column or field for each student, which is clearly cumbersome and the schema of the table will change all the time because new students will always need to be added. Same inefficiency exists in the Student entity if the teacher names are stored in there, there will be too many columns and the schema of the entity is easily subject to change.

The solution lies in an “in-between” table which just stores the exhaustive teacher-student relationship mapping, this means it can be as simple as having two columns, one containing Teacher ID and the second containing Student ID, and the primary key of this in-between table is exactly the combination of these two fields. With this Interim table, the teacher Christmas card problem can be solved by going through the teacher table to find the correct Teacher ID, take Teacher ID and look into the in-between table for all related Student ID’s, and use these Student ID’s to look into Student entity and find the corresponding email addresses.