Denormalization — when and why: Exercises — Coding deep dive

See the parent for the full derivation: Denormalization — when and why.

Intuition How to READ the boundary map above (guided walk-through)

Alt text: a white square chart, x-axis "read/write ratio $f_{r} / f_{w}$ ", y-axis "cost ratio $C_{sy n c} / C_{j o in}$ ", split by a gray diagonal $y = x$ into a green lower-right "DENORMALIZE" region and a red upper-left "STAY NORMALIZED" region, with four sample workloads plotted. Read it in three moves:

Locate your workload's x — how read-heavy is it? A page read 500× per write sits far right; a bank read only 1.2× per write sits near the left edge.
Locate your workload's y — how expensive is the sync versus the join it removes? A cheap counter sits low; a transactional bank balance sits high.
Which region is the dot in? Below the gray diagonal (green) → denormalize. Above it (red) → stay normalized. Worked read: the blue dot at $(2, 2)$ sits exactly on the line — that's L3.2's break-even, neither region. The orange arrow at the far right ( $f_{r} / f_{w} = 50$ , cost ratio $1.2$ ) is deep in green → strong yes. The red square at $(1.2, 2.5)$ is up in red → the bank of L5.1, a no.

Common mistake Two boundary cases the ratio test hides — read before Level 1

(A) The tie, $f_{r} / f_{w} = C_{sy n c} / C_{j o in}$ (exactly on the line). Here $Δ = 0$ : the read saving exactly cancels the write cost, so the compute is a wash. The strict inequality is >, not ≥, so do NOT denormalize on a tie — you gain nothing in speed but still take on all the extra costs the ratio ignores: more storage, sync code, race conditions, and drift risk. When the math is neutral, the non-math costs decide, and they always point to "stay normalized." (B) Zero writes, $f_{w} = 0$ . Then $f_{r} / f_{w}$ is division by zero — the ratio test is undefined and useless. Fall back to $Δ = f_{r} C_{j o in} - f_{w} C_{sy n c} = f_{r} C_{j o in} - 0 = f_{r} C_{j o in}$ , which is positive whenever you read at all. So truly write-once/read-only data is the easiest denormalize decision there is — with no writes, sync can never drift. (Watch out: "zero writes" must mean forever; a table written once at load and never again qualifies, a table written "rarely" does not — use small $f_{w} > 0$ for that.)

Level 1 — Recognition

Recall Solution L1.1

Pre-joined / merged column — a column copied from the parent table into the child to kill a join.
Pre-computed / derived column — stores an aggregate that would otherwise be recomputed.
Inline repeating / array column — a list stored in-row instead of via a junction table.
Materialized view — a cached result of an expensive query, refreshed on a schedule.

Recall Solution L1.2

Makes reads cheaper (fewer/no joins) and writes more expensive (you must keep the copies in sync). You are moving cost from the read path to the write path, not deleting it. The figure below shows this schema move concretely.

Intuition Reading the schema-move figure above

Alt text: left half shows two blue-outlined tables orders and customers joined by a red double arrow labelled "JOIN on every read"; a green arrow labelled "denormalize" crosses to the right half, showing one orange-outlined orders table that now also holds a customer_name (copy!) column, with a red arrow warning "keep copy in sync on write". Trace it left to right: before, a read must open two tables and match customer_id — the red arrow is the join tax paid on every read. After the green "denormalize" arrow, customer_name has been copied into orders, so a read now touches one table and the join vanishes. But look at the red arrow on the right: that copy is a second home for the same fact, so every write must now update both homes — the join tax didn't disappear, it moved to the write path (exactly the $C_{j o in} \to C_{sy n c}$ swap the whole page is about).

Level 2 — Application

Recall Solution L2.1

Compute both sides of $\frac{f _{r}}{f _{w}} > \frac{C _{sy n c}}{C _{j o in}}$ .

Left: $\frac{f _{r}}{f _{w}} = \frac{5000}{10} = 500$ .
Right: $\frac{C _{sy n c}}{C _{j o in}} = \frac{3}{2} = 1.5$ . Since $500 > 1.5$ , the inequality holds → denormalize. On the decision-boundary map this workload sits far to the right of the diagonal. Cross-check with net saving $Δ = f_{r} C_{j o in} - f_{w} C_{sy n c} = 5000 \cdot 2 - 10 \cdot 3 = 10000 - 30 = 9970$ units/day saved. Strongly positive.

Recall Solution L2.2

Set $Δ = f_{r} C_{j o in} - f_{w} C_{sy n c} = 0$ and solve for $f_{r}$ : $f_{r} = \frac{f _{w} C _{sy n c}}{C _{j o in}} = \frac{4000 \cdot 3}{2} = 6000 reads/day .$ So you need at least 6000 reads/day to justify it. Below that, staying normalized wins. This $f_{r}$ is exactly the point where the workload lands on the diagonal boundary — and, from the tie-case rule above, break-even alone is not a reason to denormalize; you'd want to be clearly past it.

Level 3 — Analysis

Intuition Reading the O(N)-vs-O(1) figure above

Alt text: a white chart, x-axis "number of comments $N$ " up to one million, y-axis "work per page view (rows touched)"; a red line rising diagonally is labelled " $O (N)$ : COUNT(*) scans all rows" and a flat green line near the bottom is labelled " $O (1)$ : read stored counter", with the red dot at one million marked "1,000,000 rows scanned per page view" and the green dot at one marked "1 lookup". The red line's slope is the cost of the normalized count: push $N$ right and the work climbs with it, unbounded. The green line is dead flat — the denormalized counter costs the same one lookup at $N = 5$ or $N = 5, 000, 000$ . The vertical gap between the two lines at your real $N$ is precisely what denormalizing buys back on each read; at a million comments that gap is a million rows.

