bigonotes
System DesignData Modeling

Data Modeling in an Interview

A step-by-step checklist for sketching a schema during a system design interview, plus sensible database defaults.

3 min readLast updated Data Modeling
On this page6 sections

In a system design interview you don't need a complete, textbook data model. You need a schema that's just detailed enough to show how your design serves its main queries. This note pulls the data modeling lessons together into a checklist you can follow on the whiteboard.

When data modeling happens#

Data modeling shows up at two points in the interview.

  1. Core entities step: list the main entities early, as a short list of nouns such as User, Post and Follow.
  2. High-level design step: when you draw a database, sketch its schema next to it: key fields, relationships, and how you'd index or partition it for the main queries.

The checklist#

When you add a database to your design:

  1. Pick the type of database.
  2. List the columns each entity needs to meet the functional requirements. Skip obvious ones.
  3. Mark primary and foreign keys for each relationship.
  4. Decide which columns need indexes, tied to specific queries.
  5. Decide whether any read path needs denormalization.
  6. Decide whether sharding is needed. If so, pick a shard key that matches the main access pattern.

The result can be as light as this:

A Postgres schema sketch: users, posts and comments tables with primary keys, foreign keys and indexed columns marked, and lines showing that posts and comments reference users

Sensible defaults for the database type#

Each need has a sensible default.

Need Default Notes
Core entities, money, relationships Relational (PostgreSQL) ACID, joins, constraints. The reliable source of truth.
Faster reads of hot data Key-value (Redis) in front of the source of truth Cache-aside: check Redis, on a miss read PostgreSQL and fill the cache. Also good for sessions and rate limits.
Schema that changes often or varies per record Document (MongoDB) Mention it when prompted, or when records are truly polymorphic.
Very high write volume: logs, events, time series Wide-column (Cassandra) Move just those tables; keep core data relational.
Deep relationship traversal Graph Rarely worth it. Usually relational plus a cache of adjacency lists is enough.

A typical progression:

  1. Start with PostgreSQL for relationships and consistency.
  2. If read latency is too high, add Redis in front.
  3. If one table's writes exceed a single leader's capacity, move it to Cassandra.
  4. If the schema is unpredictable, mention a document store as an alternative.

Details for each model are in Database Model Options.

How to phrase it#

Short, reasoned sentences work best when you name each store.

  • The default: "PostgreSQL is the default here. It gives us ACID transactions and integrity, and for this system a relational model's reliability matters more than raw horizontal scale."
  • The cache: "Redis caches hot data. On a miss we read from PostgreSQL, the source of truth, and fill the cache for later requests."
  • High writes: "Event logs go to a wide-column store like Cassandra, which scales writes horizontally beyond what one relational leader can handle."

Key takeaways#

  • List entities early; sketch the schema when the database appears in your high-level design.
  • Follow the checklist: type, columns, keys, indexes, denormalization, sharding.
  • Default to PostgreSQL. Add Redis for read speed and Cassandra for extreme write volume.
  • Only document the columns that matter to your design.

Found a mistake? Tell me on LinkedIn ↗

Further reading#