Database schema design: get it right before you have users

Application code is easy to refactor. A schema with a million rows and foreign keys pointing at it is not. A few hours of design now saves a migration weekend later.

Model the domain, then the screens

Start from the real-world entities and their relationships. Build tables for those. Resist shaping the schema around the first UI — screens change, the domain rarely does.

Non-negotiables

• A surrogate primary key on every table, plus unique constraints on the natural keys.
• Foreign keys with explicit ON DELETE behaviour — the database should not allow orphans.
• Money as decimal or integer minor units; timestamps in UTC; enums as lookup tables or checked columns, not free text.
• created_at / updated_at everywhere. Add deleted_at only where you actually need soft deletes.

Index with the query plan in mind

Index the columns you filter and join on, and composite-index the common multi-column filters. Then read the query plan on realistic data volumes — not ten test rows.

Plan for history

If anyone will ever ask "what did this look like last month", design an audit table or an append-only event log now. Reconstructing history after the fact is guesswork.