Data Modeling for Scale: Entities & Access Patterns

Picture the whiteboard on day one of the marketplace. Someone draws customers, restaurants, dishes, orders, couriers, reviews — boxes, arrows, crow's feet. Every relationship lovingly captured. It's a beautiful model of the world, and drawing it feels like progress, because it is: nothing about the business is missing.

Six months later the app's hottest screen — a restaurant's menu, 2,000 requests a second — is assembled by a six-way join that touches five of those beautiful boxes. Nobody designed that query. Nobody designed any query. The team modeled the world and let the questions fend for themselves, and now the questions are taking revenge in p99.

Martin Kleppmann opens his data-models chapter with a line this course fully endorses: "Data models are perhaps the most important part of developing software." Not because boxes and arrows are hard — because the model decides which questions are cheap, and you've spent eleven lessons learning that engines answer cheaply only what the layout was shaped for. Work is proportional to the question. That law doesn't stop at the store's front door; it walks right into your schema.

So this lesson is the discipline the rubric assumed you'd have: model the queries, not the world. More honestly: model the world and the queries, in the right order, with numbers — and know exactly which part of your design serves which. There's an artifact for it, a six-step method around it, and a bench below where you'll run the whole thing on the marketplace yourself.

Start from the world, and you get the ER diagram: entities, relationships, attributes. Its virtue is correctness — it captures what things ARE and what must be true about them. Its blind spot is physics: an ER diagram contains no request rates, no latency budgets, no growth curves. It cannot tell you that the menu screen fires two thousand times a second while the refund flow fires twice a minute — and so it silently prices both the same.

Start from the queries alone, and you get the opposite failure. In the NoSQL world this works — the wide-column lesson's whole doctrine was the table IS the query — but imported carelessly into SQL it becomes premature denormalization: duplicating and pre-joining data that a well-placed index would have served, paying Codd's price (drift, double-writes, migration pain) and buying nothing for it. You gave away the relational bargain for free.

The discipline that scales is the synthesis, in a strict order:

1. Entities first, briefly — the nouns and the relationships that must stay true. This is the correctness skeleton, and it earns you the constraint shields from the relational lesson.
2. Then the questions, with numbers — every read AND write the product will fire, each with a rate, a latency budget, and a growth shape. This is the physics.
3. Then, and only then, the physical design — where each hot question gets made cheap.

Step 2 is where almost everyone under-invests, so it gets its own artifact —

One table, one row per question, six columns. Filling it in IS the modeling work:

access pattern	shape	R/W	rate	latency	growth
order by id	point, by key	R	500/s	ms	bounded
menu — dishes of a restaurant	list, by parent	R	2,000/s	ms	~40 rows
my recent orders (latest 20)	list, by user, sorted	R	300/s	ms	paginated
restaurant's orders today	range, by parent + time	R	50/s	~1s ok	bounded/day
place order (order+items+payment)	multi-row txn	W	800/s	ms	—
courier GPS ping	append	W	30,000/s	ms	UNBOUNDED
revenue by city, 90d	scan + aggregate	R	10/min	seconds	history

Read the columns like instruments. Rate separates the two-thousand-a-second question from the twice-a-minute one — they will not get the same treatment. Latency says who's allowed to be slow (the dashboard is; checkout isn't — the OLTP vs OLAP lesson, applied per-row). Growth is the column people skip and regret: "latest 20" is a bounded question, "all orders ever" is a different and unbounded one — say which you mean. And the GPS row's UNBOUNDED flag is doing real work: a pattern's growth shape can veto its residence entirely (that row is about to get evicted from the orders database — you know the rubric that does it).

Two details that mark a serious table. First, writes are patterns too — "place order" and "GPS ping" sit right there beside the reads, because every read optimization you're about to add will be paid for at write time. Second, the last row is OLAP-shaped, and the table makes that visible before anyone points a BI tool at production — you've seen that incident.

In an interview, this table is the move. Entities take thirty seconds; then: "let me list the access patterns with rough rates, because the patterns — not the entities — decide the physical design." That sentence is what a staff signal sounds like.

Here's the reframe that makes SQL modeling click, and that almost no course states plainly.

Keep the schema normalized — one fact, one place, the relational lesson's whole bargain. The schema's job is to model the world and keep it correct. Resist the urge to warp it toward any single query.

Then walk your pattern table, and for each hot read, make the pattern physical as an index:

• menu by restaurant → index on dishes(restaurant_id) — the 2,000/s six-way join collapses to one range read.
• my recent orders, latest 20 → composite index on orders(user_id, created DESC) — user's orders stored as a sorted run, so "latest 20" is one sequential read. (Recognize it? That's the wide-column lesson's clustering-key trick, living happily inside Postgres.)
• restaurant's orders today → composite on orders(restaurant_id, created).

