ModelingIntermediate
How do you start a schema design?
“You are given requirements for a new feature. Walk me through getting to tables and indexes.”
What this tests
- Access-pattern-first design
- Connecting indexes to queries
Answers by level
Read the beginner answer first and notice what is missing.
I start from the access patterns, not the nouns: list the questions the system must answer, each with a frequency and a latency budget. Those decide the keys, foreign keys and indexes; the nouns fall out along the way.
Then I draw the ER diagram — entities, relationships with cardinality, attributes on the right side including on relationships — and for every frequent pattern I name the index that serves it, shipping those in the same migration as the tables.
Green flags · Red flags
Strong green flag · Ties each index to a specific named query.
Green flags
- Access patterns with frequencies
- Draws the ER diagram
- Indexes shipped with the schema
Red flags
- Nouns-to-tables with no thought about queries
- No indexes until something is slow
Follow-up questions
F1
Which access pattern decides the primary key vs an index?
Scenario
Design storage for a chat app. What do you ask before writing any DDL?