Choosing a data model
Relational, document or wide-column: how do you choose, and when should you copy data instead of joining it?
Will the monthly totals survive a document store?
The kitchen’s ledgers now live in one PostgreSQL database. Orders, order lines, dishes, cooks and customers sit in separate tables, linked by ids, the way the textbooks teach it.
Now someone proposes replacing all of that with a document database, storing each order as one JSON document. There would be no joins and no migrations, and the order page would read one document and have everything it needs.
The proposal has a point. To print one kitchen slip, the server joins five tables: the order, its lines, the dishes, the cook and the customer’s address. That takes only a few milliseconds, but it is five lookups for something that, on paper, is a single sheet. Look at a paper slip from the morning rush: three dishes, their prices, the cook and the address, all written out in one place. The kitchen copies the day’s menu onto every slip and reads the slip, not the menu book. Nobody walks to the office to look up a price while the dal is on the stove.
The accountant has two questions. On the first of every month she pays each of the 300 home cooks their share of what they sold. If every order is its own little document, where do her totals come from? And when a cook corrects the spelling of her name, does someone change it on two thousand slips?
Do the sum before reading on: a cook who sells 200 boxes a month appears in how many orders over a year?
The slip and the ledger each point to a real need, and the two needs pull the schema in opposite directions. The slip wants everything about one order copied into one place. The ledger wants each fact stored once, so totals and corrections stay right.
Pause and predict The proposal wants one document per order. The accountant wants monthly totals and one place to fix a name. What should decide the schema?
Two obvious designs, two ways to break
There are two tempting extremes, and each one breaks in a way the other does not.
- Normalisation
- Store each fact once, in the one table it belongs to, and link to it by id everywhere else. A cook’s name lives in the cooks table; orders hold only the cook’s id.
- For example: Correcting a name is one update to one row. Every slip shows the new name, because every slip looks it up.
Extreme one: copy nothing. The first schema links every order line to the dish, and the dish holds the price. In October the kitchen raises dal from ₹90 to ₹100.
Pause and predict In November the accountant reruns September’s totals. What does she get?
Extreme two: copy everything. The document version carries the cook’s name, the dish names and the address inside each order. Reading a slip is one read. But a cook who sells 200 boxes a month appears in 2,400 orders over a year. Correcting her name means 2,400 writes, and if the job stops halfway, some slips show the old name and some the new.
- Denormalisation
- Deliberately keeping a copy of a fact next to the data that reads it, so a read needs no join.
- For example: The cook’s name written into every order document.
The way out is a question asked about each fact: who reads it, how often, and must it change when the original changes?
A mental model
That last row is the principle. The daily menu is copied onto every order slip because the kitchen reads the slip, not the menu book. You choose a data model by listing the reads and writes first, then picking the shape that makes the common ones cheap and the rare ones possible.
The four families of model
| Model | Shape | Good at | Hard at |
|---|---|---|---|
| Relational | Tables of rows, linked by ids, joined when read | Many-to-one and many-to-many links, new questions in SQL, transactions across rows | Reading a deep tree in one go (several joins) |
| Document | Self-contained trees (JSON), read and written whole | One-to-many data read together: an order with its lines. Fields that vary per record | Many-to-many links and updating a fact copied into many documents |
| Wide-column, single table | Items grouped by a partition key and sorted by a sort key, designed per question | Known questions at very large scale, each answered by one key lookup | Any question you did not design a key for |
| Graph | Nodes and edges | Following links many steps deep: who referred whom, which customers share a building | Bulk totals over everything |
Two ideas from Martin Kleppmann’s Designing Data-Intensive Applications make the document model precise. Locality: a document is stored together, so reading the whole order is one read. And schema-on-read versus schema-on-write: a relational database checks every row against the table’s schema when it is written; a document store accepts whatever is written, and the code that reads it must cope with every shape that was ever stored. Neither removes the need for a schema. It only moves where the checking happens.
The wide-column style reverses the usual order of work: you list the questions first and design the keys from them. Amazon’s DynamoDB guide puts it plainly: you should not start designing the schema until you know the questions it will need to answer. Each question gets a key, and anything the application needs together is stored together, often in a single table.
The same five questions, counted
At today’s size: 60,000 orders a month, 3 lines each, 300 kitchens, a year of history.
| Question | Relational | Document (cook name copied) | Wide-column, single table |
|---|---|---|---|
| One slip | 9 rows, 4 joins | 1 document | 4 items, 1 partition |
| Last 10 orders | 40 rows, 1 join | 10 documents | 10 items |
| Monthly payouts | 240,000 rows | 60,000 documents | 300 items |
| Correct a name | 1 row | 2,401 documents | 2,401 items |
| Unplanned: revenue by dish | 540,000 rows | 180,000 documents | 2,883,600 items (full scan) |
| Writes per new order | 4 rows | 1 document | 5 items |
Where the numbers come from. A cook’s orders in a year: 60,000 × 12 ÷ 300 = 2,400, plus her own record. Monthly payouts, relational: 60,000 orders + 180,000 lines. The wide-column table answers payouts from 300 counters, but only because every order also updated one: that is the fifth write. The full scan is every item in the table: 60,000 × 12 × (1 order + 3 lines) + 300 × 12 counters.
Read it with care. Nine rows found through primary-key indexes is a few milliseconds; a slip is not slow in PostgreSQL. A scan of 240,000 rows once a month is a fine nightly report and a terrible page. The table shows which questions get harder as the business grows. It does not show which model is faster for any single question.
Move the orders to a document store?
Two goals pull against each other: a slip that is one read, and totals and corrections that can never be wrong.
At this size, joins are cheap and the accountant’s questions are the ones that cannot be wrong. The slip’s one real need, that it shows what was charged, is met by copying the price and dish name onto each order line when the order is placed. That copy is a snapshot, so it never needs updating. Allergy notes and add-ons, whose shape varies, can go in a jsonb column.
The same method gives a different answer elsewhere. When the news feed arrives in lesson 4.3, the read is “show me today’s menus from everyone I follow”, read millions of times, and a copied, pre-built feed per customer is the right model.
What to remember
- Write down the reads and writes first. Choose the model that makes the common ones cheap and keeps the rare ones possible.
- Normalise living facts (names, addresses, current prices) so each has one place to update.
- Copy on purpose. A snapshot (the price paid) is copied once and never updated. A cache (a cook’s name on a slip) must be kept in step, and has a cost on every change.
- Documents suit trees read whole; relational suits links and new questions; single-table wide-column suits known questions at huge scale; graphs suit paths.
List the reads and writes first. Copy what must stay frozen; link what must change in one place.
In the real world
Real systems rarely use one model for everything. A common shape is a relational database as the source of truth, with copies built for particular reads: a search index for typing “pa…”, a cache for the menu, a pre-built feed per customer. Those copies are rebuilt from the source when they go wrong. Later parts build several of them, and the outbox in lesson 6.5 is how they stay in step.
Our modeller counts rows and items, not milliseconds. Real costs depend on indexes, memory and row sizes, and a document limit matters too: one MongoDB document can be at most 16 MiB, so a list that grows without end (every order a customer ever placed) belongs in its own collection, not embedded.