Recall Solution L3.1

(a) Normalized read cost = views × N = $2000 \times 1, 000, 000 = 2, 000, 000, 000$ row-scans/day (2 billion). (b) Denormalized: each read touches 1 stored value ( $2000$ reads → $2000$ touches) and each write touches 1 row ( $500$ writes → $500$ touches). Total = $2000 + 500 = 2500$ row-touches/day. (c) Factor = $\frac{2 , 000 , 000 , 000}{2500} = 800, 000$ . Denormalizing cuts row work by 800,000×. This is why aggregate counts are the textbook denormalization case.

Recall Solution L3.2

(a) The diagonal boundary crosses at $f_{r} / f_{w} = 2$ . (b) $C_{sy n c} / C_{j o in} = 4/2 = 2$ . ✅ They match exactly — the break-even ratio is the cost ratio, which is precisely what the boxed rule says. Right of the line, denormalizing wins; left of it, stay normalized. And on the line (the blue dot) is the tie: $Δ = 0$ , so you would not denormalize there.

Level 4 — Synthesis

Recall Solution L4.1

Trigger (i) fits best. It runs inside the same transaction as the item change, so total_amount is always consistent atomically — no window where the order shows a wrong total. The transactional flow is drawn below.

App logic (ii) works but is fragile: if any code path (a script, an admin tool, a bulk import) writes order_items without going through that code, the total drifts. The invariant lives outside the database.
Scheduled refresh (iii) is wrong for "exact instantly": it leaves a staleness window between refreshes. That's fine for a dashboard, not for a checkout total. Materialized-view refresh trades freshness for cheaper writes — the opposite of what this requirement wants.

Recall Solution L4.2

(a) Left $= f_{r} / f_{w} = 10000/200 = 50$ . Right $= C_{sy n c} / C_{j o in} = 6/5 = 1.2$ . Since $50 > 1.2$ → denormalize. Net $Δ = 10000 \cdot 5 - 200 \cdot 6 = 50000 - 1200 = 48800$ units/day saved. (b) Add users.follower_count; guard it with a trigger on follows that does +1 on insert and -1 on delete, inside the same transaction. (c) Failure modes: (1) a follow inserted without firing the trigger → drift; (2) concurrent follows racing to update the same counter → lost updates (fix with an atomic count = count + 1, not read-modify-write); (3) double-firing on retries → over-count. A periodic reconciliation job that recomputes the true count is wise insurance.

Level 5 — Mastery

Recall Solution L5.1

(a) Left $= f_{r} / f_{w} = 3000/2500 = 1.2$ . Right $= C_{sy n c} / C_{j o in} = 20/8 = 2.5$ . Since $1.2 < 2.5$ , the inequality fails → do NOT denormalize. Net $Δ = 3000 \cdot 8 - 2500 \cdot 20 = 24000 - 50000 = - 26000$ units/day (a loss). On the map this workload sits left of the diagonal. (b) $C_{sy n c}$ is high because every balance change must be atomic, durable, and auditable; a cached balance would need locking, transactional coupling, and reconciliation for regulatory correctness — expensive per write. And $f_{w}$ is nearly as large as $f_{r}$ , so the sync cost is paid almost as often as the saving. (c) Keep the ledger as the single source of truth and compute the balance from it (optionally an indexed running total per account rather than a duplicated field). This is where Indexing beats denormalization: it speeds reads without introducing a second copy of the fact.

Recall Solution L5.2

(a) $Δ_{co m p u t e} = f_{r} C_{j o in} - f_{w} C_{sy n c} = 8000 \cdot 12 - 300 \cdot 90 = 96000 - 27000 = 69000$ units/day. (b) $Δ_{n e t} = Δ_{co m p u t e} - S = 69000 - 5000 = 64000$ units/day → still strongly positive. Denormalize (build the view). (c) The verdict is a clear yes: even after paying $5000$ /day for storage you net $64000$ /day. The knob to tune is refresh cadence. The dominant write cost is the refresh ( $f_{w} C_{sy n c} = 27000$ /day); if you batch refreshes — say once/hour instead of on every underlying write — you cut the effective $f_{w}$ that drives sync cost, pushing $Δ$ even higher, at the price of some staleness. Because this is a reporting query (an OLAP-style workload), slight staleness is acceptable, so trading freshness for a bigger net saving is the right move here.

Recall Solution L5.3

A good answer hits: (1) Normalize first — never start denormalized; measure to find an actually-slow hot read. (2) Estimate the read/write ratio $f_{r} / f_{w}$ and the cost ratio $C_{sy n c} / C_{j o in}$ ; denormalize only when the former clearly beats the latter (a tie, $Δ = 0$ , is not enough — the non-math costs break the tie toward "stay normalized"), and note that truly write-once data ( $f_{w} = 0$ ) is an automatic yes. (3) Choose a guard mechanism matched to the freshness requirement — trigger/transaction for must-be-exact-now, scheduled refresh for tolerable staleness. (4) Consider whether Indexing or a materialized view solves it without duplicating a fact. (5) Add reconciliation/monitoring because unguarded duplicates will drift into anomalies.

Connections

Denormalization — when and why — the parent this drills
Normalization (1NF 2NF 3NF BCNF) — always the starting point
Update Insert Delete Anomalies — the drift risk every solution must guard
Indexing — speed reads without a second copy (L5.1)
Materialized Views — cached-query denormalization (L4, L5.2)
Database Triggers — the exact-now sync mechanism (L4.1)
OLTP vs OLAP — why analytics tolerate stale denormalized data
CAP Theorem — distributed denormalization to dodge cross-node joins

#flashcards/coding