Data Modelling
Normalise until it hurts, denormalise until it works — and know which one you're doing.
45 minDifficulty 3/5database · designAI-writtenWritten by a model on 16 August 2026 and not yet read by a person. Checked automatically: schema, the pedagogical rules the hand-written material is held to, and every diagram parsed for real.
Before this
Why this exists
A customer's address is stored as a text column duplicated on every single one of their two hundred orders. They move house, and now updating their address means finding and rewriting two hundred rows — miss even one, and you have two 'current' addresses for the same customer, disagreeing with each other, with no way to know which is right. This is the exact failure normalization prevents: store each fact ONCE, and everything referencing it automatically stays consistent.
The mental model
Normalization is the discipline of storing each independent fact exactly ONCE, in the table it logically belongs to, and referencing it by id everywhere else it's needed — the opposite of copying the same data into every row that happens to need it. Denormalization is the deliberate reverse: accepting some duplication in exchange for faster reads, because joining is sometimes more expensive than the update-anomaly risk duplication introduces.
How it works
Update anomalies are normalization's core justification
If a customer's name is duplicated across 50 order rows, updating their name correctly requires updating all 50 — miss one, and the data becomes internally CONTRADICTORY (the same customer has two different names depending on which row you look at), with no way for the database to detect or prevent this, because as far as it's concerned, these are just 50 unrelated text values.
Third normal form (a practical target): every non-key fact depends only on the key
A `orders` table with columns `customer_id, customer_name, customer_address` has `customer_name` and `customer_address` depending on `customer_id`, NOT on the order itself — that's a signal they belong in a separate `customers` table, referenced by `customer_id`, rather than repeated on every order. The practical rule of thumb: if a fact would need to change identically across many rows to stay correct, it's probably in the wrong table.
Denormalization trades update simplicity for read speed, deliberately
A fully normalized schema for an analytics dashboard might require joining six tables for every single report query, which can be genuinely slow at scale. Deliberately duplicating a frequently-needed value (like storing a pre-computed `order_count` on the customer row, updated whenever an order is placed) trades some update complexity for dramatically faster reads — a reasonable trade when reads vastly outnumber writes and the duplicated value changes rarely.
Normalize until it hurts, denormalize until it works
The practical guidance is to start FULLY normalized — no duplication, every fact in one place — and only denormalize specific, MEASURED bottlenecks once they're actually shown to be a real problem, rather than guessing in advance which duplications might someday help performance. Premature denormalization trades away consistency guarantees for a performance benefit that may never materialize.
The mechanism
Designing a normalized schema means identifying each independent kind of fact (a customer, an order, a product) and giving it its own table with its own primary key. Relationships between them are expressed via foreign keys — an order REFERENCES a customer by id, rather than repeating the customer's details inline. Retrieving a full picture (a customer with their orders) requires a join, but every fact exists in exactly one place, so updating it is a single, unambiguous operation.
What people get wrong
- Normalization is an academic concern that doesn't matter for a real, practical application.
- Update anomalies from duplicated data are a genuinely common source of real production bugs — inconsistent customer details across records, prices that disagree between an order and a product catalog — not an abstract theoretical concern. Teams that skip normalization 'for simplicity' often pay for it later with data integrity bugs that are much harder to fix retroactively than the schema would have been to design correctly upfront.
- A fully normalized schema is always the correct choice, and denormalization is always a mistake.
- Denormalization is a legitimate, deliberate engineering tradeoff for read-heavy workloads where join cost genuinely matters — the mistake is denormalizing PREMATURELY, before measuring an actual bottleneck, not denormalizing at all. Treating normalization as a moral absolute rather than a starting point ignores real cases (analytics, caching, read replicas) where controlled duplication is the right engineering call.
- More tables (more normalization) always means better data integrity with no downsides.
- Every additional table requiring a join to answer a common query adds real complexity and query cost — normalization is a means to an end (avoiding update anomalies), not a goal to maximize for its own sake. Over-normalizing (splitting out tables for data that never actually needs independent updating) adds unnecessary joins and complexity without a corresponding integrity benefit.
When not to use it
- You're designing a new schema and haven't yet measured any actual performance bottleneck.
- Start fully normalized — this is the safer default that avoids update anomalies, and denormalize later only for specific, measured problems.
- A specific, measured query is a genuine bottleneck due to expensive joins, and the joined data changes rarely.
- Targeted denormalization — duplicating or pre-computing just the specific value causing the slowdown, while keeping the rest of the schema normalized.
Terms
- Normalization
- — Organizing data so each independent fact is stored exactly once, referenced by foreign keys where needed, to avoid update anomalies.
- Update anomaly
- — An inconsistency that arises when duplicated data isn't updated everywhere it appears, leaving contradictory values for what should be one fact.
- Denormalization
- — Deliberately introducing controlled data duplication to improve read performance, at the cost of more complex or error-prone updates.
- Third normal form (3NF)
- — A practical normalization target where every non-key column depends only on the table's primary key, not on other non-key columns.
In an interview
Why might a team deliberately denormalize a schema that started fully normalized?
- a specific query has been measured to be a genuine performance bottleneck due to expensive joins
- the joined data changes infrequently relative to how often it's read, making duplication's update cost low relative to its read benefit
- this is a deliberate, targeted tradeoff made after measuring a real problem, not a default starting design
Can you recall it?
What specific problem does normalization solve, and why is 'normalize until it hurts, denormalize until it works' good practical guidance?