Normalization & Denormalization
Normalize for write correctness and storage efficiency, denormalize for read performance — pick based on the read/write ratio.
What it is
Normalization decomposes tables to eliminate redundancy and update anomalies. 1NF requires atomic columns (no comma-separated lists); 2NF eliminates partial dependencies on composite keys; 3NF (Third Normal Form) eliminates transitive dependencies; BCNF is a stricter 3NF. Denormalization is the deliberate reintroduction of redundancy — duplicating a column across tables, pre-computing aggregates, or storing materialized join results — to avoid expensive joins on hot read paths. The choice is workload-driven: OLTP normalizes, OLAP denormalizes (star/snowflake schemas), and most real systems sit in between.
Why senior interviewers ask
Junior engineers either over-normalize (joins everywhere, performance dies) or under-normalize (data inconsistency, update anomalies). Senior interviewers want to hear the read/write ratio and the consistency requirements drive the choice.
Key points
- 1NF: every column is atomic (no arrays, no comma-separated values).
- 2NF: no partial dependency on a composite key — every non-key column depends on the whole key.
- 3NF: no transitive dependency — non-key columns don't depend on other non-key columns.
- BCNF: every determinant is a candidate key; stricter than 3NF for edge cases.
- Denormalization wins when read frequency >> write frequency and joins are expensive.
- Materialized views are a controlled form of denormalization — DB refreshes them.
- NoSQL forces denormalization because joins aren't first-class.
- Denormalized data must be kept consistent — triggers, CDC, application logic, or accept staleness.
Pros
- Normalized: single source of truth, no update anomalies, smaller storage.
- Normalized: schema enforces correctness — invalid states are unrepresentable.
- Denormalized: read latency drops to single-table scans.
- Denormalized: maps cleanly to NoSQL data models.
Cons
- Normalized: joins on hot paths can dominate query time.
- Normalized: schema migrations require coordinated changes across tables.
- Denormalized: writes get expensive (update everywhere the field appears).
- Denormalized: data drift is a constant risk without good tooling.
When to choose
- Normalize: OLTP with frequent updates and unknown future queries.
- Normalize: when storage and write consistency matter more than read latency.
- Denormalize: OLAP / analytics (star schemas).
- Denormalize: read-heavy NoSQL workloads where joins aren't an option.
- Denormalize: pre-compute expensive aggregates (counters, leaderboards).
When to avoid
- Over-normalize: don't split a 1:1 relationship into two tables to feel principled.
- Denormalize prematurely: measure first; denormalization adds complexity that's hard to reverse.
Real systems
Interview probe
'Why are you storing user_name on the orders table instead of joining to users?' Answer: read-heavy display path + name is essentially immutable + want order history to reflect name at order time. Don't denormalize just to avoid a join.