Normalization: 1NF, 2NF and 3NF
Organize tables so every fact is stored once, in exactly one place
Big idea: Normalization is the process of splitting a wide, repetitive table into smaller related tables so that each fact lives in exactly one place. The payoff is fewer bugs: you stop storing the same value in many rows, so it can never disagree with itself. The first three normal forms - 1NF, 2NF and 3NF - are a checklist you apply one step at a time, each removing a specific kind of redundancy.
By the end you will be able to:
- name the three anomalies that redundancy causes,
- read a functional dependency such as
order_id -> customer_id, - put a table into 1NF (atomic values, no repeating groups),
- reach 2NF by removing partial dependencies on a composite key,
- reach 3NF by removing transitive dependencies,
- decide when denormalization is a reasonable trade-off.
1. The problem: one big table
Imagine an online shop that records every order line in a single wide table. It feels convenient - everything is in one place - but the same customer and product facts get copied into many rows.
| order_id | customer_name | customer_city | products | total |
|---|---|---|---|---|
| 1001 | Aида Sultanova | Bishkek | Keyboard, Mouse | 45.00 |
| 1002 | Aида Sultanova | Bishkek | Monitor | 150.00 |
| 1003 | Boris Ivanov | Osh | Mouse | 15.00 |
Because Aida Sultanova / Bishkek appears in two rows, and because the products cell crams several products together, this table suffers from three classic anomalies:
- Update anomaly: if Aida moves to Osh, you must find and change every row that mentions her. Miss one and the database now claims she lives in two cities at once.
- Insertion anomaly: you cannot record a brand-new customer who has not ordered yet, because there is no row to put them in without inventing a fake order.
- Deletion anomaly: delete order 1003 and you also erase the only record that Boris Ivanov exists at all.
Normalization removes these anomalies by giving each real-world thing - a customer, a product, an order - its own table, so a fact is written down only once.