Choosing and modeling a database

When it comes up

• Straight after the API, in every design.
• When the prompt needs history: audits, price changes, anything that must be reconstructed later.
• When relationships drive the product: permissions, sharing, social graphs.
• When write rates or data size rule out the obvious choice.

The core idea

List the entities and how they relate, then list the queries with how often each runs. The top two or three queries decide the design. A relational database gives you transactions, joins and constraints, and suits most product data. A key-value or wide-column store gives you simple access by key at very large scale. A document store fits records read and written whole. A search index answers text and filter queries. Many systems use more than one.

Design the keys and indexes for the main queries, and say what each index costs on writes. Denormalize on purpose when a join would be too slow, and say how the copies stay in step. When history matters, store changes as immutable events and derive the current state, rather than overwriting rows.

An example schema

Meeting-room booking, read mostly by room and by person:

rooms(room_id PK, building, floor, capacity)
bookings(booking_id PK, room_id FK, user_id, starts_at, ends_at, status)
    index (room_id, starts_at)   -- "what's booked in this room this week?"
    index (user_id, starts_at)   -- "my upcoming bookings"

Rule: no two confirmed bookings for one room may overlap.
    -> check for an overlap and insert inside one transaction,
       or use an exclusion constraint where the database has one.

Trade-offs to name

• Normalized data (one copy, easy to keep correct) against denormalized copies (fast reads, more writes to keep in step).
• Transactions and constraints in a relational database against the scale and simplicity of a key-value store.
• More indexes (faster reads) against slower writes and more storage.
• Strong consistency against eventual consistency for each kind of data.
• Storing derived values such as counts (fast) against computing them (always right).

Common mistakes

• Naming a database product before listing the queries it has to serve.
• No key or index for the most frequent query.
• Overwriting rows when the requirements need history.
• Ignoring how data is deleted, which matters for privacy and for storage cost.
• Choosing a NoSQL store for scale the estimate does not show.

How to explain it out loud

Say the access patterns first: "The two queries that matter are a room's bookings for a week and a person's upcoming bookings, so I'll index both by their owner and start time." Then name the store and why it fits those queries. In Devana's rubric, data modeling and storage is a quarter of the design half of the score.

Point at the invariant the data must keep, such as no overlapping bookings, and say exactly where it is enforced. Interviewers push on that line, and a clear answer ("inside a transaction, so two requests can't both succeed") is strong evidence of design depth.