In short
A good schema models the real entities in your domain and the real relationships between them, using the database's own guarantees — foreign keys, unique constraints, not-null — to make invalid states impossible rather than merely unlikely. Normalise by default and denormalise only against a measured read problem. The decisions worth agonising over are the ones with data already shaped by them: identifiers, timestamps, and how you represent state.
Model the domain, not the screens
The most common early mistake is designing tables that mirror the current interface. Screens change every few months; the underlying entities rarely do.
Start by naming the real things in the domain — the nouns a customer would recognise — and the relationships between them. An order belongs to a customer. An invoice covers one or more orders. Those facts survive three redesigns.
A useful test: could you explain the schema to someone in the business without mentioning the app? If not, you have modelled the UI.
Get identifiers right first
Hard to change later, so decide deliberately.
Auto-incrementing integers are compact, fast to index, and readable in logs. They also leak information — a competitor can tell how many customers you have — and they collide when merging data from multiple sources.
UUIDs avoid both problems and can be generated client-side before an insert, which simplifies plenty of flows. Random UUIDs (v4) fragment index locality and hurt insert performance at scale; time-ordered variants (v7) largely fix that, and are the sensible default when you want UUIDs.
Never use a natural key as a primary key. Email addresses change. Company registration numbers get reissued. Anything meaningful to a human will eventually need to change, and changing a primary key means updating every reference to it.
Store time properly
Store timestamps in UTC, always. Convert at the edges, for display. A database holding local times without offsets is a source of bugs nobody ever fully clears.
Use a type that includes a time zone where your database offers one. Record created_at and updated_at on nearly everything — they cost almost nothing and you will want them during your first incident.
Where the user's own time zone matters — a scheduled reminder, a booking — store their time zone as a separate field. "9am" means different absolute instants depending on where someone is, and their offset changes twice a year.
Normalise by default
Normalisation means storing each fact once. The practical version: if you are copying the same value into many rows, it probably belongs in its own table.
The benefit is not elegance but correctness. When a customer's name lives in one place, updating it updates everything. When it is copied into every order, you get a system where the same customer has three different names and no one is authoritative.
Denormalise deliberately and only against a measured problem — a read that is genuinely too slow, in production, with real data volumes. Denormalising in anticipation is how teams inherit consistency bugs they never needed.
The honest exception is deliberate historical copies. An invoice should store the address it was sent to and the price charged, because those are facts about the invoice, not the current customer. That is not denormalisation; it is modelling the domain correctly.
Let the database enforce the rules
This is where most application bugs could have been prevented and were not.
Foreign keys stop orphaned rows. Skipping them because they are inconvenient in tests guarantees rows pointing at things that no longer exist.
Not-null on anything genuinely required. Nullable columns spread if statements through your entire codebase.
Unique constraints where duplicates are invalid. Checking uniqueness in application code is a race condition; two simultaneous requests both pass the check.
Check constraints for simple invariants — a quantity above zero, a status within a known set.
The principle: make invalid states impossible rather than merely unlikely. Application code can be bypassed by a migration, a script, an admin panel, or a bug. Constraints cannot.
Representing state
Most tables end up with a status. Two common approaches, each with a real trade-off.
A status column on the row is simple and easy to query. It tells you where something is now, and nothing about how it got there.
An event table records each transition. It gives you history and audit for free, and answers questions like "how long does this usually take" that a status column cannot.
For anything where the history matters — orders, payments, approvals, moderation — record the transitions. Retrofitting history is impossible; you cannot recover events you never stored.
Deletion is a modelling decision
Decide early whether records are ever truly removed.
Hard delete is simple and genuinely required for some privacy obligations. It also destroys referential history — an order pointing at a deleted customer.
Soft delete keeps the row with a deleted_at, preserving history at the cost of remembering to filter it in every query. Miss one and deleted records reappear somewhere embarrassing.
There is no universally right answer. The wrong answer is not deciding, and ending up with both patterns in the same database.
Money and precision
Never store money in a floating-point type. Binary floats cannot represent decimal fractions exactly, and rounding errors accumulate.
Use an integer count of minor units — cents, pence — or a fixed-precision decimal type. Store the currency alongside the amount; an amount without a currency is not a price.
Plan for migrations from the start
Your schema will change. Make that routine rather than exceptional.
Keep migrations in version control, applied in order, forward-only in production. Write them to be safe on a live table — adding a nullable column is cheap; adding a non-null column with a default can rewrite an entire table and lock it.
The rule that avoids most incidents: make schema changes backward-compatible, and deploy them separately from the code that needs them. Add the column, deploy, backfill, then deploy the code that uses it. Doing both at once means any rollback breaks something.
Before you build
Write down the entities and relationships and check the list against your actual queries. Confirm identifiers, timestamps, money, and the delete policy are decided. Then confirm every rule you rely on is enforced by a constraint rather than by discipline.
An hour here saves the kind of week nobody enjoys.
If you are modelling something with real consequences and want it reviewed before the first migration runs, book a call.
Common questions
Should I use UUIDs or auto-incrementing IDs?
Auto-incrementing integers are compact and fast but leak volume information and collide when merging data sources. UUIDs avoid both and can be generated before insert, but random v4 UUIDs fragment index locality — use a time-ordered variant such as v7 if you want UUIDs. Either way, never use a natural key like an email address as a primary key.
Should I normalise or denormalise my database?
Normalise by default, so each fact is stored once and updates are unambiguous. Denormalise only against a measured read problem in production with real data volumes. Storing historical copies — the address an invoice was sent to, the price charged — is not denormalisation; those are facts about the record rather than about the current customer.
How should I store money in a database?
As an integer count of minor units such as cents, or in a fixed-precision decimal type. Never a floating-point type, because binary floats cannot represent decimal fractions exactly and rounding errors accumulate. Always store the currency alongside the amount.
Should I use soft deletes?
It depends on whether history matters and what your privacy obligations require. Soft deletes preserve referential history but require filtering in every query, and one missed filter surfaces deleted records somewhere embarrassing. Hard deletes are simpler and sometimes legally necessary. The wrong answer is not deciding and ending up with both patterns in one database.
How do I change a schema without downtime?
Make changes backward-compatible and deploy them separately from the code that depends on them: add the column, deploy, backfill, then deploy the code that uses it. Doing both at once means any rollback breaks something. Be aware that adding a non-null column with a default can rewrite and lock an entire table.
