Question 3 · Track 1 · How to run the interview
The API and the data model
Boxes are cheap. The write path is where the design actually lives.
How to run the interview. 3 clarifying questions to ask first, the answer in 4 moves, the follow-up that catches a memorised answer and 3 supporting topics to watch or read.
What it is really testing
Whether you can turn a requirement into an access pattern, and an access pattern into a key.
Ask these first
Before a single box goes on the board. The answers change the design, which is the point of asking out loud.
- What are the three or four calls a client actually makes?
- What is the one entity everything hangs off?
- How is that entity read — by id, by owner, by time range?
The answer, in 4 moves
In this order. Each move earns the next one — say them out loud rather than drawing all four and narrating afterwards.
- 1Name the endpoints first. Three or four, with their inputs.
- 2Name the entity and its primary key.
- 3Name the access pattern that key serves.
- 4Name the index (or the second table) that serves the other pattern.
The trap
The follow-up that separates a rehearsed answer from a real one.
Choose the partition key before you choose the database. A key that spreads writes evenly and still answers the main read is the whole decision; the product name on the box is downstream of it.
Watch or read
The pieces of this answer, each covered on its own. Take them whichever way suits you, then give the whole answer without looking.
Data modeling
Data modeling is designing which facts live where. Learn the three layers (conceptual, logical, physical), normalization vs. denormalization, and why your schema is the contract every pipeline depends on.
How a database index works
The same query, 4.2 seconds then 3 milliseconds — and the only thing that changed was one line of SQL. Most explanations stop at 'it's like the index in a book.' This one goes a level below: what a table actually is on disk, why a full table scan is the database's only option without an index, how a B-tree gets you there in three hops, and the cost nobody mentions — every write has to update every index.
Offset vs cursor pagination
LIMIT 20 OFFSET 40 works perfectly on page two — and page two is the one page that hides both of offset pagination's defects. Deep pages get slower because the database builds and discards every skipped row, and an offset is a position in a list that keeps moving, so inserts duplicate rows across pages and deletes make them vanish. Here's how keyset (cursor) pagination fixes both by pointing at a row instead of counting positions, what it honestly costs you, and how to pick.
Part of System Design Interviews, Answered Out Loud. The fundamentals underneath it are the free CS course.