Topic 0.3
The Golden Database Design Framework
In one line
Design in a fixed order: requirements → entities → relationships → access patterns → consistency and transaction needs → scale → data model → database choice → indexes → queries → caching → replication → partitioning → sharding → backup/DR → security → observability → cost → failure handling → trade-offs. Choosing the database comes in the middle, not the start.
Think of it like this
Building a house. You don't start by buying bricks; you ask who lives there, how they'll use each room, and what the plot allows. Materials are chosen once the plan exists.
Key ideas
- 01
Requirements first: functional (what data and operations), non-functional (latency, availability, durability, compliance), constraints (team skills, budget, cloud).
- 02
Entities and relationships give the logical model; access patterns (every important read and write with its frequency, filters, sort and latency target) give the physical model. Phase 3.5 turns this into a table you fill in.
- 03
Consistency and transaction needs decide the hardest parts: which operations must be atomic together, which reads must see the latest write, what can be eventually consistent.
- 04
Scale numbers decide whether a single primary with replicas is enough (it usually is, to a surprisingly large size) or whether partitioning and sharding are needed.
- 05
Mastery levels used in this course: L1 understands, L2 can implement, L3 can design with it, L4 explains trade-offs and failure modes, L5 designs under pressure and defends it. Aim for L5 on core relational design and interviews, L4 on internals, L3–L4 on specialised stores.
Code & diagrams
Interview problem
The problem
"Design the database for a food delivery app" in 45 minutes
An interviewer says only "Design the database for a food delivery app." Show how you would structure the first ten minutes using the framework, before drawing any tables.
When it breaks
Choosing the database first
What you see
The team picks a document store for fashion, then discovers orders need multi-document transactions and ad-hoc reporting; months of workarounds follow.
Fix & prevent
Write the access-pattern and consistency table before any technology decision; make the choice justify itself against it.
Explain it without notes
Why does the framework place "database choice" after access patterns and consistency?
Practice
Apply the first five steps of the framework to a hotel booking system and write them down in one page.
Trade-offs
- ↔
Following the framework takes a few minutes up front and saves the most expensive mistakes: the wrong data model and the wrong store.
Done when you can
I can drive a database design from requirements to trade-offs in a fixed order.