An index is a little machine whose only job is answering one question shape fast — the layout bet, at index granularity. Alex DeBrie's DynamoDB vocabulary generalizes perfectly here: your columns are application attributes (they mean something to the business), your indexes are pure indexing structure (they exist only to make questions cheap). The schema stays the world; the indexes are the queries, made physical.

And now the bill, because you can hear it coming: every index taxes every write. One INSERT INTO orders no longer touches one heap page — it updates the primary key, the user composite, the restaurant composite… each a separate structure that must stay current. Three indexes ≈ four writes where there was one. This is why the pattern table lists writes: the read side of the table spends the write side's budget, and the bench below will make you watch that happen.

When does honest denormalization enter? When a measured hot pattern still can't be served by indexes — cross-entity reads at brutal rates, or read paths that must survive with one lookup. Then you duplicate deliberately, with your eyes open to drift and double-writes. That discipline — duplication as a designed artifact, not an accident — is exactly the next lesson.

A model that satisfies every pattern on paper can still die in production. Pressure-test it against the course's three named forces:

Skew concentrates. Your "by restaurant" patterns are averages; production is a hit parade. The top restaurant on a festival night is not 1/50,000th of the traffic — it's a double-digit percentage, all landing on the same index pages, the same cache lines, eventually the same shard. Ask of every by-parent pattern: what happens when one parent is 100× the median? (You've met this force twice — the hot key in the coat-check lesson, the hot partition in the cabinet lesson. It follows you here.)

Unbounded growth wounds. The GPS-ping row was flagged for a reason. Thirty thousand appends a second into the orders database means the orders database is now mostly GPS pings: the working set bloats, every backup and index carries dead weight, and vacuum falls behind. The growth column routes that pattern to a store built for appends — the time-series lesson's whole reason to exist, chosen by the rubric's Q1. A pattern's growth shape can veto its residence.

Fan-out multiplies. Some patterns touch one row per event; some touch N. "Notify every follower" or "update every cached menu copy" writes N rows for one action — and N grows with success. Every denormalized copy you add in the bench is a small fan-out you've signed up to maintain forever. Count the N before you commit; feeds and their fan-out trade-offs get their full treatment later in the course.

Skew, growth, fan-out. Three questions, thirty seconds each, and they catch the majority of models that were about to fail their first traffic spike.

One modeling decision sits above tables and indexes: which entities must change together?

When an order is placed, the order row, its line items, and the payment record move as one — all or nothing. That group is an aggregate (domain-driven design's word, and the document lesson's whole geometry): a cluster of entities with a single consistency boundary around it. The rubric asked this as its invariants question; modeling is where you draw the answer.

The rule of thumb: the aggregate is the transaction is the boundary. Inside it, invariants are enforced by the store (constraint shields, transactions — the machinery you get for free in the relational world). Across boundaries, you accept looser coupling — updates that arrive separately, reconciled later. Choosing where the line sits is choosing which mistakes are impossible and which are merely unlikely.

Keep aggregates as small as the invariants allow — a boundary around everything is a transaction around everything, and that's a lock queue wearing a design costume. The order+items+payment group earns its boundary; the courier's GPS trail, notably, does not (nothing breaks if a ping lands late — which is one more reason it was evicted).

Hold onto this idea. In the next lesson the boundary stops being an annotation on a diagram and becomes physical: in single-table NoSQL design, the aggregate literally becomes the item collection under one partition key.

The whole discipline, compressed to something you can run on a whiteboard in ten minutes:

1. Entities, briefly — nouns, relationships, what must stay true. (Marketplace: six boxes, three invariant groups.)
2. The pattern table, with numbers — every read and write, each with shape · rate · latency · growth. No numbers, no table. Estimates are fine; missing is not.
3. Draw aggregate boundaries where invariants live (order+items+payment). Small as possible.
4. Normalize the schema — the world, kept correct. Then index the hot patterns — the queries, made physical. Composite indexes for sorted, per-parent lists.
5. Pressure-test: skew (hot parent?), growth (unbounded rows? evict them), fan-out (who writes N for 1?). Check the write tax against the write patterns' budget.
6. Iterate on proof — a new pattern gets a row in the table first, then an index, then (only when measured) deliberate duplication.

Notice how much of this course the method quietly reuses: the family map told you what each engine answers cheaply; the OLTP/OLAP axis put the dashboard row in its place; the rubric evicted the GPS pings; the relational lesson supplied the shields inside your boundaries. Modeling is where all of it becomes your decisions on your data.

What the method hands you is a schema that models the world, indexes that model today's questions, and a table that prices tomorrow's. What it can't do alone is serve a workload whose store gives you no joins and no ad-hoc indexes at all — where the table itself must become the queries. That's DynamoDB-country, single-table design, duplication as doctrine: next lesson, same method, harder physics.