Data Modeling for System Design Interviews: Entities to Partition Keys

Data modeling in a system design interview means deciding what you store, how it's shaped, and how it's keyed so the system's main reads and writes are fast and correct at the stated scale. It sits between the API and the architecture: once you know the operations, the data model decides which database fits, what the primary and partition keys are, which indexes you need, and what you denormalise. Interviewers use it to test whether a design will actually work, because most scaling problems are data model problems in disguise.

Where it fits in the interview

A typical flow is requirements, then core entities and APIs, then the data model, then the high-level design, then deep dives. You don't need every column. You need the handful of entities that matter, their keys, their relationships, and the access patterns each table serves. Two to five tables, drawn with keys, is usually right.

A method in six steps

1. List the core entities from the requirements: users, posts, follows, likes for a social feed; links and clicks for a URL shortener.
2. Mark the relationships and their cardinality: one-to-few, one-to-many, many-to-many. Cardinality decides whether data is embedded, referenced or joined.
3. Write down the access patterns: the queries the system runs, with rough rates. "Get the 50 newest posts from people I follow" and "redirect a short code" are access patterns; "store users" is not.
4. Choose the store per entity from the access patterns and consistency needs, not from habit.
5. Choose keys: the primary key that identifies a row, and for distributed stores the partition key that decides which machine holds it and the sort key that orders rows within a partition.
6. Add indexes and denormalisation only where an access pattern needs them, and state what each costs on writes.

Access patterns first. In a relational database you can usually add an index later for a new query; in a key-value or wide-column store, a query the key doesn't support often means a new table and a backfill. Interviewers want to see you design the keys around the queries.

Conceptual, logical and physical models

Relationships and how each model handles them

Two practical rules follow. Documents work well for self-contained, tree-shaped data that is read as a whole and changes as a unit, such as a product page or a user profile with a few addresses. They work badly for unbounded children (a celebrity's comments) or shared entities referenced from many places (a company name copied into a million profiles must be updated a million times).

Choosing a store from the access patterns

Most real systems use several, each serving the access patterns it is good at, with one of them as the source of truth and the others kept in sync by events or change data capture. See SQL vs NoSQL and NoSQL database types.

Keys: primary, partition and sort

• Primary key uniquely identifies a record. Prefer stable, meaningless ids (a generated id) over natural values like email addresses, which change.
• Partition key (or shard key) decides which node stores the record. It must spread load evenly and match the main query, so that query hits one partition. See sharding.
• Sort key (or clustering key) orders records inside a partition, which makes range queries such as "newest 50 messages in this conversation" a single sequential read.
• Id generation at scale needs a strategy: database sequences don't work across shards, so use unique ID generators such as Snowflake-style ids, which are also roughly time-ordered.

Hot partitions are the classic trap: partitioning messages by conversation works until one group chat has a million members, and partitioning by date sends all of today's writes to one node. Say how you'd spread a hot key (add a bucket suffix, split by time window). See hot partitions.

Indexes and their cost

Every secondary index speeds up one read pattern and slows every write, because each insert or update must also update the index. Name the index each query needs, prefer composite indexes that match the filter and sort order ((user_id, created_at desc) for a user's latest posts), and use covering indexes for very hot reads. In distributed stores, a secondary index is either local to each partition (cheap writes, scatter-gather reads) or global (fast reads, slower and eventually consistent writes). See database indexing.

Normalise or denormalise

Normalised data stores each fact once, so updates are simple and consistent, at the cost of joins on read. Denormalised data copies fields to where they're read, so reads are one lookup, at the cost of more writes and possible inconsistency. In interviews, start normalised for the source of truth and denormalise deliberately for the hot read paths, saying how the copies are kept up to date: synchronously in the same transaction, or asynchronously through events. Precomputed counts (likes on a post) and fan-out feeds are the most common examples. See normalization vs denormalization.

Schema flexibility and evolution

Relational databases enforce the schema on write: changing it is an explicit migration, and every row follows the new shape. Document stores read whatever was written, so the schema lives in application code, which must handle old and new shapes until old records are migrated, either lazily on read or by a background job. Flexibility is useful for genuinely varied records, but most data has a stable shape, and a JSON column in a relational database often gives enough flexibility without giving up joins and constraints. Whatever the store, plan changes as backward compatible steps: add, backfill, switch reads, then remove.

Worked examples

URL shortener

links      (short_code PK, long_url, owner_id, created_at, expires_at)
           -- key-value or wide-column store, partitioned by short_code
           -- access: get long_url by short_code (very hot, cached); create link
clicks     (short_code, clicked_at, country, referrer)
           -- append-only; partition by short_code + day bucket, sort by clicked_at
           -- access: click counts per link over time (aggregated asynchronously)

The redirect is a single-key lookup at enormous read rates, so a key-value store plus a cache fits. Clicks are a high-volume append stream aggregated offline, not counted on the redirect path. See design a URL shortener.

Chat messages

messages      (conversation_id, message_id, sender_id, body, sent_at)
              -- wide-column: partition key conversation_id, sort key message_id (time-ordered)
              -- access: newest N messages in a conversation; page backwards
conversations (conversation_id PK, type, title, created_at)          -- relational
members       (conversation_id, user_id, role, last_read_message_id) -- relational, indexed both ways
inbox         (user_id, last_activity_at, conversation_id)
              -- per-user list of conversations sorted by recent activity (denormalised)

Messages are written constantly and read as a recent range per conversation, which is exactly what a partition key plus a time-ordered sort key serves. The inbox is a deliberate denormalised copy for the "my chats" screen, updated on every message.

Social follow graph and feed

follows_by_follower  (follower_id, followee_id, created_at)   -- who do I follow?
follows_by_followee  (followee_id, follower_id, created_at)   -- who follows me? (same data, other direction)
posts                (author_id, post_id, body, created_at)    -- partition by author, sort by time
feed                 (user_id, post_id, author_id, created_at) -- precomputed timeline per user (fan-out on write)
post_counters        (post_id PK, likes, comments)             -- denormalised counts, updated asynchronously

The many-to-many follow relationship is stored twice, once per direction, because both questions are hot and a single table can only be partitioned one way. Feeds are precomputed for most users and assembled at read time for accounts with millions of followers. See fan-out and design Twitter.

Operational data model vs analytical data model

Everything above is about operational data that serves the product: small reads and writes, shaped by access patterns. Analytics needs a different shape: a few huge scans and aggregations over history, which is what warehouses and dimensional modeling are for. Don't try to serve both from one schema; stream changes from the operational store into the analytical one. See OLTP vs OLAP.

Common mistakes

• Listing tables and columns without saying which queries they serve.
• Choosing a NoSQL store and then needing joins or ad-hoc queries it can't do.
• Partition keys that create hot spots: by date, by a celebrity's id, or by a low-cardinality status.
• Embedding unbounded lists in a document.
• Adding indexes for every field without mentioning the write cost.
• Denormalising without saying how the copies stay in sync.
• Using mutable natural values (email, username) as primary keys.