Schema Design Coach

Schemas outlive the applications written against them, which makes schema design the highest-stakes decision most projects take in their first month. This skill coaches a design from workload to tables: what will be read, what will be written, what must never be wrong — then shapes entities, keys, constraints, and indexes to fit, and pressure-tests the result against the queries it must serve and the migrations it will someday need.

When to use this skill

• Designing tables for a new feature or service from scratch
• Reviewing a proposed schema before the first migration locks it in
• Untangling a schema that has accreted nullable columns and mystery join tables
• Deciding between normalization and denormalization for a specific hot path

Workflow

1. Start from the workload, not the nouns. List the top queries and writes the schema must serve — the five reads that will dominate traffic, the writes that must be atomic, the reports allowed to be slow. Every design decision that follows cites one of these.
2. Name the invariants. What must the database itself guarantee even if every application bug ships? Uniqueness, referential existence, value ranges, mutual exclusivity. These become constraints, not comments: unique indexes, foreign keys, checks. An invariant enforced only in application code is a suggestion.
3. Design entities at third normal form by default, and write down every deliberate departure with its triggering query from step 1. Denormalization is a performance loan; record the interest — which writes must now update two places, and what reconciles them when they drift.
4. Choose keys deliberately. Surrogate keys for entities whose natural identifiers can change; natural keys only where truly immutable and meaningful. Decide identifier exposure now: sequential ids leak volume and invite enumeration, random identifiers cost index locality.
5. Type columns as narrowly as truth allows: timestamps with time zones, money as integer minor units or decimals (never floats), enumerations constrained by check or lookup table, and nullability meaning exactly "value may be unknown" — never "we were unsure".
6. Index for the workload from step 1: filters and join columns first, composite indexes ordered to match query shape. Every index is a write tax; justify each with the query it serves.
7. Rehearse change before committing. For the two most likely future requirements (the user can usually name them), sketch the migration. A design that needs table rewrites for its most probable evolution is a design to revise now.
8. Deliver the design with its reasoning attached — DDL plus a decisions table — so the next engineer inherits the whys, not just the whats.

Output format

Workload: <top reads/writes the design serves>
DDL: <create statements with constraints and indexes>
Decisions:
| choice | alternative rejected | because |
Denormalization ledger: <each departure + reconciliation plan, or "none">
Migration rehearsal: <the two likely changes and what each costs>

Quality bar

• Every business invariant is enforced by the database, or explicitly logged as application-enforced with the reason.
• No column named data, info, or misc, and no document-typed blob standing in for undesigned structure — document columns are allowed only with a stated shape and query pattern.
• Join paths for the top five queries touch no more tables than necessary, and each has traced index support, not assumed index support.
• The design survives two questions: "what happens at a hundred times the rows?" for reads, and "what drifts?" for every denormalized copy.
• Naming is boring and consistent: one pluralization rule, one casing convention, foreign keys named after their target.