Work out your schema before you commit to it
Give each entity its own branch, compare keys, indexes and denormalization side by side, and test the design against your real queries.
See it in action
The URL-shortener interview question worked through end to end, from scale estimates to the schema, key generation and caching.
How it works
Describe your application and the model proposes entities and relationships. Each entity and each common query gets its own branch, and every question under it keeps that part of the schema in context.
-
Describe the domain. Explain the core concepts and how users work with them. Ask for entities, attributes and relationships, including one-to-many, many-to-many and polymorphic ones.
-
Branch on each entity’s decisions. Under an entity, ask about the primary key (auto-increment, UUID or ULID), which fields need indexes, and whether data belongs in its own table or a JSON column.
-
Add a branch per query. Write out your most common queries, such as “sum time entries for a user this week”. Under each, ask which indexes it needs and where denormalization or a materialized view would help.
-
Test at ten times the data. Under each entity, ask what breaks at 10x volume. Billions of time entries may need partitioning, and full-text search across projects may need a search index.
Why a map instead of a schema file
Schema choices trade against each other. An index speeds up one query and slows every insert on that table. Denormalizing user_name into time_entries speeds up reads, and then every name change rewrites thousands of rows. On a map, you ask about each side of a choice on sibling branches and read the answers next to each other.
Example
Say you are designing the schema for a clinic’s appointment booking system. The map splits into Patients, Providers, Appointments, Availability Slots and Insurance Records. Under Appointments you compare two ways to store recurring visits: one row with an RRULE string, or one row per visit. The RRULE branch saves storage but leaves “find all appointments on Tuesday” to application code, while the expanded branch keeps queries simple and adds thousands of rows. A third branch settles on storing the rule on a parent record and expanding only the next 90 days, with a background job that extends it.