Designing a Database Schema You Won't Regret

Of all the decisions in a backend, the schema is the one you change least willingly. Code you refactor in an afternoon. A schema that's wrong has data sitting on top of it, foreign keys pointing at it, and queries assuming it — changing it means a migration, a backfill, and a held breath. So it's worth getting close to right the first time.

This is the process I actually follow when modeling a new domain, before I write a single line of application code.

Start with the nouns, not the tables

Before thinking about columns, I list the entities — the real things the system is about. For a scheduling app: users, appointments, services, organizations. Each stable entity usually becomes a table. Each has attributes (columns) and relationships to other entities.

The trap is jumping straight to "what fields do I need." Model the things and their relationships first; the columns fall out naturally and you avoid stuffing unrelated data into one bloated table.

Every table needs a real primary key

A primary key uniquely identifies a row. My default is a surrogate key — an auto-generated id (or UUID) that has no business meaning — rather than a natural key like an email, because business values change and keys shouldn't.

CREATE TABLE users (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Notice email is still UNIQUE — it's a natural key constraint, just not the primary key. You get uniqueness without tying your foreign keys to a value that might change.

Relationships are foreign keys, and they should be enforced

The three shapes cover almost everything:

• One-to-many — an org has many users. The "many" side holds a foreign key to the "one".
• Many-to-many — a user has many roles, a role has many users. This needs a join table.
• One-to-one — rarer; often a sign the data belongs in the same table.

-- one-to-many: each appointment belongs to one user
CREATE TABLE appointments (
    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    starts_at TIMESTAMPTZ NOT NULL
);
 
-- many-to-many: users <-> roles via a join table
CREATE TABLE user_roles (
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, role_id)
);

Normalize first, denormalize on evidence

Normalization means: every fact lives in exactly one place. A user's email is stored once, in users, not copied into every order. The payoff is you can never have two rows disagree about the same fact — the classic update anomaly.

The rule of thumb (third normal form, informally): every column depends on the key, the whole key, and nothing but the key. If you find yourself copying the same value into many rows, it wants its own table.

Denormalization — deliberately duplicating data for read speed — is a real tool, but it's an optimization, and optimizations need evidence. Start normalized. Denormalize a specific hot path only when you've measured that the joins are actually the bottleneck. Premature denormalization buys you consistency bugs before you've earned any speed.

Model time and money carefully

Two column types cause outsized pain when done wrong:

• Timestamps: always store TIMESTAMPTZ (with time zone), in UTC. A naive timestamp is ambiguous the moment two users are in different zones, and you can't fix it retroactively.
• Money: never FLOAT. Floating point can't represent 0.10 exactly, and rounding errors compound. Use NUMERIC (exact decimal) or store integer cents.

Constraints are free correctness

Push invariants into the schema. A CHECK (amount >= 0), a NOT NULL, a UNIQUE — these are guarantees the database enforces on every write, forever, no matter which service or script does the writing. Every rule you can express as a constraint is one your application code doesn't have to defend and can't accidentally skip.

The mindset

Treat the schema as the foundation, not scaffolding. Application code is easy to change because it has nothing depending on it. The schema has everything depending on it. So spend the time up front: model the real entities, enforce relationships with real foreign keys, normalize until you have a reason not to, and let constraints carry the invariants. Get the foundation right and the code above it gets simpler; get it wrong and you'll feel the drag on every feature that follows.