The Database Case-Study Template

In one line

A fixed structure for any database design answer: requirements, scale, entities, schema, normalization, access patterns, indexes, transactions, scaling, cache, failure, backup and DR, security, and cost, ending with trade-offs. It keeps you from skipping the parts interviewers score most: numbers, invariants and failure handling.

0/5 · 0%

Think of it like this

A pilot's checklist. Even experienced pilots read it aloud, because the one skipped item is what causes the accident.

Key ideas

01
Requirements (5 min): functional operations, non-functional (latency, availability, durability, compliance), constraints. Scale (3 min): users, QPS reads/writes, storage per day, growth.
02
Model (10 min): entities and relationships, tables with keys and constraints, the normal form and any deliberate denormalization.
03
Access (5 min): the top queries with frequency, and the indexes that serve them (composite column order!).
04
Correctness (5 min): transaction boundaries, isolation, locking, and the invariants (no overbooking, balanced ledger) and how the schema enforces them.
05
Scale and survive (10 min): replicas, partitioning, sharding key, caching and invalidation, failure of each component, backup and DR with RPO/RTO, security and PII, cost. Close with the two or three trade-offs you'd revisit at 10× growth.

Code & diagrams

template.mdmarkdown

## Requirements      functional / non-functional / constraints
## Scale             users, peak read QPS, peak write QPS, storage/day, growth
## Entities          list + relationships (cardinality both ways)
## Schema            tables, columns, PK/FK, UNIQUE/CHECK/EXCLUDE
## Normalization     normal form + deliberate denormalization and its sync
## Access patterns   query | freq | filters | sort | latency | consistency
## Indexes           one per hot pattern, ESR order, covering/partial
## Transactions      boundaries, isolation, locking, invariants
## Scaling           replicas, partitioning, shard key, hot keys
## Cache             what, TTL, invalidation, stampede
## Failure           primary, replica, cache, queue, disk, bad migration
## Backup / DR       backup type, PITR, RPO, RTO, drills
## Security          authn, roles/RLS, encryption, PII, audit
## Cost              compute, storage, replicas, network
## Trade-offs        what changes at 10x

Interview problem

The problem

Timed mock: URL shortener database in 30 minutes

Use the template to design the database for a URL shortener: 100M new links/month, 10B redirects/month, custom aliases, expiry, and per-link click analytics.

When it breaks

Jumping straight to tables

What you see

The candidate designs a schema that misses the dominant access pattern and the scale, then has to redo it under time pressure.

Fix & prevent

Spend the first 5–8 minutes on requirements, numbers and access patterns; state them out loud.

Explain it without notes

Which three sections of the template do candidates most often skip, and why do they matter?

Practice

Run the template on "design the database for a food delivery app" in 35 minutes and compare with Topic 0.3.

Trade-offs

↔
A fixed template can feel rigid; it guarantees coverage, and you can reorder sections when the interviewer steers.

Done when you can

I can run a complete database design interview using the template within 45 minutes.

Command Palette