Technical

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.

Open the full map

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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

Now try it yourself

Design the database schema for a multi-tenant project management app with time tracking
The schema needs five core entities: Tenants, Users, Projects, Tasks, and TimeEntries. Use a shared-schema multi-tenancy model with a tenant_id column and row-level security. Time entries should reference both tasks and users, with denormalized daily rollups for reporting performance.
How should we handle soft deletes without breaking referential integrity?
Add a deleted_at timestamp column to soft-deletable tables. Create a partial index WHERE deleted_at IS NULL for queries that filter active records. For foreign keys, keep the references intact — a deleted project's tasks should still resolve. Use application-level filtering rather than database-level CASCADE to avoid accidental hard deletes of related records.

Ready to try database design?