Denormalization — when and why: Visual walkthrough

Step 1 — Two paths through the database

WHAT. Every table design forces two kinds of traffic: reads (someone asks "show me this data") and writes (someone changes data — insert, update, delete). Think of the database as a building with two doors: a READ door and a WRITE door.

WHY. Before we can talk about cost, we must see that every choice we make helps one door and hurts the other. There is no design that makes both doors free. This tension is the entire subject.

PICTURE. In the figure the cyan arrow is the read path, the amber arrow is the write path. Normalized design makes the write door wide (easy) and the read door narrow (slow). Denormalization slides the tuning knob the other way. (Figure: a database box with a cyan READ arrow entering on the left and an amber WRITE arrow leaving on the right, captioned that normalization widens WRITE and narrows READ.)

Step 2 — Why a read costs something: the JOIN

WHAT. In a normalized schema, one fact (like an order and its item prices) is split across several tables. To answer a read, the database must stitch the tables back together — that stitching is a JOIN.

WHY — why a JOIN and not just "look it up"? Because normalization deliberately scattered the pieces so that no fact is stored twice. The price of that safety is that reading now requires re-assembly. We want to measure exactly how expensive that re-assembly is.

PICTURE. Two tables, orders and order_items. To build one order summary the engine walks the child rows and matches them by order_id. Every matched row is work. (Figure: an orders table on the left and an order_items table on the right, with amber arrows matching rows by order_id, captioned that the matching is the read cost.)

Step 3 — How big is a JOIN? Counting rows

WHAT. Let us actually count. Join a table of $R$ rows with a table of $S$ rows using the simplest method (a nested loop): for every row on the left, scan every row on the right.

WHY. We need a number, not a feeling. Counting turns "joins are slow" into " $C_{j o in}$ scales like $R \times S$ ", which is what makes the final inequality quantitative.

PICTURE. A grid: $R$ rows down, $S$ rows across. Every cell is one comparison. The whole rectangle is the work — that is $R \times S$ cells. (Figure: a shaded 5-by-7 grid of cells, each labelled as one comparison, with the total work R×S = 35 written underneath.)

Common mistake The nested loop is the

worst case, not the only one Why it feels right: the $R \times S$ rectangle is easy to picture, so it's tempting to think all joins cost that much. The fix: real engines pick smarter algorithms. With an index on the right table the inner scan shrinks from $S$ to $lo g S$ , giving $C_{j o in} \sim R lo g S$ . With a hash join the engine first builds a lookup table of one side, then each row of the other side finds its match in one hop — total work $C_{j o in} \sim R + S$ , far smaller than $R \times S$ . A merge join on pre-sorted inputs is likewise $\sim R + S$ . So $C_{j o in}$ ranges from the huge $R \times S$ down to the cheap $R + S$ — but in every one of these it still grows with the row counts and is never truly zero while both tables have rows. That single fact — " $C_{j o in} > 0$ whenever there is data to join" — is all the derivation needs. (This is exactly why Indexing is the other way to cut read cost — without adding redundancy.)

Step 4 — Why a write costs something once we denormalize: the SYNC

WHAT. Denormalization copies a fact into a second place (e.g. storing total_amount on the order instead of summing items). Now, whenever the original changes, we must update the copy too. That maintenance is sync.

WHY. A copy that is never maintained silently rots — the two versions disagree, which is an update anomaly. So denormalization forces extra work onto the write path. We must price that work to know if the trade is worth it.

PICTURE. One write now fans out: change the source cell (amber), then a trigger must also fix every duplicate cell (amber dashed). Each extra hop is cost. (Figure: an amber source cell on the left with dashed amber arrows fanning out to three cyan duplicate copies on the right, captioned that the trigger must fix every copy.)

Step 5 — Weighing the two paths: frequencies

WHAT. A cost per event only matters multiplied by how often the event happens. Introduce $f_{r}$ = number of reads per unit time, and $f_{w}$ = number of writes per unit time.

