The short answer
is the process of organising tables so that each fact is stored in exactly one place. It reduces repeated data and prevents errors when data is changed.
Take a single table that stores each order together with the customer's name and city:
| order_id | customer | city | amount |
|---|---|---|---|
| 1 | Asha | Pune | 450 |
| 2 | Asha | Pune | 150 |
| 3 | Ravi | Delhi | 300 |
Asha's city is stored twice. That causes three kinds of problems, called anomalies:
- Update anomaly: if Asha moves, you must change every one of her rows. Miss one and the data disagrees with itself.
- Insert anomaly: you cannot record a new customer until they place an order, because the customer only exists inside an order row.
- Delete anomaly: deleting Ravi's only order also deletes everything you knew about Ravi.
The fix is to split the data into two tables, customers and orders, and link them with a key. Now a city is stored once.
Normalization is done in steps called normal forms: 1NF, 2NF, 3NF and BCNF. Each step removes one kind of redundancy.
The trade-off: more tables means more joins when you read the data. Systems that mostly write, such as an online shop's order database, are normalised. Reporting and analytics systems often do the opposite on purpose, called denormalization, so that reads need fewer joins.
Interview shortcut
> Normalization removes repeated data to avoid update, insert and delete anomalies, at the cost of more joins.