Start here: model the queries, not the data#
TL;DRthe 30-second version
- The inversion: relational design normalizes the data first and queries later. At scale you flip it — list the queries you must serve, then shape the data so each one is a direct lookup.
- Normalize to store each fact once (no update anomalies, less space). Denormalize — duplicate the fact — to avoid a join or a scan on the read path. That is the whole trade: read speed against write cost and consistency.
- The primary key (and, when sharded, the partition key) is the single most important decision. It decides what is one cheap lookup versus an expensive scan — and a bad key gives you hot partitions.
- A secondary index is a second access path to the same data. It makes a new query cheap, but every index is more work on every write.
- Rule of thumb: list your queries → pick keys so each query is a direct lookup → duplicate data where a read would otherwise join or scan → pay for it on the write side.
Most people learn databases in one order: draw the entities, split them into clean tables so no fact repeats, and assume that whatever question you ask later, a join will answer it. That works beautifully until the tables are huge and the traffic is high — then a query that joins three big tables, or scans a table looking for matching rows, becomes the slow part of the system. The problem isn't the database. It's that the schema was built to describe the data, not to serve the reads.
The fix is to invert the design order. Before you draw a single table, write down the access patterns — the concrete reads and writes the application actually performs. 'Get a user by id.' 'List a user's last 20 orders, newest first.' 'Look up an order by its short code.' Each one is a question the storage layer must answer cheaply. Only then do you design the tables and keys, and you design them so that every question on the list is a direct lookup rather than a search. AWS states this bluntly in its DynamoDB guidance: you shouldn't begin designing the schema until you know the questions it will need to answer.
The recipe: queries in, keys out#
The method has four steps, and they run in this order every time. The output isn't a diagram of the domain — it's a set of keys and tables chosen so the query list is cheap.
Step 2 is where most of the outcome is decided, because the key you choose is the difference between a lookup and a scan. Give a table a primary key of user_id and 'get user by id' is one lookup. But 'list a user's orders' against an orders table keyed by order_id is a scan — the order rows for one user are scattered, and the database has no direct path to them. Re-key the orders so a user's orders sit together (a primary key of user_id plus a timestamp, say) and that query becomes a single range read. Same data, different key, and the expensive query turned cheap.
Step 3 handles the queries that a good key still can't make cheap. If a read needs data from two entities — an order plus the customer's name to show on it — a normalized schema joins them at read time. Denormalizing means you store the customer's name on the order itself, so the read is one lookup with no join. You've duplicated the name, and now you owe a write: if the customer renames, you must update it everywhere you copied it. That's the bargain in one sentence — you moved the work from every read to the occasional write, which is a great deal when reads vastly outnumber writes.
A secondary index is the escape hatch for a query that needs a second access path. Your orders table is keyed by user_id, but you also need 'find an order by its short code.' Rather than scan, you build a secondary index on the code: a second sorted structure that maps code → order, maintained by the database. The lookup by code is now cheap too. The cost is real — every write to the table also writes to the index (see /btree for how that index is built and why each one adds write work). Indexes are how you buy extra cheap reads, and you pay in write amplification.
Go deeperUnder the hood: composite and covering indexes
A composite key packs several fields into one ordered key, most-significant first — for example (user_id, created_at). Because the key is sorted, a lookup on user_id alone still finds all of that user's rows as one contiguous range, and adding created_at orders them by time for free. The rule: put the field you always filter by exactly first, and the field you range-scan or sort by second. This is why 'a user's orders newest-first' can be a single range read rather than a fetch-then-sort.
A covering index goes one step further: it stores, alongside the indexed key, the extra columns a query returns, so the query is answered entirely from the index without a second hop back to the main table to fetch the row. It trades more space and more write cost for turning a two-step read into a one-step read — the same duplicate-to-avoid-work bargain, applied inside the index.
What each choice costs#
Every technique on this page buys a cheaper read with a more expensive write or more storage. Putting rough numbers on it is what makes the trade concrete in an interview.
- A denormalized copy costs one extra write per place you duplicated it. Copy a customer name onto their orders and a rename touches every order row — for a customer with 10,000 orders, that's 10,000 writes to save a join on every read. Worth it when reads dominate; ruinous when the copied field changes constantly.
- Each secondary index roughly adds one write per index per row written. A table with five indexes turns one logical insert into six physical writes. This write amplification is why you add indexes for the queries you have, not the queries you might have.
- A scan costs O(rows scanned), not O(rows returned). Finding 10 matching rows in a 10-million-row table by scanning reads 10 million rows; the same query with a key or index reads about 10. That factor-of-a-million gap is the entire reason keys matter.
- A join's cost grows with the size of the tables joined, and it can't be sharded away cleanly — joining two tables that live on different machines means moving data across the network. Denormalizing removes the join by paying storage up front.
PredictYou have a 50-million-row events table. A new screen needs 'all events for one account, newest first,' and accounts have at most a few thousand events each. The table is keyed by event_id. What do you change, and what does it cost?
Hint: The query has no cheap key today. What key makes one account's events a contiguous, already-sorted range?
Give the query a direct access path instead of a scan. Keyed by event_id, this query is a full scan of 50 million rows to find a few thousand — O(rows scanned), catastrophic. The fix is a key (or secondary index) of (account_id, created_at): now one account's events sit together, sorted by time, so the read is a single range scan of just that account's few thousand rows, already in newest-first order. The cost you accept is on the write side — every event insert now also maintains that index/key ordering (roughly one extra write per event), and a bit more storage. That's the trade in miniature: pay a small, fixed write cost forever to turn a million-row scan into a few-thousand-row range read. If you can make it the table's primary partition key (account_id) rather than a secondary index, even better — the events are physically co-located on one shard (see /sharding/sim).
Normalized vs denormalized, side by side#
Normalization and denormalization are the two shapes the same data can take. Normalize and each fact lives in exactly one place; denormalize and a fact is copied wherever a read needs it. Here is the same order-and-customer data under both, and what each makes cheap or expensive.
| Normalized | Denormalized | |
|---|---|---|
| Each fact stored | Once, in one table | Copied everywhere a read needs it |
| Read that spans entities | A join at read time | One lookup — data is already together |
| Update a shared fact | One write, one place | Many writes, every copy |
| Storage | Minimal — no duplication | More — duplicated data |
| Update anomalies | Avoided — single source of truth | Possible — copies can drift out of sync |
| Best when | Writes and reads are balanced; the fact changes often | Reads vastly outnumber writes; the fact rarely changes |
Neither is 'correct.' Normalization is the safe default because a single copy of each fact can't disagree with itself — the classic anomalies (update the same fact in some rows but not others, and the data now contradicts itself) simply can't happen. Denormalization deliberately reintroduces that risk in exchange for read speed, and you manage the risk by only duplicating facts that rarely change, or by updating every copy on write. The decision is per-query, not per-database: you normalize by default and denormalize the specific reads that a join or scan would make too slow.
Choosing the model for the access pattern#
The same query-first thinking also picks which kind of database fits. Relational, document, and wide-column stores each make a different access pattern cheap by default. Match the store to how you read, not to how the data 'looks.'
| Store | Shines when your reads are… | Because |
|---|---|---|
| Relational (Postgres, MySQL) | Flexible, ad-hoc, join-heavy; queries you can't fully predict | Normalized tables + joins + a query planner answer questions you didn't design for |
| Document (MongoDB, DynamoDB items) | Whole-object reads — fetch one aggregate (a user + their profile + settings) at once | The object is stored together, so one read returns it with no join |
| Wide-column (Cassandra, Bigtable, HBase) | Known key + huge write volume + range scans within a partition | The partition key co-locates a row's data; writes are cheap appends |
The dividing line is how much you know your queries in advance. A relational database is the right default when the access patterns are still shifting or genuinely varied — it will answer new questions with a new query, no reshape needed, and you pay for that flexibility with join cost at scale. A document or wide-column store rewards you when the access patterns are few and known: you pre-shape the data to those exact reads and get very cheap, very scalable lookups, but a query you didn't design for can be awkward or impossible without reshaping. Pick the flexible store when you don't yet know the queries; pick the pre-shaped store when you do and you need them cheap at scale.
Go deeperUnder the hood: the CQRS / read-model split
Taken to its conclusion, pre-computed views become a full architectural pattern: keep one normalized write model as the source of truth, and derive one or more denormalized read models shaped to each query. Every write updates the write model, then propagates (often asynchronously) into the read models. This is CQRS — Command Query Responsibility Segregation — the write side and read side are separate schemas optimized for their opposite jobs. It buys the fastest possible reads and independent scaling of reads versus writes, at the cost of complexity and eventual consistency: a read model can lag the write model for a short window after a change.
In the wild
- DynamoDB single-table design (championed by AWS's Rick Houlihan) is the query-first method taken to the extreme: many entity types — users, orders, order-items — share one table, with the partition and sort keys overloaded so that one query returns a whole related group (a user and all their orders) in a single request. There are no joins because the data that's read together is stored together.
- Instagram and Twitter/X feeds are pre-computed views: when you post, the write fans the post out into each follower's timeline (a denormalized copy per follower), so reading a feed is a cheap lookup of an already-built list rather than a query that gathers and ranks posts on the fly.
- Cassandra's whole modeling philosophy is 'one table per query': you are told to design a table for each access pattern and duplicate data across them freely, because the partition key is what makes a read a single-node operation and a scan across partitions is what you must avoid.
- Relational systems keep denormalized copies too — a cached like_count or comment_count column on a post, updated on write, so the common read (show a post with its counts) never has to aggregate. It's the same store-the-answer trade inside a normalized database.
Pitfalls & gotchas
What's a hot partition, and how does a key choice cause it?
A hot partition is one shard that receives far more traffic than the others because too many rows — or too many reads — share its partition key. Keying by something low-cardinality or skewed (a status field, today's date, a handful of celebrity accounts) funnels load to one machine while the rest idle, and that one shard caps your throughput. The fix is a higher-cardinality or better-spread key so load fans out evenly (see /sharding/sim).
If denormalizing is faster, why not denormalize everything?
Because every duplicate is a write you now owe forever, and a chance for copies to disagree. Denormalize a field that changes often and a single logical update fans out into thousands of writes and a window where copies are stale. Normalize by default; denormalize only the specific reads a join or scan makes too slow, and prefer duplicating facts that rarely change.
Isn't adding an index basically free? Just add one per query.
Reads get cheaper, writes get more expensive. Each secondary index is another structure the database must update on every write, so five indexes make one insert into roughly six physical writes. Indexes also take space and can slow down bulk loads. Add indexes for the queries you actually run, not the ones you imagine you might.
The classic mistake — what does 'modeling the data instead of the queries' look like?
It looks like a beautifully normalized schema that nobody can query cheaply: the entities are perfectly separated, but the screen you have to build needs to join four of those tables and sort the result, and it's slow. You modeled the world instead of the reads. The tell is discovering your access patterns after the schema is set, then fighting the schema to serve them.
In a document/single-table design, what's the sneaky failure mode?
Unbounded item collections. If you co-locate a parent with all its children under one partition key (a user with all their events), a parent that grows without limit makes that one partition huge and hot. Watch for one-to-many relationships where the 'many' has no ceiling, and shard or cap them before they blow up a single partition.
QuizA team stores a user's display name once (normalized) and joins it onto every message when rendering a chat. At scale, that join is the bottleneck: chats are read constantly, but users rename themselves maybe once a year. What's the right move?
- Add more indexes on the messages table until the join is fast.
- Denormalize — copy the display name onto each message on write — accepting that a rename must update the copies.
- Keep normalizing; joins are always the correct relational choice.
- Switch the whole system to a document database to avoid all joins.
Show answer
Denormalize — copy the display name onto each message on write — accepting that a rename must update the copies. — This is the textbook case for denormalization: reads (rendering messages) vastly outnumber writes (renames), and the duplicated fact — the display name — almost never changes. Copying the name onto each message turns every read into a single lookup with no join, and the cost you accept is that a rename must update the copies, which happens about once a year per user. Adding indexes doesn't remove the join itself. Staying fully normalized keeps the bottleneck. And swapping databases wholesale is a huge change to fix one query — the point of query-first modeling is that you denormalize the specific read that's slow, not that you throw out the store.
In an interview
Data modeling questions reward the inversion. When you're asked to design storage, don't reach for entities and normal forms — reach for the query list. Saying 'first let me write down the access patterns' out loud is the signal that you model at scale, not just in a textbook.
- Lead with the access patterns. Enumerate the concrete reads and writes before drawing any table. Every later decision points back to this list.
- Justify each key by a query. For every important read, say which key makes it a direct lookup — and call out any read that would be a scan so you can fix it with a key, an index, or denormalization.
- Name the trade explicitly: normalize for one source of truth and cheap writes; denormalize for cheap reads at the cost of write work and possible staleness. Then say which side your workload favors and why (usually reads dominate).
- Watch the partition key for hotspots. When sharded, state what the partition key is and argue it spreads load evenly — flag any skew that would create a hot partition.
- Reach for pre-computed views when a read is far hotter than its data: store the answer (a counter, a materialized feed) and update it on write. Mention CQRS only if they push on the read/write split.
PredictAn interviewer says: 'Design the storage for a URL shortener. It gets 100x more redirects (reads) than new links (writes).' How does query-first modeling shape your answer?
Hint: Which single query dominates by 100x, and what primary key makes exactly that query one lookup?
Start from the two access patterns, not the entities. The reads are: (1) redirect — given a short code, get the long URL, which happens 100x as often as anything else; and (2) occasionally, list the links a user created. So the hot query is 'short code → long URL,' and it must be a single, direct key lookup. That dictates the primary key: key the links table by the short code itself, so a redirect is one lookup, no scan, no join (see /btree/sim for why a keyed lookup is a few reads). For 'a user's links,' add a secondary access path keyed by user_id rather than scanning. Because reads so dominate writes, this is a case where you happily pay a little on the write side — an extra index, and you might even denormalize the click count as a counter updated on each redirect rather than aggregating a log on read. The strong-answer framing: I designed the key around the 100x query, made it a direct lookup, and gave the secondary query its own path — the schema is the answer to the access pattern.
References
- Designing Data-Intensive Applications, Ch. 2-3 — Kleppmann on data models (relational vs document vs graph) and on normalization vs denormalization / locality.
- AWS — NoSQL Design for DynamoDB — The official 'identify your query patterns first' guidance — don't design the schema until you know the questions.
- Alex DeBrie — The What, Why, and When of Single-Table Design — The canonical write-up of query-first, single-table modeling in DynamoDB (Rick Houlihan's method, explained).
- Cornell Virtual Workshop — Database Normalization — 1NF/2NF/3NF and the update, insert, and delete anomalies normalization prevents.
- Cassandra — Data Modeling by Query — The 'one table per query, duplicate freely' philosophy of wide-column modeling.