How to Implement Many-to-Many Relationships in Relational Databases
Tags: database, sql
Published: 2022-02-20 08:27:30

---
title: How to Implement Many-to-Many Relationships in Relational Databases 
published: true
description: Nearly any data model of non-negligible complexity will include at least one (if not several) many-to-many relationships. This relationship emerges when, given two tables A [&#8230;]
tags: database, sql
canonical_url: https://erikscode.space/index.php/2022/02/20/how-to-implement-many-to-many-relationships-in-relational-databases/
cover_image: https://i0.wp.com/erikscode.space/wp-content/uploads/2022/02/pexels-scott-webb-1029624-scaled.jpg?fit=2560%2C1440&ssl=1
---

Nearly any data model of non-negligible complexity will include at least one (if not several) many-to-many relationships. This relationship emerges when, given two tables A and B, instances of A could have many instances of B, and instances of B could have many instances of A. Successfully implementing this relationship is a bit of stumbling block for people new to working with or building databases. However, the strategy for implementing this relationship in relational databases is important to understand. Believe it or not, it’s also not all that complicated.

Once you’ve implemented this relationship a few times, the strategy becomes rather intuitive. However, I remember this concept being one of the hardest ones for me and my classmates to finally understand when it was introduced in second year of undergrad. Hopefully, this article will help you internalize the many-to-many relationship like I eventually did.

What is the Many-to-Many Relationship?
======================================

The many-to-many (m2m) relationship is a relationship that occurs in data modeling when two entities are related but neither belong to the other and both can have many instances of each other. Here are some examples of many-to-many relationships:

- a pet can have many owners, and owners can have many pets
- a language can be spoken by many people, and a person can speak many languages
- a class can have many students, and a student can be in many classes
- a venue can have many visitors, and a visitor can visit many venues

To better understand the many-to-many relationship, let’s briefly talk about the one-to-many relationship to see how the m2m differs:

- a country can have many cities, but a city can only be in one country
- a car company can make many car models, but each car model is made by only one company

In each of these examples, note that there is a sense of one entity *belonging* to another.

Modeling One-to-Many in the Database
====================================

One-to-many relationships are easy to model in the database. For example, consider the country-city example. The country India has many cities such as Mumbai, Hyderabad, and Kolkata, but Kolkata only belongs to the country of India.

So, in the database, we probably have a `Country` table that looks like this:

![](https://i0.wp.com/erikscode.space/wp-content/uploads/2022/02/image-1.png?resize=357%2C155&ssl=1)Country table with Id and Name columns

And the `City` table plus relationship to the `Country` table would look like this:

![](https://i0.wp.com/erikscode.space/wp-content/uploads/2022/02/image-2.png?resize=277%2C334&ssl=1)Country/City relationship

This is simple enough, and we can query for all cities in a country by simply writing something like `SELECT City.Name FROM City JOIN Country ON Country.Id = City.CountryId WHERE Country.Name = 'India'` and we’d get a result set including Mumbai, Hyderabad, Kolkata, and more.

But this kind of modeling is easy and we’ve only used it as an example to better understand our next section: modeling the many-to-many relationship.

Modeling Many-to-Many in the Database
=====================================

Now let’s consider the more complex many-to-many relationship. Specifically, let’s consider the instance of many people speaking many languages. For example, we might have a Person table and Language table like so:

![](https://i0.wp.com/erikscode.space/wp-content/uploads/2022/02/image-3.png?resize=605%2C122&ssl=1)Person and Language tables

Now, suppose we have two people: Erik and Lina. Erik speaks English and Spanish, Lina speaks English and Arabic. This is what we mean by many-to-many relationships; Lina speaks many languages–English and Arabic–and English is spoken by many people–Erik and Lina. So how do we relate the `Person` and `Language` tables to list out all of the languages a person speaks, or all of the people that speak a language?

The answer is to create a new table, the ***associative table***. The associative table sits between the two entities in a many-to-many relationship and actually has a one-to-many relationship with each. That is, given tables A and B and an associative table C, A has many Bs *through* C and B has many As *through* C; put another way, A has many Cs, B has many Cs, but C has one A and one B.

> Note: The associative table are also sometimes called association, bridge, intermediary, join, junction, crosswalk, or linking table (and there’s probably many more names for it). I learned the term “associative” table in undergrad, and I think that’s its official name in relational theory, so that’s what we’ll stick with in this article.

Let’s make this more concrete by creating an associative table for the `Person` and `Language` relationship. It’s common practice to name association tables as an amalgamation of the tables they join, so we’ll call our associative table `PersonLanguage`. I’d create such a table with the following SQL:

```
CREATE TABLE PersonLanguage(
	Id INT IDENTITY(1,1) PRIMARY KEY,
	PersonId INT FOREIGN KEY REFERENCES Person(Id),
	LanguageId INT FOREIGN KEY REFERENCES Language(Id)
)
```


Notice that we specified two foreign keys on this table, `PersonId` and `LanguageId`, pointing to the `Person` and `Language` tables respectively. The table structure now looks like this:

![](https://i0.wp.com/erikscode.space/wp-content/uploads/2022/02/image-4.png?resize=643%2C367&ssl=1)

Now, there will be a record in the `PersonLanguage` table for every language an individual `Person` speaks. As a result, there will also be a record in the `PersonLanguage` table for every person that speaks a `Language`. If we want to see the list of languages Lina speaks, we could run the following query:

```
SELECT l.name
FROM PersonLanguage pl
JOIN Person p ON p.id = pl.PersonId
JOIN Language l ON l.Id = pl.LanguageId
WHERE p.name = 'Lina'
```


and we’d get results like:

![](https://i0.wp.com/erikscode.space/wp-content/uploads/2022/02/image-5.png?resize=267%2C146&ssl=1)Results

Likewise, if we want a list of all the people that speak English, we could run a query like this:

```
SELECT p.name
FROM PersonLanguage pl
JOIN Person p ON p.id = pl.PersonId
JOIN Language l ON l.Id = pl.LanguageId
WHERE l.name = 'English'
```


This would give us the following results:

![](https://i0.wp.com/erikscode.space/wp-content/uploads/2022/02/image-6.png?resize=284%2C146&ssl=1)Results

Extending the Associative Table
-------------------------------

The above examples show the most basic implementation of building associative tables to break up many-to-many relationships. The example `PersonLanguage` associative table above only has two columns (besides its own ID column) for pointing to `Person` and `Language` records.

We can actually add columns to this table as well and capture interesting information. For example, what if we wanted to record which language was a person’s mother tongue? In other words, if Erik grew up speaking English and learned Spanish later, but Lina grew up speaking Arabic and learned English later, how would we capture that?

Thanks to the associative table, the answer is pretty easy. We just have to add a column to `PersonLanguage` that indicates if the `Language` record captured is the `Person` record’s primary language. In T-SQL, I’d run the following `ALTER` statement:

```
ALTER TABLE PersonLanguage ADD PrimaryLanguage BIT
```


and set the `PrimaryLanguage` value to `1` for languages the person learned from birth. By capturing this data on the associative table, we can now write a query to generate a report of every person and their mother tongue like so:

```
SELECT p.name AS 'Person', l.Name AS 'Primary Language'
FROM PersonLanguage pl
JOIN Person p ON p.id = pl.PersonId
JOIN Language l ON l.Id = pl.LanguageId
WHERE pl.PrimaryLanguage = 1
```


and we’d get results like:

![](https://i0.wp.com/erikscode.space/wp-content/uploads/2022/02/image-7.png?resize=371%2C154&ssl=1)Results

That is just one example of how to extend the functionality of our database with associative tables.

Conclusion
==========

In this brief article, we learned how to break up many-to-many relationships in relational databases. The general approach is to place an associative table between the two tables and have a one-to-many relationship with them. Then we saw how we can use associative tables to capture nuanced data and create interesting reports. This is an important concept to understand in any kind of software or data engineering work that uses relational databases. As always, I hope this was helpful to you and don’t be afraid to contact me with any questions you might have!