Relationship types: 1:1, 1:N, M:N
In short: A relationship between two tables is one-to-one, one-to-many or many-to-many, and its type decides where the foreign key goes and when you need a junction table.
Problem
A card and tags
The shop is launching two new things. The first is a loyalty card: every customer can have one card with points. The second is product tags, such as “vegan”, “gluten-free” and “organic”, so the website can filter by them.
Where do you store them? A column card_number in customers? A column tags in products with the text “vegan, organic”? The answer depends on one question: how many things on one side match how many on the other.
The idea
One to many (1:N)
One customer has many orders. Each order belongs to one customer. This is a one-to-many relationship, written 1:N.
The key goes on the “many” side. Each order says whose it is with customer_id:
customers
- PK
idinteger nametext *emailtext *citytextjoined_attext *
orders
- PK
idinteger - FK
customer_idinteger *→ customers ordered_attext *statustext *
The other way round does not work. How many columns order_1, order_2 should a customer have? Maria has 12 orders, and tomorrow she will have 13.
This is the most common relationship. In the shop, a category and its products are like this too, and so are a product and its reviews.
Try it
Products by category
How many products are in each category that has products? Show the category name and the count.
Solution
SELECT c.name, COUNT(*) AS products
FROM categories c
JOIN products p ON p.category_id = c.id
GROUP BY c.id, c.name;A category and its products are 1:N, so you join products.category_id to categories.id and group by category. You get 6 categories. “Food” and “Home” are missing: they are top-level categories with no products directly in them, only subcategories.
The idea
One to one (1:1)
Every customer has at most one card, and every card belongs to one customer. This is one-to-one, 1:1. You store it like 1:N, with a key to the customer, plus one more rule: UNIQUE.
loyalty_cards
- PK
idinteger - FK
customer_idinteger *→ customers numbertext *pointsinteger *issued_attext *
Five customers have a card. Try giving Maria a second one:
UNIQUE on customer_id does not let in a second row for the same customer. Without it, the relationship would be 1:N.
Why not columns in customers? That works too. Often 1:1 is just more columns in the same table. A separate table makes sense when most rows do not have the second part (15 of the 20 customers have no card and would have empty cells), or when it has a life of its own: the card gets lost, replaced, it has its own points.
Try it
Who has no card yet
Marketing wants to offer a card to every customer who does not have one yet. Show their names.
Solution
SELECT c.name
FROM customers c
LEFT JOIN loyalty_cards l ON l.customer_id = c.id
WHERE l.id IS NULL;LEFT JOIN keeps every customer, and for those without a card the columns of loyalty_cards are NULL. WHERE l.id IS NULL keeps only them: 15 customers.
The idea
Many to many (M:N)
One order has many products. One product is in many orders. This is many-to-many, M:N.
Here the key cannot go in either of the two tables. The answer is a third table, a link table, with one row for each pair. In the shop this is order_items: each row is one product in one order, with the quantity and the price.
products
- PK
idinteger nametext *- FK
category_idinteger→ categories pricereal *stockinteger *added_attext *
order_items
- PK
order_idinteger→ orders - PK
product_idinteger→ products quantityinteger *unit_pricereal *
Which orders have the coffee (product 17)?
Tags are the same. A product has many tags, and a tag is on many products:
tags
- PK
idinteger nametext *
product_tags
- PK
product_idinteger→ products - PK
tag_idinteger→ tags
See it
The pair is the key
The primary key of order_items is the pair (order_id, product_id). Try adding the bread a second time to order 60, which already has it:
The database does not allow the same pair twice. A product is in the order once, and more pieces go into quantity. Otherwise the report “how many times was the bread ordered” would count the same product from one order twice.
Try it
Tags on the website
Next to each tag, the website shows how many products have it. Show the tag name and the count.
Solution
SELECT t.name, COUNT(*) AS products
FROM tags t
JOIN product_tags pt ON pt.tag_id = t.id
GROUP BY t.id, t.name;You group product_tags by tag: “vegan” 10, “gluten-free” 11, “organic” 6. That adds up to 27 pairs, while only 13 products have a tag, because most have more than one.
Exercise
Two tasks
1. The apples page shows their tags. Which are they?
The tags of the product “Apples”:
Solution
SELECT t.name
FROM tags t
JOIN product_tags pt ON pt.tag_id = t.id
JOIN products p ON p.id = pt.product_id
WHERE p.name = 'Apples';The same link table, read from the other side: from the product to the tags. “vegan”, “gluten-free” and “organic”.
2. A customer is looking for products that are both vegan and gluten-free. Show their names.
The products with both tags, “vegan” and “gluten-free”:
Solution
SELECT p.name
FROM products p
JOIN product_tags pt ON pt.product_id = p.id
JOIN tags t ON t.id = pt.tag_id
WHERE t.name IN ('vegan', 'gluten-free')
GROUP BY p.id, p.name
HAVING COUNT(*) = 2;WHERE t.name = 'vegan' AND t.name = 'gluten-free' finds nothing, because each row has one tag. You take the rows with either tag, group by product and keep those with COUNT(*) = 2. You get 8 products: the white bread is only vegan, and the white cheese is only gluten-free.
Test