WHY — why multiply by frequency? Because a cheap-but-constant read (small $C_{j o in}$ ) paid a million times can outweigh an expensive write (big $C_{sy n c}$ ) paid once. The heart of the whole argument is total cost = per-event cost × how many events.

PICTURE. Two stacks of coins. Read stack: $f_{r}$ coins, each of height $C_{j o in}$ . Write stack: $f_{w}$ coins, each of height $C_{sy n c}$ . Denormalizing removes the read stack but grows the write stack. (Figure: a tall cyan stack of many short READ coins beside a short amber stack of a few tall WRITE coins.)

Step 6 — Turning $Δ > 0$ into the boxed rule

WHAT. We denormalize only when we come out ahead, i.e. $Δ > 0$ . Now rearrange that into a clean ratio.

WHY. A ratio is easier to judge on sight than a difference. "Do I read 100× more than I write?" is a question a human can answer; " $Δ$ " is not.

PICTURE. A balance scale. Left pan: the read/write ratio $f_{r} / f_{w}$ . Right pan: the cost ratio $C_{sy n c} / C_{j o in}$ . Denormalize when the left pan wins. (Figure: a balance scale tilting left, its left pan labelled f_r/f_w in cyan and its right pan labelled C_sync/C_join in amber.)

Formula The algebra, one move per line

To divide safely we need both divisors strictly positive. Two boundaries must first be ruled out and are handled separately in Step 7:

$f_{w} = 0$ (no writes) — cannot divide by it; treated on its own.
$C_{j o in} = 0$ (nothing to join — e.g. one of the tables is empty, so the read touches no rows) — then there was no read cost to remove in the first place, and $Δ = - f_{w} C_{sy n c} \leq 0$ : denormalizing can only lose. Ruled out here.

For the main derivation assume $f_{w} > 0$ and $C_{j o in} > 0$ (some writes happen and the join really touches rows). Both are then strictly positive, so we may divide by them and the inequality direction is preserved. $f_{r} C_{j o in} - f_{w} C_{sy n c} > 0$ $f_{r} C_{j o in} > f_{w} C_{sy n c} (move the write term to the other side)$ $\frac{f _{r}}{f _{w}} > \frac{C _{sy n c}}{C _{j o in}} (divide both sides by f_{w} > 0 and by C_{j o in} > 0)$ Dividing by a positive number never flips " $>$ ", so the direction is safe. The boxed result: $\frac{f _{r}}{f _{w}} > \frac{C _{sy n c}}{C _{j o in}}$

Step 7 — Edge and degenerate cases (never leave a gap)

WHAT. We check what the rule says at its extremes, so no real scenario surprises you — including the boundaries $f_{w} = 0$ , $C_{j o in} = 0$ , $C_{sy n c} = 0$ , and the "no load" case we set aside earlier.

WHY. A formula you trust must survive its own boundaries. Each corner below matches a real design decision.

PICTURE. The same balance scale in four tilt states. The left pan always carries the read side $f_{r} / f_{w}$ (cyan); the right pan carries the cost side $C_{sy n c} / C_{j o in}$ (amber). Top-left tilts hard left (denormalize wins); top-right tilts right (never); bottom-left tilts right (join already cheap, skip); bottom-right slams right (sync infinite, stay normalized). Read each little scale's tilt as the decision. (Figure: a 2×2 grid of small balance scales, one per corner case, each tilting the way that case decides, with its verdict printed beneath.)

Worked example The corner cases

