A customer moves house.
You update their address. Their next order still ships to the old one.
Nobody wrote a bug. The address was stored in five rows, and you updated four.
That's not a code problem. It's a table design problem. And the fix has a name: normalization.
What Is Database Normalization?
Normalization is the process of organising data into tables so that each fact is stored in exactly one place.
It has four goals:
- No redundancy: don't store the same fact twice.
- No contradictions: an update happens in one place, so copies can't disagree.
- Less wasted storage: repeated values take space.
- Easier change: a clean structure is simpler to extend.
Why It Matters: A Broken Orders Table
Here's a typical first version of an e-commerce orders table:
| order_id | customer_name | customer_address | product | product_price |
|---|---|---|---|---|
| 1001 | Alice | 12 Park Rd | Keyboard | 49 |
| 1002 | Alice | 12 Park Rd | Mouse, Monitor | 20, 180 |
| 1003 | Bob | 7 Hill St | Keyboard | 49 |
It works. It also has three problems.
- Redundancy: Alice's address and the keyboard's price are repeated on every order.
- Inconsistency: if Alice moves, every one of her rows must be updated. Miss one and the data contradicts itself.
- Messy values: order 1002 crams two products and two prices into single cells. You can't query or total them properly.
The Problems Normalization Solves
Update anomalies
When a fact lives in many rows, changing it means changing all of them. One missed row and your data disagrees with itself.
Insert anomalies
You can't add a new product until someone orders it, because products only exist inside order rows.
Delete anomalies
Delete Bob's only order and you lose Bob's address too.
Normalization removes all three by giving each kind of fact its own table.
The Normal Forms, Step by Step
Normalization happens in stages called normal forms. Each one builds on the last.
First Normal Form (1NF): One Value per Cell
- Every column holds a single, atomic value. No lists like "Mouse, Monitor".
- No repeating groups of columns, like
product1,product2,product3.
Second Normal Form (2NF): Depend on the Whole Key
- The table is in 1NF.
- If the key has more than one column, every other column depends on the whole key, not just part of it.
Third Normal Form (3NF): Depend Only on the Key
- The table is in 2NF.
- No column depends on another non-key column. A customer's address depends on the customer, not on the order.
Normalizing the Orders Table
Step 1: Get to 1NF
Split order 1002 so each row holds one product:
| order_id | customer_name | customer_address | product | product_price |
|---|---|---|---|---|
| 1001 | Alice | 12 Park Rd | Keyboard | 49 |
| 1002 | Alice | 12 Park Rd | Mouse | 20 |
| 1002 | Alice | 12 Park Rd | Monitor | 180 |
| 1003 | Bob | 7 Hill St | Keyboard | 49 |
Every cell now holds one value. The key is now (order_id, product).
Step 2: Get to 2NF
customer_name depends only on order_id, and product_price depends only on product. Both depend on part of the key, so they move out:
orders(order_id, customer_name, customer_address)
order_items(order_id, product_id)
products(product_id, name, price)
Step 3: Get to 3NF
In orders, the address depends on the customer, not the order. Give customers their own table:
customers(customer_id, name, address)
orders(order_id, customer_id)
order_items(order_id, product_id, quantity)
products(product_id, name, price)
Now Alice's address lives in one row. Update it once and every order sees the change.
How to Approach It
- Start with real data. List the facts you store and spot the ones that repeat.
- Draw the relationships. A quick entity-relationship diagram shows which table owns which fact.
- Apply the forms in order. 1NF, then 2NF, then 3NF.
- Use keys. Every table gets a primary key. Every relationship gets a foreign key, so the database enforces it.
- Write down your decisions. Future you will want to know why a table looks the way it does.
Know When to Denormalize
Normalization trades fewer copies for more joins.
For most applications, 3NF is the right default. But for read-heavy paths, like a reporting dashboard, storing a few pre-joined or pre-computed values can be worth it.
Denormalize on purpose, for a measured reason, and keep the normalized tables as the source of truth.
Key Takeaways
- Normalization means storing each fact in exactly one place.
- Duplicated facts cause update, insert and delete anomalies.
- 1NF: one value per cell. 2NF: depend on the whole key. 3NF: depend only on the key.
- Use primary and foreign keys so the database enforces relationships.
- Denormalize only for measured read performance, never by accident.
Final Thoughts
Most "data bugs" aren't bugs in the code.
They're the same fact stored in two places, slowly drifting apart.
Normalize first, and you'll spend a lot less time wondering which copy is the right one.