Many-to-Many Relationships

Objectives:

• Why do we need to think about many-to-many relationships?
• How do they work? (programming language agnostic)

Introduction

Last week I learned about one-to-many (“has many”) relationships, where one class of objects can belong to another class, and one class can have many objects of another class. A real life example of this would be a museum and its art relationship. A piece of art can only belong in one museum at a time, but a museum can contain one or more pieces of art. Therefore, the museum “has many” pieces of art. This is modeled in code through the use of “foreign keys” to reference another column, but that’s a topic for another time.

Here’s a visual diagram of a one-to-many relationship that I made:

Each museum has multiple pieces of art but each piece of art only belongs in one museum.

So that’s great. But what if we needed to describe the relationship of something a little more complex? Let’s take a real-life example. Think about the relationship between a movie and the actors in it. If we have a movie, a movie can have many actors. That’s fine, just like the example above! We can have something like a movies table, with columns ID, Title, Actor… wait, but actors can also have many movies! This is where we run into a database design problem. Here’s what a one-to-many relationship of movie to actor would look like:

Oo, Ryan Reynolds!

This only works because these two movies have completely separate actors. But in real life, an actor can have multiple movies! So how would you model that? If you tried to connect it with only two tables, things would get messy real fast.

This is gross, and NOT how you want to model these tables

So, what’s the best way to do this? The solution is: use another table! That’s right, whenever you want to model a many-to-many relationship it would be easier to use another table to connect the relationships. In our case, we would have another table that knows the relationships between each actor and each movie. So what does this look like?

Many-to-Many Example

Let’s break this down.

1. We have a Movies table that lists movies with their title and each movie has an ID.
2. We have an Actors table that lists actors by name and each actor has an ID.
3. Now heres, the tricky part: we have created a third table! This table’s sole job is to define the relationship between movies and actors.

This third table is where the magic happens. If we really look at what’s happening here we can see that the movie with the ID of 1, has an actor with an ID of 1. If we look at the corresponding data, we can see that movie Deadpool has actor Ryan Reynolds in it! But wait theres more: if we go to the next line, we can see that move Detective Pikachu also has corresponding actors_id 1 which is ALSO RYAN REYNOLDS! So right there, we defined an actor that has two movies.

This relationship works both ways! If we look at the next line, we can see movie_id 1 corresponds to Deadpool again, but this time actors_id of 3 corresponds to actress Morena Baccarin. So there, a movie can have multiple actors. And there, you have the essence of many-to-many relationships!