System design · choosing a data store
Pick a store from how the data is read and written and the invariant you must protect, never from familiarity or hype. The four questions to ask first, the six families side by side, a card for every real database, ERD design and normal forms, and the polyglot sync tax. Companion to S3 · Relational vs Document vs Key-Value.
The data model follows the access pattern. Nobody picks a store in a vacuum. Name how it is read, how it is written, what must stay true, and how big it gets, and the shortlist writes itself. Defend the choice by the invariant it protects (“I need this store because it enforces X”), not by “it’s web-scale.”
Ask these in order, out loud, before any store name. By the fourth, the answer is usually obvious, and you can defend it instead of asserting it.
How is it primarily read: by exact key, by range, by join, by full-text, by aggregate, by relationship. This one decides the family.
One row, a whole document/aggregate, or an append-only event. And what write rate.
What must stay true: uniqueness, referential integrity, atomic multi-row transactions, or “none needed here.”
Rows, write rate, and the p99 you must hold. Scale only rules options out after the first three.
Jumping straight to question four. “We need scale” picks nothing on its own. Reach for Cassandra at ten thousand rows and you have answered the wrong question.
Answer “how is it primarily read?” and the store falls out. Then ask the second question juniors skip: does more than one access pattern fight? If not, stop at one store.
| Primary read shape | Reach for | Real product | Note |
|---|---|---|---|
| By exact key | Key-value | Redis, DynamoDB | + wide-column if writes are huge |
| As one whole aggregate | Document | MongoDB, DynamoDB | embed what you read together |
| Ad-hoc, joined, with invariants | Relational | PostgreSQL, MySQL | the default; earn the exit |
| By relationships / traversal | Graph | Neo4j, Kuzu | when edges are the question |
| Full-text | Search | Elasticsearch | derived store, never the truth |
| Heavy aggregates / analytics | OLAP / columnar | ClickHouse, BigQuery | derived store, fed in batches |
If a single store serves every pattern, stop there. Only when patterns genuinely fight do you go polyglot: one source of truth plus rebuildable derived stores. Every extra store is a sync problem you now own (§7).
The same four questions, six answers. No row is “best.” Each column is a question you must already have answered.
| Family | Reads well by | Joins | Schema | Enforces invariants | Scales by |
|---|---|---|---|---|---|
| Relational | anything (ad-hoc) | native | rigid, migrated | yes, for you | vertical, then hard |
| Document | aggregate / key | app-side | flexible | in-doc only | horizontal |
| Key-value | exact key | none | opaque value | no | flat / linear |
| Wide-column | partition + range | none | per-table | no | linear writes |
| Search / OLAP | text / aggregates | limited | indexed / columnar | no (derived) | horizontal |
| Graph | relationships | traversal | flexible | edges only | vertical-ish |
Read the columns, not the rows. Only relational gives a confident yes on enforcing invariants; key-value and wide-column are the flat-scaling exits; graph turns a pile of self-joins into one traversal.
The real products you actually reach for. Each card: model what it is, best for when to pick it, avoid when not, gotcha the thing that bites.
Five steps, the same every time. Entities are nouns, relationships are verbs, and every many-to-many needs a join table.
The real things you store: customer, order, product. Each becomes a box.
One column (or set) that uniquely identifies a row. Prefer a stable surrogate id over something that can change, like an email.
“A customer places many orders.” Mark each line 1:1, 1:N, or M:N.
Many-to-many can’t be one line. Insert an entity (order_item) that holds the two foreign keys.
No repeating groups; every column depends on the key. Kills the update anomaly of a value stored five times that drifts apart.
A normalized model (mermaid-style)
erDiagram
CUSTOMER ||--o{ ORDER : places
ORDER ||--|{ ORDER_ITEM : contains
PRODUCT ||--o{ ORDER_ITEM : "appears in"
CUSTOMER {
int id PK
string email UK
}
ORDER {
int id PK
int customer_id FK
datetime placed_at
}
ORDER_ITEM {
int order_id FK
int product_id FK
int qty
}
PRODUCT {
int id PK
string name
int price_cents
}
The M:N between products and orders is resolved by order_item. PK primary key · FK foreign key · UK unique.
Crow’s-foot notation
| Symbol | Reads as |
|---|---|
| || | exactly one |
| o| | zero or one |
| o{ | zero or many |
| |{ | one or many |
| ||--|| | one-to-one (1:1) |
| ||--o{ | one-to-many (1:N) |
| }o--o{ | many-to-many (M:N) → join table |
Normalize to 3NF by default; it removes the anomalies that come from storing a fact twice. Denormalize later only with a named read reason.
| Form | Rule, in one line | Fixes |
|---|---|---|
| 1NF | Atomic values; no repeating groups or arrays in a column. | a “tags” column holding “a,b,c” |
| 2NF | 1NF + every non-key column depends on the whole composite key. | partial dependency on half a key |
| 3NF | 2NF + non-key columns depend on the key only, not each other. | transitive dependency (zip → city) |
| Denormalize | Duplicate on purpose to serve a specific hot read. | name the read, own the drift |
The mnemonic: every non-key column depends on “the key, the whole key, and nothing but the key.”
Polyglot persistence is not “use five databases.” It is: one store owns the truth; the rest are read-optimized copies you can rebuild from it.
Source of truth
Writes go here. Enforces invariants. The one you back up. Usually relational.
Derived stores
Rebuildable from the source: cache, search index, OLAP copy, graph projection. Disposable.
The sync tax
A pipeline, a staleness window you name, and a way to rebuild. You own this the moment you add a store.
A real polyglot system with no server at all: Markdown + YAML files are the document source of truth; an append-only graph.jsonl is the event log; a Kuzu graph DB is derived from both (dropped and rebuilt, never authoritative). The house rule is literally an access-pattern decision: “use the graph first, grep second.” Relationship questions (“all docs about X”) go to the graph; raw phrase lookups go to grep. Same bytes, two stores, split by how they’re read.
Four ways engineers pick the wrong store, and the four-sentence move that avoids all of them.
The four traps
The repeatable move
1. name the access pattern
2. name the invariant it must protect
3. pick the store that follows
4. own the sync cost if derived
“Reads are by exact key, writes are huge, no cross-row invariant. So: wide-column. The ledger stays in Postgres; this store is derived from it.” That is a staff answer. “I’d use Cassandra” is a reflex.
Access pattern first, store second, invariant always. If you can fill one row of the families table for your feature, and name the invariant, you have made the decision and can defend it.
Companion to S3 · Relational vs Document vs Key-Value. Further reading: Fowler: Polyglot Persistence · System Design Primer · MongoDB data modeling · DynamoDB single-table design.