The Data Model Is the Step Everyone Skips (and It Picks Your Database for You)

Jordan Beland12 min read
interview-prep
system-design

There's a moment I got to know well in the system design interviews I've conducted. The candidate has drawn a respectable architecture (clients, a load balancer, a couple of services, a queue, a cache, arrows all pointing in sensible directions), and down at the bottom sits a cylinder labeled "DB." Everything above the cylinder has been discussed with care. So at some point I'd ask a version of "what's actually in there?", and an interview that had been cruising would wobble, sometimes badly, because at no point had anyone decided what the data looks like. I don't mean which database engine, I mean the data itself: what the entities are, which fields they carry, what the key is, which query needs which field. In my experience this gap shows up at every level, including candidates who were otherwise clearly senior, and it's a bit of a shame, because the fix takes about two minutes of the interview and saves you far more than that later. It also defuses the question people seem to dread most, the SQL vs NoSQL one, which mostly answers itself once a data model exists on the board.

Why the step gets skipped

I have a few theories from watching it happen a lot. The first is a worry about sounding basic: the interview feels like it rewards distributed-systems vocabulary, so naming actual fields can feel like bailing out of the impressive conversation into CRUD. The second is training data, in the human sense: the sample solutions people study are nearly all boxes and arrows, with the schema either omitted entirely or squeezed into a corner as an afterthought, so hundreds of hours of prep teach you that storage is one opaque thing. The third, which I have the most sympathy for, is that vagueness feels safe. Committing to concrete fields hands the interviewer something specific to poke at, and when you're nervous, keeping the storage layer abstract can feel like keeping your guard up.

From the interviewer side it worked exactly backwards. The unlabeled cylinder is where I'd steer the deep dive, because nothing had been said about it yet, and the follow-ups that live down there ("walk me through what happens when a write comes in," "what does one row of this look like," "how does that query actually find its data") are the ones a vague design can't survive. I've argued elsewhere that most design-interview failures are process failures rather than knowledge gaps, and the skipped data model is a clean example: the candidate usually knows perfectly well what a chat message would contain, they just never made themselves say it, so the whole design floated free of the data underneath it.

Start with access patterns, which you mostly already have

The data model doesn't start with tables. It starts with the list of things the product reads and writes, which is really just your functional requirements restated as queries. For a URL shortener: create a mapping (a write), redirect a short code to its long URL (a read, and the overwhelming majority of all traffic), maybe record a click for analytics (a write nobody waits on). For a chat app: append a message to a conversation, fetch the recent messages of one conversation in order, list a user's conversations sorted by activity. For a feed: publish a post, then assemble a home timeline, which is the read that makes the whole problem interesting.

That list is rarely longer than five or six items, and you already gathered most of it during the requirements phase, so writing it down takes almost no time. Attach rough numbers to each line from your estimates (the back-of-envelope article covers the math, and being off by 2x remains completely fine), because a sentence like "redirect lookups happen ten thousand times a second and creates happen ten times a second" is the sentence that will shortly do the actual deciding. In the interviews I've conducted, a candidate who paused to enumerate reads and writes out loud was giving me graded signal already, in the same way clarifying questions do during requirements, and I don't think most candidates realize how visible that little act of discipline is.

The two-minute sketch, and where it fits in the interview

If you use the five-part skeleton from the no-scale article (requirements, estimates, high-level design, deep dive, tradeoffs and failure modes), the sketch lives at the front of the high-level design, either just before you draw boxes or right alongside the first ones. Out loud it sounds like "before I draw the architecture, let me spend two minutes on what we're actually storing," which interviewers tend to like hearing, since it means the design is about to be anchored to something concrete.

The sketch itself is small. For each entity: a name, the key, the handful of fields that matter, and a note about which access pattern touches it. For the chat app it might look like this on the whiteboard:

conversation
  conversation_id (key), participant_ids, last_activity_at
  -> "list my conversations" reads this, sorted by last_activity_at

message
  conversation_id + timestamp (key), sender_id, body
  -> "recent messages" reads one conversation_id, newest first
  ~1 KB per row, append-only, never updated

user
  user_id (key), display_name, ...
  -> read by id everywhere, tiny compared to messages

No column types, no VARCHAR lengths, no third-normal-form ceremony; this is a sketch, and treating it as one is part of the skill. The choices that matter are the keys and the ordering, because those encode how each read will find its data. The little annotations about size and mutability matter too: a row that's about 1 KB, append-only, and read newest-first within one conversation is practically dictating the storage design all by itself, and saying so connects your sketch back to the estimates you just did.

