Designing schemas you can live with
A field guide to database design that survives the first six months of growth.
The hardest part of a database isn’t the queries. It’s the schema you wrote on day one, while sprinting toward a deadline, and now own forever.
Here are the rules I’ve stolen, broken, and re-adopted over the years.
1. UUIDs by default, integers for hot paths
Surrogate primary keys should be UUIDs (v7 if you can). They survive merges, exports, multi-region, and accidental enumeration. The throughput cost is real but rarely matters until you’re at serious scale.
2. Soft delete is a trap
deleted_at columns spread complexity through every query. Most “we might need to recover this” requirements are imagined. If you really need recovery, build an audit log and hard delete.
3. Normalize first, denormalize with evidence
Don’t denormalize because it might be faster. Denormalize when you have a measured problem, a clear write pattern, and a maintenance plan.
4. Migrations are first-class code
Every migration should be:
- Reversible (or explicitly marked irreversible with reasoning)
- Idempotent
- Tested locally against production-sized data
- Reviewed by someone other than the author
5. The 80/20 of indexes
Index your foreign keys. Index your WHERE columns. Stop. Don’t index every column you might one day query — indexes have write cost.
The schema is the longest-lived artifact in your codebase. Treat it that way.