Data Modeling
Normalization vs. Denormalization
An online shop needs to store its orders, and there are two honest ways to lay out the tables. Keep every fact in one place — the customer’s address lives in a single row, and every order points at it. Or copy the details onto each order, so reading a whole order is one straight scan. Both work. The difference only shows up the moment you do something with the data — read it, change it, or grow it.
Normalized — one fact, one place
| id | customer | product | qty |
|---|---|---|---|
| o1 | c1 | p1 | 1 |
| o2 | c2 | p3 | 1 |
| o3 | c1 | p2 | 2 |
| o4 | c3 | p1 | 1 |
| o5 | c1 | p4 | 1 |
| o6 | c4 | p3 | 1 |
| o7 | c2 | p2 | 3 |
and 4,793 more like these
| id | name | address | country |
|---|---|---|---|
| c1 | Ada Okonkwo | 14 Bridge St, Leeds | UK |
| c2 | Bo Lindqvist | 3 Hamngatan, Malmö | Sweden |
| c3 | Cira Duarte | 88 Rua das Flores, Porto | Portugal |
| c4 | Deniz Yilmaz | 5 Bahçe Sok, Izmir | Türkiye |
| id | name | category | price |
|---|---|---|---|
| p1 | Cast-iron pan | Kitchen | $39 |
| p2 | Linen apron | Kitchen | $24 |
| p3 | Chef knife | Kitchen | $68 |
| p4 | Stovetop kettle | Kitchen | $45 |
o5 → address read live from the order’s customer row — no copy here
Denormalized — one flat table
| id | customer | ship to | country | product | price | qty |
|---|---|---|---|---|---|---|
| o1 | Ada Okonkwo | 14 Bridge St, Leeds | UK | Cast-iron pan | $39 | 1 |
| o2 | Bo Lindqvist | 3 Hamngatan, Malmö | Sweden | Chef knife | $68 | 1 |
| o3 | Ada Okonkwo | 14 Bridge St, Leeds | UK | Linen apron | $24 | 2 |
| o4 | Cira Duarte | 88 Rua das Flores, Porto | Portugal | Cast-iron pan | $39 | 1 |
| o5 | Ada Okonkwo | 14 Bridge St, Leeds | UK | Stovetop kettle | $45 | 1 |
| o6 | Deniz Yilmaz | 5 Bahçe Sok, Izmir | Türkiye | Chef knife | $68 | 1 |
| o7 | Bo Lindqvist | 3 Hamngatan, Malmö | Sweden | Linen apron | $24 | 3 |
and 4,793 more like these
Ada Okonkwo’s address is copied onto every one of her orders — 34 rows at this size.
o5 → ship to 14 Bridge St, Leeds
1/5 The same orders, laid out two honest ways. On the left, every fact lives in one row and the orders point at it. On the right, the customer and product details are copied onto every order — see Ada’s address repeated down the table.
Storing every fact once means it can never disagree with itself, and you pay for that on every read. Copying facts where they are needed makes reads cheap, and you pay for it on every write — and in the copies you forget. You are choosing which problem you would rather have.
Go deeper ↓
Store every fact once and it can never contradict itself, but reads have to reassemble it. Copy facts where they are read and reads get cheap, but every copy is now a promise you keep by hand. The sim above is the whole idea; everything here is optional.
One fact, one place
Normalization is the discipline of storing each fact exactly once. The customer’s address is one value in one row; an order that needs it holds a key, not a copy. Change the address and every order reflects it immediately, because none of them ever held an address to begin with — they hold a pointer to where it lives.
The textbook version of this comes in normal forms, a numbered ladder. First normal form: no repeating groups, every cell holds one value. Second and third: every non-key column depends on the whole key and nothing but the key — if a column actually depends on something else, it belongs in that something else’s table. Boyce–Codd tightens the third rung. You rarely recite these at work, but they are all one instinct written out carefully: if a fact can be derived or looked up, do not store a second copy of it.
The cost is real and it is on every read. A question that spans the customer, the order and the product now touches three tables and has to join them back together. Well indexed, that join is a modest tax, not a disaster — but it is never free, and it is paid every single time the question is asked.
Copies, and the promises they carry
Denormalization deliberately puts a copy back where it will be read. The order row carries the customer’s name and address and the product’s price, so the whole order is one row and the report is one scan. Analysts get fast, simple queries; a service reading one object by its id gets it without a join the database might not even support well.
The price is that a fact now lives in many places, and keeping those copies in agreement is your job, not the schema’s. The sim shows the sharpest version — a copy you forgot you had — but the family is bigger:
- The update anomaly. Change a value that has been copied, miss one copy, and the data now disagrees with itself.
- The insertion anomaly. If a product’s details only exist on order rows, you cannot record a new product until someone orders it.
- The deletion anomaly. Delete the last order for a customer and, if their details lived only on order rows, the customer vanishes too.
None of these can happen in a fully normalized schema, because there is only ever one place for the fact to be.
What actually grows when the shop grows
It is tempting to assume the join gets catastrophically slow at scale and the copies stay a minor nuisance. Measured honestly, it is closer to the other way round.
The report’s extra cost is roughly (orders + customers + products) ÷ orders rows examined. As the shop grows, the orders dominate and that ratio trends toward one — the join penalty shrinks in relative terms. What balloons is the other side: a bulk address change goes from rewriting three rows to rewriting hundreds, a schema migration becomes a weekend, and every copy you didn’t know about is now a hundred wrong rows instead of one. Scale makes denormalization’s write cost and its correctness risk grow. The read gap it was supposed to justify stays a modest multiple.
The machinery that makes controlled denormalization safe
Most mature systems do both, on purpose: a normalized source of truth that owns every fact, and denormalized read models built from it for the things that read hot. What keeps the copies honest is machinery, not discipline:
- Materialized views — the database maintains the denormalized copy and refreshes it, so you query the fast shape without owning the sync.
- Change data capture — a feed of every write to the source, which downstream read models and search indexes and caches consume to stay current.
- The star schema — a data warehouse’s fact table surrounded by wide, slowly-changing dimension tables. It is denormalized by design, and safe because the warehouse is loaded in batches and is effectively read-only, so the update anomaly barely applies.
Deliberate copies that are not anomalies
Some duplication only looks like denormalization. An event log or an order’s line items capture values as they were at that moment — the price at time of sale, the address the parcel actually shipped to. Those must not change when the current value does; the copy is the whole point. The test is whether the copy is meant to track the original (a maintenance burden) or deliberately frozen (a historical record).
Where each choice is right
- Systems of record — an ERP, a banking core, an order-management system. Data changes constantly and a contradiction is unacceptable. Normalize: it is the default for anything that owns data.
- Anything with many-to-many relationships that keep growing — users, roles, permissions, tags. Normalization absorbs a new relationship as a new table; a flat layout has to be re-flattened every time the model grows.
- Regulated or audited data, where you must prove what a value was and when it changed. One fact in one place, with history as its own table, beats reconciling copies.
- Reporting and analytics — the star schema. Load in batches, read constantly, update-anomaly risk near zero. Denormalize.
- Read-heavy caches and read models. A product page assembled from a dozen tables, denormalized into one document and rebuilt when the source changes. Reads are the hot path; the rebuild is background work.
- Document stores fetched whole by id — a profile, a cart, a CMS page. One nested document avoids joins entirely, at the cost of updating a shared value in many places.
- E. F. Codd, “A Relational Model of Data for Large Shared Data Banks” (1970) — and the normal-form papers that followed.
- C. J. Date, Database Design and Relational Theory — normal forms without the folklore.
- Martin Kleppmann, Designing Data-Intensive Applications, ch. 2–3 — data models, and the storage trade-offs that make denormalized read paths worth the sync cost.
- Ralph Kimball and Margy Ross, The Data Warehouse Toolkit — the star schema and why analytics denormalizes on purpose.