Want to practice this on a real prompt, with feedback attached?

The store choice mostly falls out of the sketch

With the access patterns and the sketch on the board, the dreaded database question deflates. Lookups by a single exact key at enormous read volume, with no joins anywhere in sight, point at a key-value store (or at almost any store with a cache in front, which your numbers will tell you). A handful of entities with real relationships, queried across each other at volumes your estimate says are modest, point at a boring relational database, and "Postgres, because the data is relational and the numbers are small" is a strong answer delivered exactly that plainly. An append-heavy, time-ordered pattern read within one partition (chat messages, event logs) points toward the wide-column family. Specialty needs like full-text search or geospatial queries point at specialty stores, which are articles of their own.

I do a fair bit of woodworking, and the SQL vs NoSQL argument, held in the abstract, has always reminded me of arguing maple versus cedar before anyone has drawn the piece. Once the cut list exists (this part bears weight, this face shows, this piece lives outside in the rain), the stock mostly picks itself, and nobody at the lumber yard treats the choice as a question of loyalty. The interview version works the same way, which is why I said the data model picks your database for you: derive the store from the sketch and the argument stops being about which one you believe in. In the interviews I've conducted, I can't recall the denomination itself ever deciding a writeup, while the justification carried real weight, and an unjustified fashionable pick ("we'll use MongoDB," full stop) reliably drew follow-ups that a derived pick never did. Practices vary, and you may well meet an interviewer with stronger database opinions than mine, but a choice derived out loud from stated access patterns is defensible in front of any of them, which is what you want to optimize for.

Trace one write through every box

The sketch covers the data at rest. The second discipline covers the data in motion, and it's the best antidote I know to the hand-waved write path: pick one concrete write and follow it through every box on the board, saying at each stop where it lands and in what shape. "Alice sends 'lunch?' to Bob" arrives as a request body at the API, becomes a message row keyed by conversation and timestamp, lands on whichever partition owns that conversation, bumps last_activity_at on the conversation row, invalidates or updates the cached recent-messages entry, and, if the design has a search feature, eventually gets indexed over there too.

Two useful things happen when you run this trace. The first is that you find your own design bugs before the interviewer does: the box that receives data in no particular shape, the two stores that both get updated with no stated order between them, the derived copy nobody ever refreshes. (Every derived copy of your data, the cache entry, the search index, the precomputed feed, is one more place your write has to land, which is a big part of why the cache box is so much harder to defend than to draw.) The second is that "walk me through what happens when a write comes in" was one of the deep-dive steers I reach for most as an interviewer, and candidates who had clearly traced writes in practice sounded different within the first sentence of their answer, because they said where the data landed and what it looked like at each stop, where everyone else narrated arrows.

If you want it as a guard rule in the same spirit as the rest of this series: don't draw a box that stores data unless you can say what's inside it and how it got there. That one rule removes most of the wobble I opened this article with, and it scales down gracefully under time pressure, since even a one-line answer ("that's the message table from my sketch, keyed by conversation") keeps the design anchored.

Building the habit

The encouraging part is that this is one of the cheapest skills in design prep to build. For every practice prompt, add two small rituals: after requirements and estimates, force the two minutes (access patterns written as queries, then entities with keys), and somewhere in the deep dive, run one full trace-one-write pass out loud even when nobody asked for it. If you're practicing alone, the solo routine absorbs both additions as-is, and the recording will tell you whether you said what the data looked like at each stop or just waved at arrows. A friend and a whiteboard cover the interactive version for free, and if you want something on demand that steers your deep dive and probes the write path the way an interviewer would, that's part of what we build at Preppable.

Nearly everyone's early diagrams look like that, largely because the materials we all learn from drew it that way first, and of all the gaps I watched sink design interviews, this is the cheapest one to close. Two minutes of schema and one traced write, repeated on every practice prompt until they're reflexes, and you'll walk into the database question with the answer already derived on the board, which is a much nicer place to be standing when the follow-ups start.

// Practice this for real

Put this into practice on a real design question

Whiteboard the design the way you would in the interview, then get structured feedback on the tradeoffs you named and the ones you skipped. Free to start, no credit card.

Not ready yet? Get new posts by email. We usually publish a couple times a week.

Unsubscribe any time. See our Privacy Policy.

About the author

Jordan Beland avatar

Jordan Beland

Co-founder & CTO, Preppable

Principal architect with 10+ years designing and scaling production-grade Azure systems, with deep expertise in distributed systems and developer platforms. Has run countless interview loops from the interviewer side across coding, system design, and behavioral rounds, and sat in the debriefs afterward.

Connect on LinkedIn