Caching & Databases · lesson 7 of 8 · about 26 min
Fewer duplicates or fewer joins: when each wins. See the anomalies a repeated fact causes, climb the normal forms, compute the read-versus-write break-even between joining and copying, decide how stale a derived copy may be, learn why history must be snapshotted, and watch hand-maintained copies drift apart. Tap any for a plain-language card.
Normalisation
The data below is a seeded set of 200 orders from 37 customers. Choose a design and an operation, and see how many rows must be touched and what is lost. The “buggy” setting models an update that finds only some of the copies, which is how real inconsistencies start.
With the wide table and the buggy update, a customer ends up with two different cities. In the normalised design the same operation touches one row.
One wide table: A buggy update touched 12 of 25 rows: 13 orders still show the old city.
Seeded data: 200 orders, 37 distinct customers. Row widths are illustrative (wide ≈ 60 bytes, orders ≈ 28 bytes plus a 36-byte customer row).
The normal forms
Most business applications aim for third normal form (or its slightly stricter cousin, Boyce-Codd), then deliberately break the rule in specific places for performance or history. The counter shows how many stored values are redundant copies in our 200-order dataset at each level.
Step from 0 to 3 and watch the redundant values fall to zero while the number of tables grows.
Rule: Everything in one table, possibly with lists inside a cell.
Unnormalised: Cells hold lists, so you cannot query or index individual items. 522 stored values are copies of facts held elsewhere in the same table.
Redundant values counted on the seeded dataset: customer name and city repeated per order until 3NF (2 per order beyond the first), product name repeated per line until 2NF.
The trade
If a page is read a thousand times for every change to the data underneath it, paying a small write penalty to make reads cheap is a good deal. If the data changes often, copying it everywhere is the wrong direction. The break-even is reads ÷ writes = (copies − 1) ÷ joins. Move the sliders to your own workload.
1,000 reads per 10 writes with 4 joins and 50 copies favours denormalising 3 to 1. At 500 writes it flips and normalising is 5 times cheaper.
At 100 reads for every write, the denormalised design costs 1,500 lookups a second against 5,010: copies are cheaper than joins here. The break-even is 12.3 reads per write.
Counts of lookups: normalised read = 1 + joins, write = 1; denormalised read = 1, write = copies. Real costs vary with caching and index depth.
Derived data
The cost is staleness. A view refreshed every T seconds is on average T ÷ 2 seconds old. If a row changes at rate u per second, the chance that a read sees an out-of-date row is 1 − e−u·T/2. Refreshing more often (or updating the copy synchronously with triggers or change-data-capture) lowers staleness and raises the load. Choose how fresh each view needs to be.
A row changing twice an hour viewed through a 5-minute refresh is stale for about 8% of reads. A daily refresh makes it stale more than half the time.
Refreshing every 5 min with a row that changes 2 times an hour, 8.0% of reads see a row that has already changed. That is fresh enough for most dashboards and listings.
Poisson updates: P(row changed since the last refresh) averaged over the refresh interval ≈ 1 − e^(−u·T/2).
Denormalising on purpose
So well-designed schemas denormalise deliberately: they snapshot the values that must not change onto the transaction (price, address, name at purchase), keep a reference to the source for reporting, and never overwrite the snapshot. The rule of thumb: normalise current facts, copy historical facts. Raise the price and compare the two ways of building the invoice.
With a 20% price rise, an invoice rebuilt by joining shows a total 20% higher than what the customer was charged. The snapshot is unchanged.
| Item | Qty | Price paid | Shown on the invoice |
|---|---|---|---|
| Lamp | 2 | 400 | 480 |
| Book | 3 | 250 | 300 |
| Pen | 10 | 40 | 48 |
Rebuilt from today's prices the invoice says 2,340 when the customer paid 1,950: history has been rewritten by a normal-looking join.
Three order lines with prices paid of 400, 250 and 40; the join uses the current price after the percentage rise.
Normalise or copy?
Normalise current facts, copy historical ones, derive read-optimised views from the truth, and use columnar storage for scans. Each answer is worth 20 XP.
Ask: is it a current fact or history? how read-heavy? who owns writes?
An invoice line must always show the price the customer actually paid.
Tip: press 1 to 4 to answer, Enter for the next one.
Who lives by this
Four published sources on normalising and copying.
relational model and normal forms
Codd's 1970 paper 'A Relational Model of Data for Large Shared Data Banks' introduced the relational model; his 1971 paper on further normalisation set out the normal forms as a way to remove redundancy and the update anomalies that come with it.
precomputed home timelines
Twitter engineers have described building each user's home timeline when a tweet is posted by writing it into followers' timeline caches (fan-out on write), because timeline reads vastly outnumber tweets, trading write amplification for cheap reads.
single-table design
AWS guidance for DynamoDB recommends designing tables around the application's access patterns, often placing related entities in one table with duplicated attributes so that a query returns everything in a single request, rather than normalising and joining.
materialised views
PostgreSQL's materialised views store the result of a query on disk and are refreshed on demand with REFRESH MATERIALIZED VIEW, giving fast reads of precomputed joins and aggregates at the price of data that is only as fresh as the last refresh.
Interview angle
Six questions, each worth 20 XP. Normalisation versus denormalisation is a staple of schema-design interviews.
Default to normalising current facts; copy where history or read speed demands it; say how the copies stay correct.
What is an update anomaly?
Tip: press 1 to 3 to answer, Enter for the next one.
Break it
The fix is structural, not heroic: one place is the single source of truth and the only writer; every other copy is derived from it (by events, change-data-capture or a view) and can be rebuilt; a periodic reconciliation job compares the copies and repairs differences; and a constraint or test fails when a new write path appears that bypasses the owner. Set how often paths miss a copy and how often a customer changes their data.
With 98% coverage and two changes a month, more than a third of customers are inconsistent after a year. Pick the single-owner design.
If 2% of updates miss a copy and customers change their data 2 times a month, 38% of customers have disagreeing copies after a year and 62% after two: about 620,815 of a million. It only gets worse because nothing repairs them.
P(consistent after n updates) = coverage^n, so P(inconsistent) = 1 − coverage^n. The single-owner row assumes reconciliation repairs problems and leaves a small residual.
Where to go next