Case	Meaning	Rule says
$f_{w} = 0$ (read-only report / OLAP warehouse)	can't divide by $f_{w}$ ; but $Δ = f_{r} C_{j o in} \geq 0$ directly, so denormalizing never loses	Always denormalize — no write ever pays $C_{sy n c}$ . Star schemas live here.
$f_{r} = 0$ (write-only log)	left ratio $= 0$	Never denormalize — you'd pay sync for reads that never come.
$f_{r} = 0$ and $f_{w} = 0$ (no load at all)	$Δ = 0$ ; the ratio is the meaningless $0/0$	Vacuous — nothing is queried or changed, so any design is equally fine. Decide on other grounds (storage, clarity).
$C_{j o in} = 0$ (empty table / no rows to join)	no read cost to remove; $Δ = - f_{w} C_{sy n c} \leq 0$	Never denormalize — you'd add sync cost to save nothing.
$C_{sy n c} = 0$ (duplicated value never changes — truly static)	right ratio $= 0$ ; $Δ = f_{r} C_{j o in} \geq 0$	Always denormalize — a free copy, no write ever pays to maintain it.
$C_{j o in}$ very small but $> 0$ (tiny / well-indexed / hash-joined tables, cost near $R lo g S$ or $R + S$ )	right ratio very large	Don't bother — the join is already cheap; use Indexing instead.
$C_{sy n c} \to \infty$ (bank balance, must be exact & transactional)	right ratio huge	Stay normalized — matches parent's banking-ledger example.

The one-picture summary

Everything collapses to a single frame: two coin stacks feeding a balance scale, whose tilt is the decision. (Figure: the cyan f_r·C_join "saved" stack on the left and the amber f_w·C_sync "paid" stack on the right, both feeding a central balance scale, with the boxed inequality written above.)

Recall Feynman retelling — the whole walkthrough in plain words

A database has two doors: a reading door and a writing door. When you keep everything tidy and unrepeated (normalized), writing is easy but reading is slow, because to answer a question you must run around several tables and stitch them together. That stitching can cost as much as $R \times S$ steps (dumb nested loop) or as little as $R + S$ (a clever hash or merge join) — but as long as there are rows on both sides it costs something. That per-read cost is $C_{j o in}$ .

To make reading fast, you keep a ready-made copy of the answer sitting right where you need it (denormalize). Now reading skips the stitching. But every time the original changes, you must also fix the copy — that per-write cost is $C_{sy n c}$ .

Which is better? Multiply each cost by how often it happens: reads happen $f_{r}$ times, writes $f_{w}$ times. You save $f_{r} \times C_{j o in}$ but pay $f_{w} \times C_{sy n c}$ . Come out ahead when the savings beat the payment — and cleaning up that comparison (assuming some writes happen and the join actually touches rows, so you can divide) gives the one rule to remember: denormalize when you read much more than you write, and only when the sync is cheaper than the join it replaces.

At the corners: pure reports (no writes) or a truly static copy (no sync) → always denormalize; pure logs (no reads) or an empty table (no join to save) → never; nobody using it at all → doesn't matter; already-cheap joins → skip it; must-be-exact bank balances → stay normalized.

Connections

Denormalization — when and why — the parent result we re-derived
Normalization (1NF 2NF 3NF BCNF) — why the data was split in the first place
Update Insert Delete Anomalies — the drift $C_{sy n c}$ exists to prevent
Indexing — shrinks $C_{j o in}$ without any redundancy
Materialized Views — the "ready-made copy" done by the engine
Database Triggers — the machinery that pays $C_{sy n c}$ automatically
OLTP vs OLAP — the $f_{w} = 0$ corner where you always denormalize

In the cost model, what does

C_{j o in}

represent?

The extra work a single read pays to stitch normalized tables back together; ranges from

R \times S

(nested loop) down to

R + S

(hash / merge join), and

R lo g S

with an index.

In the cost model, what does

C_{sy n c}

represent?

The extra work a single write pays to keep duplicated data consistent.

Why multiply costs by

f_{r}

and

f_{w}

Because total cost = per-event cost × how often the event happens; a cheap read paid a million times can outweigh one expensive write.

What two boundaries must be ruled out before dividing the inequality?

f_{w} > 0

(some writes happen) and

C_{j o in} > 0

(the join actually touches rows); the

f_{w} = 0

C_{j o in} = 0

C_{sy n c} = 0

and no-load cases are handled separately.

When

C_{sy n c} \to \infty

, what should you do?

Stay normalized — the right ratio explodes, so the inequality fails (e.g. a transactional bank balance).

When

C_{sy n c} = 0

, what should you do?

Always denormalize — a free static copy, since no write ever pays to maintain it and

Δ = f_{r} C_{j o in} \geq 0