Database normalization, explained with examples
Normalization means arranging tables so each fact is stored once. It prevents updates that fix a value in one place and leave it wrong in another. Three rules cover almost everything you need.
First normal form: one value per cell
A column holds a single value, and there are no repeating groups. A column "phones" containing "555-1234, 555-9876" breaks it; a separate phones table with one row per number follows it.
Second normal form: depend on the whole key
In a table whose key has two columns, every other column must depend on both. In order_items(order_id, product_id, product_name, quantity), product_name depends only on product_id, so it belongs in products.
Third normal form: depend on nothing but the key
A column must not depend on another non-key column. In customers(id, city, postal_code, state), the state follows from the postal code, so it is stored twice. Move postal codes to their own table, or drop the redundant column.
When to stop
- Denormalize on purpose, for a measured reason such as a read-heavy report, and keep the normalized source of truth.
- Store a snapshot when history matters: an order keeps the price the customer paid, even if the product price changes later.
- Do not split a value that is always used whole just to reach a higher normal form.
Schemas to start from
Questions
Is third normal form enough?
For most applications, yes. Higher forms matter for unusual key structures. Practical designs fail much more often by repeating facts than by missing a subtle dependency.
Does normalization make queries slower?
It adds joins, which databases do well with the right indexes. Measure before denormalizing.