What is normalization and when do you denormalize?

Model answer

Normalization organizes tables to reduce redundancy and avoid update anomalies. Each fact is stored once, so you change it in one place.

1NF: each column holds an atomic value, with no repeating groups.
2NF: every non-key column depends on the whole primary key, not only a part of it.
3NF: non-key columns depend only on the key, not on other non-key columns. For example, store a customer's city in the customers table, not in every order.

The benefits are consistent data, smaller storage and simpler writes.

Denormalization deliberately adds redundancy to speed up reads. Examples: storing an order total on the order row, copying a product name into order items to keep history, or maintaining a reporting table. It reduces joins, but you must keep the copies in sync, usually with application logic, triggers or a rebuild job.

Normalize by default, then denormalize specific hot paths after measuring a real read problem. Analytical systems often use denormalized star schemas on purpose.

Key points

Normalization stores each fact once to prevent update anomalies
1NF atomic values, 2NF full key dependency, 3NF no transitive dependency
Denormalize on measured read hot paths to cut joins
Denormalized copies must be kept in sync

Likely follow-ups

Give an example of an update anomaly in an unnormalized table.
Why do data warehouses often denormalize?

Common mistakes

Denormalizing up front before any measured problem
Over-normalizing into many tiny tables that need deep joins