The table that seemed convenient
A small shop keeps its orders in one table. Every row has everything you could want to know: the order, the item, the customer's name and the customer's address. One table, no joins, easy to read.
Then real life happens to it: a customer moves, an order is cancelled, somebody signs up.
orders
| order | customer | address | item |
|---|---|---|---|
| 1 | Asha | 12 Lake Road | Keyboard |
| 2 | Ben | 5 Hill Street | Mouse |
| 3 | Asha | 12 Lake Road | Monitor |
| 4 | Asha | 12 Lake Road | Cable |
Start. One table holds everything about an order, including who placed it and where they live. Asha has three orders, so her address is written three times.
- Places Asha's address is stored
- 3
- Different addresses on file for Asha
- 1
Three ordinary events, three problems. They have names.
| Problem | What happened |
|---|---|
| Update anomaly | A fact stored in several places was changed in only some of them. The table now disagrees with itself. |
| Delete anomaly | Removing one thing (an order) removed something unrelated (the only record of a customer). |
| Insert anomaly | A thing (a customer) could not be stored without inventing something else (an order). |
All three have the same cause: the table is about two different things at once, orders and customers.
The rule
Store each fact once.
Asha's address is a fact about Asha. It is not a fact about order 1, order 3 or order 4. So it belongs in a table about customers, in one row. Orders refer to her by an id.
customers
| id | name | address |
|---|---|---|
| 1 | Asha | 12 Lake Road |
| 2 | Ben | 5 Hill Street |
orders
| order | customer_id | item |
|---|---|---|
| 1 | 1 | Keyboard |
| 2 | 2 | Mouse |
| 3 | 1 | Monitor |
| 4 | 1 | Cable |
Start. Two tables. Each customer is stored once. An order does not repeat the customer's details. It holds only the customer's id, which points at their row.
- Places Asha's address is stored
- 1
- Different addresses on file for Asha
- 1
Reorganising tables this way is called normalization.
CREATE TABLE customers (
id INT PRIMARY KEY,
name TEXT NOT NULL,
address TEXT NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(id),
item TEXT NOT NULL
);Two words from that:
- A primary key is the column that identifies a row. No two rows share one.
- A foreign key is a column holding another table's primary key.
REFERENCES customers(id)makes the database enforce it: an order cannot point at a customer who does not exist.
When you want the wide view back, ask for it with a join:
SELECT orders.id, customers.name, customers.address, orders.item
FROM orders
JOIN customers ON customers.id = orders.customer_id;The wide table still exists. It is now something you compute when needed, not something you store and keep in step by hand.
Try both designs
orders
| order | customer | address | item |
|---|---|---|---|
| 1 | Asha | 12 Lake Road | Keyboard |
| 2 | Ben | 5 Hill Street | Mouse |
| 3 | Asha | 12 Lake Road | Monitor |
| 4 | Asha | 12 Lake Road | Cable |
Start. One table holds everything about an order, including who placed it and where they live. Asha has three orders, so her address is written three times.
- Places Asha's address is stored
- 3
- Different addresses on file for Asha
- 1
The normal forms
Textbooks describe normalization as a ladder of "normal forms". The first three are the ones people mean in practice. A plain way to read them:
First normal form: one value per cell
No lists inside a cell, and no numbered columns such as item1, item2, item3.
Example
A list in a cell
An order row with items = "Keyboard, Mouse, Cable" cannot be searched, counted or joined properly. "Which orders include a mouse?" becomes text matching.
Fix: an order_items table with one row per item per order.
Second normal form: every column depends on the whole key
This matters when a table's key is made of two columns. In order_items(order_id, product_id, quantity, product_name), the quantity depends on both the order and the product. The product name depends on the product alone, so it is repeated for every order that includes that product.
Fix: move product_name to a products table.
Third normal form: no column depends on another non-key column
In customers(id, name, postcode, city), the city is decided by the postcode, not by the customer. Change one customer's city and others with the same postcode now disagree.
Fix: a postcodes table, if that level of care is worth it for your data.
There is an old line that sums up all three: every column should describe the key, the whole key, and nothing but the key.
The price, and when to break the rule
Normalized data is safe to change. The cost is paid when reading: showing an order with its customer and products means joining several tables.
Usually that is fine. Joins on indexed keys are fast (see Indexes and B-Trees). But sometimes a copy is the right choice. Storing a copy on purpose is called denormalization.
| Reason to keep a copy | Example |
|---|---|
| The value must not change afterwards | The price and delivery address at the time of the order. If Asha moves next year, last year's invoice must still show where the parcel went. |
| Reads are heavy and the data rarely changes | A comment_count column on a post, to avoid counting rows on every page view |
| Reporting | Wide, flat tables built for analysis, refilled from the normalized ones |
The first row is worth a second look. An order's delivery address is a fact about that order, so storing it on the order is not a violation at all. Deciding what a fact is really about is the whole skill.
Key takeaways
- A table that mixes two kinds of thing repeats facts, and repeated facts drift apart.
- The three symptoms are update, delete and insert anomalies.
- Normalization stores each fact once, in a table about the thing it describes, and links tables by id.
- A join rebuilds the wide view whenever it is needed.
- Copying data on purpose is sometimes right, for history or for speed, but every copy must be kept in step.