You can redesign a screen in an afternoon. Changing a column that three years of rows depend on is a migration, a backfill, and a risk. That asymmetry means your schema is the most durable design decision you make.
Model what is true, not what is displayed
A schema shaped around today's screen breaks when the screen changes. Ask what is actually true about the domain:
- A booking has a start time. Does it have an end time, or a duration?
- Can a user have two active subscriptions? If not, what enforces that?
- When a price changes, what should an old receipt show?
That last one catches teams constantly. If a receipt reads today's price from the products table, every historical invoice silently rewrites itself the moment you run a promotion.
Store what was true at the time of the event, not a pointer to what is true now.
Constraints are documentation that cannot go stale
A comment saying "status is one of pending, paid, refunded" is a wish. A CHECK constraint is a fact.
alter table payments
add constraint payments_status_check
check (status in ('pending', 'paid', 'refunded'));
The same goes for uniqueness. If two rows for the same payment would be a bug, a unique index is the only thing that actually prevents it — application code cannot, because two requests can run at once.
Nullable is a decision, not a default
Every nullable column is a question the reader has to answer: does null mean "unknown", "not applicable", or "not yet"? Those are three different things and they need different handling.
If you cannot say which, the column probably wants splitting.
Migrations are code your users run
Some rules that have saved real outages:
- Make them idempotent.
if not existseverywhere, so a half-failed run can be retried. - Add before you remove. Deploy the column, backfill it, switch the reads, then drop the old one.
- Never assume order. Someone will run them on a database that is three versions behind.
The test that catches most schema mistakes
Before you write the migration, write the two or three queries the feature needs. If any of them requires a subquery in a loop, or a join across four tables to answer something a user asks constantly, the shape is wrong.
Fixing that on paper takes ten minutes. Fixing it after launch takes a weekend.