Data Modeling — Cracking the System Design Interview

Ask a junior candidate to design a system and they start drawing tables. Ask a senior candidate and they start by asking what questions the system has to answer. The schema falls out of the queries, never the reverse. Data modeling is where a lot of designs quietly succeed or fail: get the access patterns and the partition key right and everything downstream is easy; get them wrong and no amount of caching or sharding saves you.

This lesson gives you a repeatable way to model data under interview pressure.

Start from the access patterns

Before a single table, list the access patterns — the reads and writes the system must support, with their frequency and latency needs. For a social feed that might be:

Write a post (moderate volume).
Read a user’s home feed, newest first (very high volume, must be fast).
Read one user’s own posts (moderate).
Count likes on a post (very high volume).

Entities, relationships, and designing from the query the system must answer — the access patterns drive the schema, keys, and indexes

Now the schema has a job: make the hot queries cheap. The home-feed read is the one that happens millions of times a second, so the model bends to serve it — even if that makes a rare query (say, “all posts mentioning a hashtag”) more expensive. This is the whole game: you cannot make every query fast, so you make the frequent ones fast and accept the cost elsewhere.

Interview tip: Say the access patterns out loud and write them down before you draw anything. “The dominant read is the home feed by user_id ordered by time, so I’ll optimize for that” is the sentence that frames every later decision.

Normalize or denormalize?

Once you know the entities, you choose how much to duplicate.

Normalized (relational)

Each fact lives in exactly one place; you reassemble data with joins at read time.

Pros: no duplication, so updates touch one row and the data cannot drift out of sync; flexible — you can query many ways.
Cons: joins get expensive at scale, and they do not distribute well across shards.

Normalize when the data is naturally relational, you query it many ways, and correctness matters more than raw read latency — orders, payments, inventory.

Denormalized (document / wide-column)

You duplicate data so a single read returns everything, with no joins.

Pros: one read serves the query; it scales horizontally because the row is self-contained.
Cons: duplicated data must be kept in sync on every write, and a field that changes in many places is painful to update.

Denormalize when the read pattern is known and hot, and you need scale — a feed, a product page, a chat message list. A classic move is to store a post together with the author’s name and avatar so rendering the feed needs no join back to the users table; the cost is updating that copy if the user renames.

Normalized:    users ── orders ── order_items        (join at read)
Denormalized:  { order_id, user{name}, items[...] }   (one read, duplicated)

Real systems mix both: a normalized source of truth, plus denormalized read models (see the Scaling Reads lesson’s CQRS note) that are rebuilt from it.

The partition key is the decision that matters

In any distributed store — Cassandra, DynamoDB, a sharded SQL database — the partition key decides which node holds a row. It is the most consequential choice in the whole model, because it controls two things at once:

Load distribution. The key must spread writes and reads evenly across nodes. A key like today's_date sends 100% of today’s traffic to one partition — an instant hotspot, no matter how many nodes you add.
Query locality. Everything you read together should share a partition key so one lookup serves the query. In a chat system, partitioning messages by conversation_id keeps a conversation’s history on one partition and makes “load this chat” a single read.

Good partition keys are high-cardinality and match the dominant access pattern: user_id, conversation_id, device_id. When one key would be too hot (a celebrity, a popular product), you shard the key further — for example user_id#bucket — to split its load.

Interview tip: When you pick a NoSQL store, immediately name the partition key and justify it: “Partition by conversation_id — it spreads load and keeps each chat’s messages together.” Skipping this is the most common way distributed designs fall apart under follow-up questions.

Modeling relationships

One-to-many (a user has many posts):

SQL — a posts table with a user_id foreign key; fetch with a join or an indexed lookup.
Document store — either a posts collection keyed by user_id, or, if the “many” is small and bounded, embed it directly in the parent document. Never embed an unbounded list (a user’s posts grow forever); embed only bounded things (a user’s addresses).

Many-to-many (users follow users; students take courses):

SQL — a join table (follows(follower_id, followee_id)) with an index on each side so you can query both directions.
NoSQL — you typically keep two denormalized copies, one per access direction (a following list and a followers list), because there are no joins to resolve it at read time. You pay with extra writes to keep both sides consistent.

A worked sketch: an e-commerce order

The access patterns: show a customer their orders; show one order with its line items; update order status.

-- Relational source of truth
customers(id PK, name, email)
orders(id PK, customer_id FK, status, total, created_at)   -- index (customer_id, created_at)
order_items(id PK, order_id FK, product_id, qty, price)    -- index (order_id)

That normalized model is correct and easy to keep consistent — the right call for the transactional core. For the read-heavy order-history screen you might additionally keep a denormalized order_summary document ({order_id, customer, items[], total, status}) so rendering it is one read. The source of truth stays normalized; the summary is a derived read model you rebuild on write.

Common mistakes

Modeling before knowing the queries. A beautiful normalized schema that can’t serve the hot query cheaply is the wrong schema. Patterns first.
Ignoring the partition key in a distributed store, then being surprised by a hotspot.
Embedding unbounded lists in a document (comments, posts) so a single record grows without limit.
Over-normalizing at scale — insisting on joins across shards where a denormalized read model would serve the hot path far more cheaply.
Denormalizing without a plan to keep copies in sync (events, CDC, or a rebuild job).

Key Takeaways

Design from the access patterns. List the reads and writes with their frequency first; the schema’s job is to make the hot queries cheap.
Normalize for correctness and flexible querying; denormalize for read speed and scale. Real systems keep a normalized source of truth plus denormalized read models.
The partition key is the make-or-break choice in any distributed store — it must spread load evenly and keep together what you read together.
Model relationships to the store: foreign keys and join tables in SQL; embedded or duplicated references in document/wide-column stores, trading extra writes for join-free reads.
The junior tell is drawing tables before stating the queries. Say the access patterns out loud, then let them drive the model.