Database Schema Designer

A schema is the one part of a system you cannot refactor with a find-and-replace: every shortcut taken at design time becomes a locked-table migration on a hot production database later. This skill produces schemas that stay correct and fast as data grows - invalid states unrepresentable, indexes matched to real queries, and every change shippable without downtime.

Operating procedure

Follow the steps in order. Constraints come before indexes because constraints define correctness and indexes only define speed; migrations come last because they depend on both.

Step 1: Gather inputs

Collect these before writing any DDL. If a number is a guess, label it a guess and proceed.

1. Entities and relationships - nouns of the domain and their cardinality (1:1, 1:N, M:N).
2. The top 5-10 queries by expected frequency, with their filter and sort columns. Indexes come from this list, not from intuition.
3. Expected row counts at 1 year and 3 years per table, and read:write ratio per hot table. Default assumption if unknown: 10:1 read-heavy.
4. Multi-tenancy - single tenant, or tenant_id on every row?
5. Deletion semantics - hard delete, soft delete, or audit-retained?

Step 2: Model, normalize, then denormalize only on evidence