Database modelling
Many-to-many relationships and junction tables
In the previous chapter, you saw a one-to-many relationship:
1customers → ordersOne customer can have many orders, while each order belongs to one customer.
Some relationships work differently.
Consider orders and products.
One order can contain many products.
At the same time, one product can appear in many different orders.
That is a many-to-many relationship.
Seeing the relationship
Conceptually:
12345678orders productsOrder 1 ───────▶ Product A ├───────▶ Product B └───────▶ Product COrder 2 ───────▶ Product A └───────▶ Product DOrder 1 contains several products.
Product A also appears in more than one order.
Both sides can therefore have many related rows.
Why we need another table
We cannot represent this cleanly by placing a single product_id column in orders, because one order can contain several products.
Similarly, placing a single order_id column in products would not work because one product can appear in many orders.
Instead, our database uses a third table:
1order_itemsThe order_items table connects orders and products.
Run:
123456789SELECT id, order_id, product_id, quantity, unit_priceFROM order_itemsORDER BY idLIMIT 5;You should see:
1234567 id | order_id | product_id | quantity | unit_price----+----------+------------+----------+----------- 1 | 2 | 2 | 2 | 3.01 2 | 3 | 3 | 3 | 3.02 3 | 4 | 4 | 4 | 3.03 4 | 5 | 5 | 1 | 3.04 5 | 6 | 6 | 2 | 3.05Each row in order_items represents a relationship between one order and one product.
For example, order_id = 2 and product_id = 2 means that order 2 contains product 2.
The junction table
A table used to connect the two sides of a many-to-many relationship is often called a junction table.
Our junction table is order_items.
It contains foreign keys that reference both related tables:
123order_items.order_id → orders.idorder_items.product_id → products.idA simplified view looks like this:
The many-to-many relationship between orders and products has been represented as two one-to-many relationships.
Two one-to-many relationships
First:
1order_items.order_id → orders.idOne order can be referenced by many order_items rows.
Second:
1order_items.product_id → products.idOne product can also be referenced by many order_items rows.
Together, these relationships allow many orders to contain many products.
The junction table can store more than keys
A junction table often stores more than just the foreign keys.
Our order_items table also contains:
12quantityunit_priceThese values describe the relationship between a particular order and a particular product.
For example, product 2 might appear in one order with:
12quantity = 2unit_price = 3.01and in another order with a different quantity or price.
Those values do not describe the product globally.
They describe that product within a particular order.
This is another important role of a junction table: it can store attributes that belong to the relationship itself.
Querying the relationship
To see the relationship between order items and products more clearly, run:
12345678910SELECT oi.order_id, p.name AS product_name, oi.quantity, oi.unit_priceFROM order_items oiINNER JOIN products p ON oi.product_id = p.idWHERE oi.id <= 5ORDER BY oi.id;The junction table provides the connection:
12345order ↓order_items ↓productWhy not store a list of product IDs?
You might imagine storing something like this directly in orders:
1product_ids = [2, 7, 15]That would make the relationship harder for PostgreSQL to enforce and query as normal relational data.
Using separate rows in order_items gives each order-product relationship its own record.
PostgreSQL can then:
enforce foreign keys
join the related tables
filter individual order items
store attributes such as quantity
add or remove relationships independently
Many-to-many relationships are common
The same pattern appears in many applications:
1234567students ← enrollments → coursesusers ← memberships → teamsposts ← post_tags → tagsproducts ← cart_items → cartsThe middle table represents the relationship between the two sides.
The key idea is:
A many-to-many relationship is typically represented using a junction table that connects the two tables through foreign keys.
In our database:
1orders ← order_items → productsorder_items turns the many-to-many relationship into two one-to-many relationships.