Data Modeling for System Design Interviews
Design something clear, functional, and aligned with your requirements, then move on. The bar is lower than a dedicated data-modeling interview, so the goal is a schema that won't crumble, not a perfect one.
01What data modeling is, and when it appears
Data modeling is the process of defining how your application's data is structured, stored, and related. In practice you decide what entities exist, how they're identified, and how they connect to one another.
In a system design interview you are not expected to normalize everything or produce a full schema diagram. You're expected to design something clear, functional, and matched to your requirements — a "good enough" model that keeps the conversation focused where it belongs.
Data modeling surfaces at two points in the Hello Interview delivery framework. First, during requirements gathering, you name your core entities — these usually map 1:1 to tables and form the backbone of your schema. Later, in high-level design, you sketch the schema next to your database: key fields, relationships, and a note on how you'd index or partition to support the main queries.
The framework has six phases, but data modeling shows up in just two of them: core entities (the nouns/tables of your system) and high-level design (where, as you resolve each API endpoint, you naturally end up in the database specifying fields, relationships, and indexes). Model your data in context, to justify how each functional requirement is served, not as a separate up-front exercise.
Core entities: auction, item, bid, user. During high-level design a requirement like "users can create an auction" flows into the database: here's what an auction is, here's the foreign key to item, here's how bid relates to user, and so on.
If you're a data engineer prepping for a dedicated data-modeling interview, this is the wrong material — it covers only the limited modeling a software engineer needs to produce a minimal, defensible model inside a system design interview.
02Database model options
Before you can design a schema, pick the database type — it shapes how you structure everything that follows.
The default answer is a relational database, and specifically PostgreSQL, unless your requirements clearly signal something else. Knowing the alternatives still matters — it shows you reason about trade-offs rather than parroting a default.
Reaching for an exotic database to look sophisticated. Resist it. Almost every interview problem can be justified on a relational database, and it's very hard to go wrong with Postgres. Only deviate if you have real hands-on experience and strong opinions about another engine.
Relational databases (SQL)the default
Relational databases organize data into tables with fixed schemas: rows are entities, columns are attributes. Relationships are enforced through foreign keys, and transactions get ACID guarantees. Most problems map naturally onto this model — users, posts, comments, likes all have clear relationships.
- Strength: complex queries via joins ("all posts by users a given user follows, ordered by recency").
- Strength: strong consistency when it's a hard requirement — payments not double-charging, inventory not overselling.
- Scalability concerns are usually exaggerated: read replicas, sharding, connection pooling, and caching take SQL a long way. Facebook and Airbnb run on relational foundations.
Multi-table joins can become performance traps at scale. Mentioning complex reporting-style queries raises yellow flags — be ready to reach for denormalized views, caching, or pre-computed results.
Technologies: PostgreSQL, MySQL, SQLite.
Document databases
Store data as JSON-like documents with flexible schemas. Modeling becomes about nesting and embedding related data inside a document rather than normalizing across tables — which eliminates joins but means updating one post can require rewriting an entire user document.
Two clarifications the diagrams can hide: (1) document databases don't force you to embed — you can still keep separate collections for users, posts, and likes. (2) Many document stores do support some joins today; embedding is a historical habit from when their join support was weaker than SQL's.
The main reason to choose a document database is schema flexibility — you don't know what fields you'll need next. But an interview explicitly scopes the requirements to a narrow, fixed set for the next 35–45 minutes, so by definition there's no evolving schema unless the interviewer says so. That neutralizes the primary reason to pick it. Default back to relational.
Technologies: MongoDB, Firestore, CouchDB.
Key-value stores
Simple lookups: fetch a value by exact key match. Extremely fast, but almost no query ability beyond that.
Think of it as a giant hashmap — you can only look things up by their key. You can't ask "find all posts from the last week" or "posts by users I follow." So you denormalize heavily and duplicate data across keys: e.g. keep a recent_posts key you append to on every post, so the same post lives both under post:123 and inside the recent-posts list.
"Over SQL" is misleading — in practice you use both together: SQL as the source of truth, with a key-value cache (Redis) in front for hot data. Great for reads, but terrible for consistency when data changes.
DynamoDB is fundamentally a key-value database but layers document-like features on top — a key-value/document hybrid sitting between the two models.
Technologies: Redis, DynamoDB, Memcached.
Wide-column databases
Data lives in column families. Instead of fixed columns per row, each row can have completely different columns. New posts are added as new rows/columns keyed by (user_id, timestamp); rows with the same partition key are stored together.
The design is optimized for massive write volume. Writes are just appends of new columns — no seeking, no finding a page to modify — which makes them incredibly fast. That suits time-series data, event logging, IoT sensors, and analytics workloads where old data is rarely updated.
Reach for this only when there's genuinely enormous write volume. Even then, alternatives like putting a queue in front or batching writes often solve it without a specialized store.
Technologies: Cassandra, HBase.
Graph databaseslast and least
Store data as nodes and edges, optimized for traversing relationships. Users are nodes; "posted" or "liked" are edges to post nodes.
Choosing a graph database in an interview — a mistake seen mostly from junior candidates who hear "social network" or "recommendations" and reach for it. They sound sophisticated but add operational complexity for little gain.
Even Facebook models its social graph on MySQL. If plain relational is good enough for the largest social network in the world, it's good enough for your interview. Avoid graph databases altogether and stick with relational.
Technologies: Neo4j, Amazon Neptune.
| Model | Best at | Modeling impact | Use in interview? |
|---|---|---|---|
| Relational | Clear relationships, joins, strong consistency | Normalize into tables with keys & constraints | Default — yes |
| Document | Evolving / flexible schemas | Embed & nest; denormalize more | Rarely |
| Key-value | Caching, sessions, single-key lookups | Very flat; duplicate across keys | As a cache, not primary |
| Wide-column | Huge write volume, time-series | Design around queries; time is first-class | Only for write-heavy |
| Graph | Relationship traversal | Nodes & edges | Almost never |
03Schema design starts with three factors
Every schema technique that follows is just a tool to satisfy three factors you already defined during requirements and API design.
1. Data volume — determines where data can physically live (millions of users may force data across multiple stores). 2. Access patterns — the most important factor; how the data is queried, read straight off your API endpoints. 3. Consistency requirements — how tightly coupled data must be (payments need strong consistency; a like showing up a few seconds late is fine).
Tie every schema choice back to these factors out loud. For example: "Since we need to load feeds quickly and likes can be eventually consistent, I'll denormalize like counts into the posts table." That reads as reasoning, not memorization.
Access patterns come straight from your APIs. For each endpoint ask: "what query do I need to support this?" and "how do I make it fast enough for a good experience?"
All the techniques below — entities, keys, normalization, indexes, sharding — exist only to address these three factors.
04Entities, keys & relationships
Map your core entities into tables with clear identifiers and relationships.
Every record needs a primary key — a unique identifier. Use system-generated IDs (user_id, post_id), not business data like email. System keys stay stable when business rules change.
A primary key is like a social security number: every row gets exactly one, no two the same — random or monotonically increasing, all that matters is uniqueness. A foreign key is a field in one table that points to the primary key of another, which is how relationships are created and how referential integrity is enforced (the database won't let you create a post referencing a user that doesn't exist).
users: id (PK), username, email posts: id (PK), user_id (FK → users.id), content, created_at comments: id (PK), post_id (FK → posts.id), user_id (FK → users.id), content likes: user_id (FK → users.id), post_id (FK → posts.id)
Candidates get tripped up reciting whether a relationship is one-to-many or many-to-many. Skip the terminology anxiety: define your foreign keys clearly and the relationships become obvious. "Posts has a user_id FK to users" already tells you one user has many posts; "likes has both user_id and post_id FKs" already tells you it's many-to-many.
For reference, the relationships the terminology describes:
- One-to-many (1:N): a user has many posts; a post has many comments.
- Many-to-many (N:M): users like many posts; posts are liked by many users.
- One-to-one (1:1): rare — often a sign two tables should be merged.
Foreign keys cost something: the database validates every insert/update. At very large scale some companies drop them for write performance and enforce integrity in application logic. Naming this trade-off shows you understand it.
Finally, layer in constraints — NOT NULL, UNIQUE, CHECK — to enforce correctness at the database level (emails must be unique, prices must be positive). They protect data quality but add write overhead.
You don't always have to write the foreign key explicitly — if a user_id appears in two tables it's usually implied. Evan often skips it. But in a more junior interview, be explicit and write the keys and constraints directly next to the columns as you list them.
05Indexing for access patterns
Indexes are data structures that help the database find records without scanning every row — like a book's index letting you jump straight to "normalization" on page 149 instead of reading every page.
A B-tree index lets you walk down a tree to find a record in O(log n) instead of scanning in O(n). You find the page holding the record and pull it in. TL;DR: an index makes reads faster, but you must be explicit about which columns you index.
Indexing is usually the first thing you reach for to make access faster — before denormalizing. Your indexes should directly support your most important queries.
- Index on
posts.user_id→ find all posts by a user. - Index on
posts.created_at→ load recent posts chronologically. - Composite index on
(user_id, created_at)→ a user's recent posts, efficiently.
Say it explicitly: "The GET /users/{id}/posts endpoint needs an index on posts.user_id." Work backwards from each API — what query does it run — and make sure an index supports it. Without it you'd read every row filtering by user id.
06Normalization vs denormalization
Normalization = storing each piece of information in exactly one place (user data lives only in the users table). Denormalization = deliberately duplicating data across tables, almost always for read performance.
Put username and email onto every post row, and if a user changes their username you must update every post they've ever made. Miss one and the data is inconsistent.
Start with a clean, normalized model — always — and denormalize only when you have a specific performance need that indexing can't meet. Repeating data creates consistency problems that are far harder to fix than the performance problems you're trying to avoid.
Reasonable exceptions where denormalization earns its keep:
- Analytics / reporting — aggregating data that changes infrequently.
- Event logs & audit trails — capturing a snapshot at a point in time.
- Heavily read-optimized systems — search engines, where speed beats strict consistency.
When you do need fast denormalized access, put it in a cache, not the source of truth. Your database stays clean and normalized; the cache holds pre-computed joins or aggregations, accepting some eventual consistency to serve feeds quickly.
When something is slow, reach for an index first, then a cache, and only then denormalize into the primary schema. Denormalizing the source of truth is the last resort, not the first move.
08The interview process, step by step
Outline core entities early. Then, when you introduce the database during high-level design, work through this sequence.
- Choose the database type. Default to a relational database (Postgres) unless you have strong reasons otherwise.
- List the columns each entity needs to satisfy the functional requirements — only those, don't go overboard.
- Add primary & foreign keys to define constraints and relationships.
- Determine indexes, derived directly from your API endpoints (an endpoint loading a user's posts → index
posts.user_id). - Consider denormalization — start fully normalized; denormalize only for a rare, real performance need, usually via a cache.
- Consider sharding — only if data exceeds one database. If yes, pick a partition key that avoids cross-shard joins.
Steps 3–6 should all happen in the context of the three factors — access patterns, data volume, consistency requirements — you defined earlier in the interview.
Data modeling is a core part of the interview but not the focus. Show you can produce a reasonable schema that supports the requirements, then move on to the more interesting deep dives.
09Quick revision
Data modeling: defining how data is structured, stored, and related — what entities exist, how they're identified, how they connect.
Aim for a clear, "good enough" schema matched to requirements. It's not the focus of the interview.
Default to relational / Postgres. Only deviate with real experience or an explicit signal.
Three factors drive everything: data volume, access patterns (most important), consistency.
PK = unique per-row identifier (system-generated). FK = points to another table's PK, enforcing referential integrity.
Don't memorize 1:N vs N:M. Define foreign keys clearly and the relationships become obvious.
Speed order: index → cache → denormalize. Start normalized; denormalize last, ideally in a cache.
Shard by your primary access pattern; keep related data (posts + their comments) on the same shard.
Avoid graph databases (even Facebook uses MySQL) and time-range sharding (hot shard).
Connect every index to an API endpoint out loud; tie every schema choice back to the three factors.
Document DB's main draw is schema flexibility — which a narrowly-scoped interview neutralizes.
Type → columns → keys → indexes → denormalize? → shard?