System designCore10 min
Data modeling & denormalization
A data model decides which questions are cheap and which are expensive, long before any code runs. Normalizing stores every fact once and keeps the data honest; denormalizing copies facts to where they are read and makes reads fast. Good models do both, on purpose, and plan how every copy stays correct.
1 · The starting point
Model the questions as well as the things
A podcast app has 2 million listeners, 50,000 shows and 3 million episodes. The things it stores, its entities, are easy to list. So are the relationships between them, and each kind shapes the model differently:
Relationship | Kind | Typical size |
|---|---|---|
A show has episodes | One-to-many | Hundreds per show, growing forever |
A listener subscribes to shows | Many-to-many | About 20 per listener; up to 400,000 per show |
A listener plays episodes | Many-to-many, as events | Billions of plays in total |
An episode has comments | One-to-many | Usually dozens; tens of thousands on a hit |
Entities and relationships are half the model. The other half is the access patterns: the reads and writes the app actually performs, and how often. For the podcast app the hot ones are the show page (the show, its latest episodes and its subscriber count), each listener's Up Next list of new episodes from their shows, and recording plays. A model that is elegant on paper but makes the show page count 400,000 rows on every view is a bad model.
Which database fits these shapes is the subject of Relational vs NoSQL; this page is about shaping the data inside it.
2 · Normalization
A normalized model stores each fact once, so it can't contradict itself
Normalization splits data so that every fact lives in exactly one place, and everything else refers to it by key. A show's title lives in shows; episodes and subscriptions refer to the show by show_id:
The show's title is stored once. Renaming a show changes one row, and every episode and subscription sees the new name.
Copy the title into every episode row instead, and three kinds of trouble, called anomalies, appear:
Anomaly | In the podcast app |
|---|---|
Update | The host renames the show; 600 episode rows must change, and a half-finished update leaves two titles |
Insertion | A new show can't be recorded until it has an episode to carry its title |
Deletion | Deleting a show's last episode also deletes the only record of the show |
3 · Denormalization
Copy data to where it is read, when the read is hot
Normalized data is honest, but reads pay for it. The show page for a popular show displays "400,000 subscribers". Normalized, that number is a count over the subscriptions table on every view: at 2,000 views a minute, 400,000 × 2,000 = 800 million rows counted a minute, to print a number that changes a few times a minute. Store the count on the show row, add 1 on every subscribe and subtract 1 on every unsubscribe, and the page reads one row.
That is denormalization: deliberately storing a fact in more than one place, or storing a value that could be computed, so that a hot read is cheap. The common forms:
Technique | Podcast example | Fits when |
|---|---|---|
Copied field | The show's title and artwork stored in each Up Next entry | The source rarely changes |
Stored count or total | subscriber_count on the show | Reads vastly outnumber changes |
Embedded subset | The 3 latest episodes stored inside the show's record | The page needs a few, not all |
Summary table | Top 100 shows by plays this week, rebuilt every hour | A little staleness is fine |
Read model | Each listener's Up Next, kept up to date from events | Reads need a shape unlike the writes (Event sourcing & CQRS) |
Every row of that table trades the same way: reads get cheaper, writes get more work, and each copy can now disagree with its source. So denormalize where measurements show a read is hot, not everywhere in advance.
4 · Embedding
Embed what belongs together, reference what grows without limit
Document databases make one denormalization decision constantly: put the related data inside the parent document (embed), or store it separately and point to it (reference).
Embed when the child belongs to the parent, is read and updated with it, and stays small: the show's host details, its categories, its 3 latest episodes. Reference when the child is numerous, grows without bound, changes on its own schedule, or makes sense on its own: all episodes, every subscriber, every comment.
Unbounded growth is the classic trap. Embedding comments in each episode works for months, until a hit episode collects tens of thousands of them. MongoDB, for example, caps a document at 16 MiB:
- Episode with embedded comments, 500 bytes each
- Document size limit, 16 MiB
Size ≈ comments × 500 bytes. The limit, 16,777,216 bytes, is reached at about 33,500 comments; long before that, every read of the episode drags the whole comment list along.
The usual fix keeps the best of both: embed a subset, such as the 20 newest comments for the episode page, and store every comment in its own collection, fetched page by page when someone scrolls.
5 · The catch
Every copy needs a plan for staying correct
A copy without a plan is a bug waiting for a busy day. For each copied or computed value, decide how it is kept in step. Subscribing to a show touches two copies with two different plans:
The count changes in the same transaction as the subscription, so the two never disagree. Up Next is updated afterward from an event, so it lags for a few seconds. Publishing that event reliably after the commit is the job of an outbox (Distributed transactions: 2PC & sagas).
The plans available, from strictest to loosest:
- The same transaction: the copy can never disagree, but both writes must be in one database, and a very popular counter becomes a point of contention (Hot partitions & skew).
- An event or trigger afterward: the copy catches up within seconds; failures need retries (Event-driven architecture).
- A periodic rebuild, like the hourly top-100 table: simple, and stale by up to the period.
- Tolerated staleness: some copies never need updating at all.
That last case is more common than it sounds. Not every copy is a cache of the original:
- Immutable facts, like an episode's publication date, can be copied freely; they never change.
- Facts at a point in time must not be updated. If listeners buy premium episodes, the price copied into each order is the price paid, not a stale copy of today's price.
- Facts that must be current, like whether an episode is still available, are worth a read of the source.
6 · Key-value stores
In key-value stores, the access patterns are the model
Wide-column and key-value stores have no joins, so the model is built from the access patterns outward. Each hot read becomes one partition, sorted the way the read needs it. In a DynamoDB-style table, the show page can be a single partition holding the show and its episodes:
Partition key | Sort key | Item |
|---|---|---|
SHOW#88 | META | Title, host, artwork, subscriber_count |
SHOW#88 | EP#2026-10-01 | Latest episode |
SHOW#88 | EP#2026-09-24 | Previous episode |
LISTENER#5 | SUB#SHOW#88 | Listener 5 subscribes to show 88 |
One query on SHOW#88, sorted newest first and limited to 11 items, returns the show and its 10 latest episodes. The subscription rows form an adjacency list: each listener's partition holds one item per show they follow, which answers "which shows does listener 5 follow?". The reverse question, "who follows show 88?", needs a secondary index keyed on the sort key, which flips the relationship. The price is that a question nobody planned for has no partition to read, and usually means another index or table (Relational vs NoSQL).
7 · In practice
Normalize by default, denormalize by measurement
A working order for a new model:
- List entities and relationships
Note one-to-many versus many-to-many, and which sides grow without limit.
- List the access patterns
The hot reads and writes, with rough rates: show page 2,000 a minute, subscribes 50 a minute.
- Start normalized
In a relational database, store each fact once; in a key-value store, design one partition per hot read.
- Denormalize the hot reads
Copy, count or embed only where a measured read is too slow or too costly.
- Write down each copy's plan
Same transaction, event, rebuild or tolerated staleness, and how to repair a copy that drifts.
A checklist:
- Bound every embedded list; reference anything that can grow without limit.
- Keep counts and totals for numbers shown on hot pages instead of counting on read.
- Copy point-in-time facts such as prices into the records that need them, and never update them.
- Make copies rebuildable from the source, so a bug can be fixed by replaying, not by hand.
- Watch write amplification: a field copied into a million rows turns one edit into a million writes.
Check yourself
5 questions
Takeaways
Remember this
- A data model is entities, relationships and access patterns; the access patterns decide what must be cheap.
- Normalization stores each fact once and prevents update, insertion and deletion anomalies.
- Denormalization copies, counts or embeds data where a hot read needs it, and makes writes do the extra work.
- Embed small, bounded data that belongs to its parent; reference anything that grows without limit.
- Every copy needs a plan: same transaction, event, rebuild, or deliberate staleness, and point-in-time facts are never updated.