Database modelling
One-to-many relationships
In the previous chapter, you saw how primary keys and foreign keys connect rows across tables.
Now we can use those keys to understand one of the most common relationship types in relational databases: a one-to-many relationship.
Our customers and orders tables already form one.
A customer can place many orders, but each order belongs to one customer.
The foreign-key reference is:
1orders.customer_id → customers.idThe relationship cardinality is:
12customers orders 1 ───────────< manySeeing the relationship in the data
Start by looking at some orders for customer 1:
123456789SELECT id, customer_id, status, total_amountFROM ordersWHERE customer_id = 1ORDER BY idLIMIT 5;You should see several rows with customer_id = 1.
Each of those rows represents a different order placed by the same customer.
Now look at the customer:
123456SELECT id, full_name, emailFROM customersWHERE id = 1;There is one customer row with id = 1, but multiple rows in orders can reference that same value.
That is what makes this a one-to-many relationship.
The one side and the many side
A simplified view looks like this:
The single row in customers is on the one side.
The matching rows in orders are on the many side.
The foreign key lives on the many side
Notice where the foreign key is stored:
1orders.customer_idIt is in the orders table.
That makes sense because each order needs to record which customer it belongs to.
The usual pattern for a one-to-many relationship is:
123456one side primary key ▲ │many side foreign keyIn our database:
1orders.customer_id → customers.idMany order rows can contain the same customer_id, while each of those values references one customer.
One customer can appear many times in a join
This relationship also explains something you saw in the joins module.
Consider:
12345678910SELECT c.id, c.full_name, o.id AS order_idFROM customers cINNER JOIN orders o ON c.id = o.customer_idWHERE c.id = 1ORDER BY o.idLIMIT 5;The customer row can appear multiple times in the result.
That is because PostgreSQL returns one joined row for each matching order.
Conceptually:
1234Customer 1 + Order ACustomer 1 + Order BCustomer 1 + Order CCustomer 1 + Order DThe customer data is repeated in the query result because one customer matches many order rows.
This is normal behavior for a one-to-many join.
One-to-many relationships are everywhere
The same pattern appears in many applications:
12345user → postsauthor → articlescategory → productscompany → employeesorder → order_itemsOur database contains another example:
1order_items.order_id → orders.idOne order can have many order items, while each order item belongs to one order.
Many doesn't mean there must be many
The word many describes what the relationship allows.
A customer might have:
1230 orders1 ordermany ordersThe relationship simply allows multiple rows in orders to reference the same customer.
If no order references a customer, that customer is still a valid row in customers.
This is why LEFT JOIN is useful when we want to include customers even if they have no orders.
The important point is:
The primary key identifies the row on the one side, while foreign keys in multiple rows can reference it from the many side.