Intermediate
Modeling many-to-many relationships with a junction table
← PrevSubqueries
Question 1
Join orders, order_products, and products to retrieve the order ID, product name, quantity, and unit price.
📖 ExplanationReference
What is a many-to-many relationship?
A single order can contain multiple products, and a single product can appear in multiple orders. This kind of many-to-many (N:M) relationship can't be represented by connecting two tables directly.
▼ Modeling many-to-many with a junction table
orders
order (1)
1:N
──
order_products
junction table (N)
order_id / product_id
N:1
──
products
product (1)
One order holds multiple products / one product appears in multiple orders
The structure of order_products
The junction table records "which order, which product, how many, and at what price."
order_products
id — line-item ID (primary key)
order_id — which order (foreign key to orders.id)
product_id — which product (foreign key to products.id)
quantity — quantity
price — unit price at the time of purchase
💡 Why does it store the price at the time of purchase?
products.price can change later on. If you didn't record "the price at the time of the order," past order totals would silently change too. This is a common and important design pattern in real-world systems.
products.price can change later on. If you didn't record "the price at the time of the order," past order totals would silently change too. This is a common and important design pattern in real-world systems.
Reading data by joining all three tables
SELECT
u.name AS customer,
p.name AS product,
op.quantity AS quantity,
op.price * op.quantity AS subtotal
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_products op ON o.id = op.order_id
JOIN products p ON op.product_id = p.id;Applying it to aggregation
SELECT u.name, SUM(op.price * op.quantity) AS total_spent
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_products op ON o.id = op.order_id
GROUP BY u.name
ORDER BY total_spent DESC;
⚠️ A common design mistake: trying to store many-to-many directly
Trying to store something like
Trying to store something like
product_ids = "1,3,5" as a comma-separated list directly on the orders table is an anti-pattern. It makes aggregation and searching much harder. Normalizing with a junction table is the correct design.
💼 Where it's used in the real world
Order details on an e-commerce site, a tagging system (articles and tags), permission management (users and roles) — many-to-many relationships show up in every kind of system. Understanding junction tables significantly improves your database design skills.
Order details on an e-commerce site, a tagging system (articles and tags), permission management (users and roles) — many-to-many relationships show up in every kind of system. Understanding junction tables significantly improves your database design skills.