Normalization vs Denormalization
Normalization vs denormalization: normal forms, joins vs duplication, read vs write trade-offs, and how to model data for access patterns.
Normalization organises data so each fact is stored exactly once, with relationships expressed through keys. Denormalization deliberately duplicates data so common reads need fewer joins or lookups. Normalised schemas are easier to keep correct; denormalised ones are faster to read. Most systems normalise the source of truth and denormalise selected read paths.
Normalization in practice
The normal forms formalise one idea: don't repeat facts. In everyday terms:
- 1NF: each column holds one value. No comma-separated lists of tags in a column; use a separate table.
- 2NF: every non-key column depends on the whole key, not part of a composite key.
- 3NF: non-key columns depend only on the key, not on other non-key columns. A customer's city belongs in the
customerstable, not repeated in every order.
Benefits: an update touches one row (change a customer's email once), no update anomalies (two copies disagreeing), smaller storage, and flexibility to answer new questions with joins.
Why denormalize
Normalised reads can need many joins. At high read volumes, or when data is sharded across machines where joins are impossible, it's often better to store data in the shape it's read:
| Technique | Example |
|---|---|
| Duplicate columns | Store author_name on each post or feed item |
| Precomputed aggregates | like_count on the post instead of COUNT(*) on every view |
| Embedded documents | An order document containing its line items |
| Materialized views / read models | A table per screen, rebuilt from events. See event sourcing and CQRS |
| Per-query tables (NoSQL) | In Cassandra, messages_by_conversation and messages_by_user hold the same messages for different queries |
The costs of duplication
- Write amplification: one change updates many copies (a name change touches every post).
- Inconsistency windows: copies are usually updated asynchronously, so they can disagree for a while.
- More code: something must keep copies in sync: application logic, triggers, change data capture or stream processing.
Decide how stale each copy may be. A denormalised author name that updates within a minute is fine; a denormalised account balance is not, so it shouldn't be denormalised at all.
A practical approach
- Model the source of truth normalised, in a store with transactions.
- Find the hot read paths (feeds, product pages, dashboards) from your access patterns.
- Build derived, denormalised views for those paths: caches, read models, search indexes, aggregated counters.
- Update derived views from changes (events or CDC), and make it possible to rebuild them from the source of truth.
Where it’s used
Feed entries carry denormalised author details and counters so a feed renders from one read. See fan-out.
Star schemas denormalise dimensions (product, customer, date) around fact tables for fast analytics.
Data modelling starts from queries, duplicating data into one table per access pattern.
Orders copy the product name and price at purchase time, intentionally, so later catalogue changes don't rewrite history.
In an interview
- Normalise the core model, then justify each denormalisation with a hot read path.
- Say how duplicated data is kept in sync and how stale it may get.
- Use precomputed counters for likes and views instead of counting rows on every read.
- Point out data that should be copied at a point in time (the price on an order) versus data that should stay in sync.