Database relationships — Many-to-Many
By definition, a many-to-many relationship refers to the connection between two entities. In our computer system, by virtue of different database languages such as Ruby or Python, these relationships are usually implemented directly or via a joiner table.
Because this relationship is such an important piece of demonstrating any associating data between two different models, it is important that the concept is understandable. Many-to-many relationships, although laid out pretty simply and have a very basic premise, can get complicated quickly if not understood.
Let’s say we have a list of books that we want to find in a library. There is a chance that each book can be found in either your local library or any other library. This is because every library contains many books, signifying a one-to-many relationship and a specific book can be found in multiple libraries, hence marking a many-to-many relationship. We can represent this relationship by creating two lists of entities, as simple as making two tables creating a column linking both fields.
A simple many-to-many relationship between two lists such as the above is just as straightforward as one-to-many relationships. Another relatable example of this relationship can be understood by looking at the relationship between customers and products. Customers can purchase multiple products and these products can be purchased by many customers. Again, this can be viewed very briefly via two tables with crossing IDs.
Keeping this in mind, many relationships that we tackle aren’t as simple as directly correlating with one another. Most of the time, many-to-many relationships are defined through a joining model. For example, if you were required to keep track of employee invoices; as an employer you can have many invoices with the same invoice number. If an employee comes to inquire about an invoice, and you look up that invoice number, you wouldn’t have direct access to just one invoice. Instead you would lead to all invoices with that same number. For such a case, a unique value or ID needs to be assigned to each individual invoice.
The Join Model
To avoid this problem, you can break this scenario into two one-to-many relationships, but both containing one join model. Each record in a join model will include a ‘match’ field that contains the value of a unique ID assigned to both the invoice number and employee. By definition, these ‘match’ fields are called foreign keys. A join model is essential in many-to-many relationships that are not directly associated with one another. Instead they have an overlapping factor that we can use to connect and move between both models.
Another example to further understand the join model and its importance in many-to-many relationships is between students and classes. A class can hold many students and a student can have many classes. To begin building this relationship, we will have two simple tables for students and classes. However, we’d need a joiner table, such as registration or enrollment can clearly shows which class belongs to which student and vice-versa. This table would include unique IDs for both students and classes.
Many-to-many relationships are not limited to three models. We can build this relationship between as many models as we want as long as we are connecting them via unique IDs either directly or via a joining model.
References
