§ 1.8section 14 of 25·~6 min
Normalization
In 1970, E. F. Codd at IBM published A Relational Model of Data for Large Shared Data Banks, the paper behind every SQL database you'll use. Codd also defined normalization: organising tables so that each fact is stored in exactly one place.
The problem: one big table
| order_id | customer | customer_city | product | price_paise | qty |
|---|---|---|---|---|---|
| 1 | Asha | Pune | T-shirt | 49900 | 2 |
| 1 | Asha | Pune | Cap | 19900 | 1 |
| 2 | Asha | Pune | Mug | 29900 | 1 |
| 3 | Ravi | Delhi | T-shirt | 49900 | 1 |
Asha's city is stored three times, and the T-shirt's price twice. That causes anomalies:
- Update anomaly: Asha moves to Mumbai. Update one row and miss another, and the data now disagrees with itself.
- Insert anomaly: you can't add a new product until someone orders it, because every row needs an order.
- Delete anomaly: delete Ravi's only order and you lose the fact that Ravi lives in Delhi.
They all have one cause: the table mixes several kinds of fact (about customers, products and orders), so one fact gets repeated wherever another one appears.
The normal forms
Each normal form removes one kind of repetition. Each includes the ones before it.
| Form | Rule, in plain words | Fix in the example |
|---|---|---|
| 1NF | Each cell holds one value; no lists or repeating groups | One row per product in an order, never "T-shirt, Cap" in one cell |
| 2NF | Every column depends on the whole key, not part of it | Product details depend only on the product, so move them out |
| 3NF | Non-key columns depend on the key |