Bootcamp
Search…
3.3.3: SQL Relationships (Many to Many)

Introduction

In 3.4.2: SQL Relationships (One to Many) we learned how to associate 2 tables to each other using foreign keys. This created a relationship in which one row in a table (e.g. categories) could be referenced multiple times in another table (e.g. recipes).
Recap of a one-to-many SQL relationship
We will now learn to create relationships where entries in both associated tables can be referenced multiple times in the associated table.

Many-to-Many Recipes and Categories

To implement a many-to-many relationship with recipes and categories, one idea might be to store multiple category IDs inside the category_id column in the recipes table, something like the following. However, a rigid rule of SQL is that we should never put more than one conceptual piece of data in a cell (i.e. no arrays or JS Objects), thus SQL convention is to create a 3rd table instead. This rule is so that SQL can easily search its tables for the data that it needs without looking into arrays or objects, because the SQL language does not support loops or object dereferencing.
Idea: Store Multiple Category IDs in recipes Table
{
"label": "Mee Pok",
"category_id": [1, 2]
}
The 3rd table used for many-to-many relationships holds relationship data. A row in this table will associate an entry in 1 table with an entry in another, and potential store other information about the relationship between those 2 entries.
We will redo our Recipe App from before, where the new app enables both categories to have many recipes AND recipes to have many categories.
Sample DB Schema for Recipe App with Many-to-Many Relationship

Sample Implementation

Create Tables

CREATE TABLE recipes (id SERIAL PRIMARY KEY, label TEXT);
CREATE TABLE categories (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE recipe_categories (id SERIAL PRIMARY KEY, recipe_id INTEGER, category_id INTEGER);

Create Categories

INSERT INTO categories (name) VALUES ('vegan');
INSERT INTO categories (name) VALUES ('keto');
INSERT INTO categories (name) VALUES ('nut free');

Create Recipes

INSERT INTO recipes (label) VALUES ('Udon');
INSERT INTO recipes (label) VALUES ('Mee Pok');
INSERT INTO recipes (label) VALUES ('Pasta');

Check IDs

SELECT * FROM categories;
SELECT * FROM recipes;

Create Associations Between Recipes and Categories

Give Udon the Vegan and Keto categories, and give Mee Pok the Keto category.
INSERT INTO recipe_categories (recipe_id, category_id) VALUES (1, 1);
INSERT INTO recipe_categories (recipe_id, category_id) VALUES (1, 2);
INSERT INTO recipe_categories (recipe_id, category_id) VALUES (2, 1);

Get All Vegan Relation Records by Category ID

SELECT * FROM recipe_categories WHERE category_id=1;

Get All Vegan Recipes by Category ID

SELECT recipes.id, recipes.label, recipe_categories.category_id, recipe_categories.recipe_id
FROM recipes
INNER JOIN recipe_categories
ON recipe_categories.recipe_id = recipes.id
WHERE recipe_categories.category_id = 1;

Get Udon Category Relation Records by Recipe ID

SELECT * FROM recipe_categories WHERE recipe_id=1;

Get Udon Categories by Recipe ID

SELECT categories.id, categories.name, recipe_categories.category_id, recipe_categories.recipe_id
FROM categories
INNER JOIN recipe_categories
ON recipe_categories.category_id = categories.id
WHERE recipe_categories.recipe_id = 1;

Further Reading

Past students have found this video helpful in introducing many-to-many relationships.