Many-to-many SQL Relationship Explained
Databases
10/10/2022
A many-to-many relationship is defined by many records in one table relating to many records in another table. As a typical example, take a school. Every class will have many students enrolled, but at the same time every student student will attend many classes.
If one were to represent this in a database, one would assume to add multiple foreign keys 🔑 in a single column as seen below.
A student table, and
| Id | Name | Class Id |
|---|---|---|
| 1 | Dilshan | 1, 2 |
| 2 | Jessica | 2 |
A class table.
| Id | Name | Student Id |
|---|---|---|
| 1 | Finance | 1 |
| 2 | Accounting | 1, 2 |
However, adding comma separated values is not possible in a database. Adding a second foreign key column instead for classes or students isn't possible either. Neither should we add another row, else we introduce data duplication.
Joining Table
To solve this issue, one must employ the use of a joining table (also known as a junction or bridging table). The point is to break up 💔 the many-to-many relationship into 2 one-to-many relationships. It's basically an intermediate table that records any combination of foreign keys of 2 tables. Furthermore, it's common practice to name the joining table after the tables it's linking, e.g. student_class.
To illustrate this, the example from above can be properly remodeled as follows:
A student (one-to-many with student_class) table,
| Id | Name |
|---|---|
| 1 | Dilshan |
| 2 | Jessica |
A student_class table, and
| Student Id | Class Id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 2 |
A class (one-to-many with student_class) table.
| Id | Name |
|---|---|
| 1 | Finance |
| 2 | Accounting |
