Schema Design Habits

The blueprint phase

Designing a schema is drawing the blueprint before pouring concrete. A good blueprint saves months of renovation later. A bad one means re-routing plumbing after the residents have moved in.

Six habits that keep schemas honest

Normalise first; denormalise on measured pain. Every entity in its own table; foreign keys connecting them. Denormalisation (computed totals, duplicated names) is a performance optimisation, not a starting point.
snake_case everywhere: users, order_items, created_at. Plural tables, singular columns, table_id for foreign keys. Consistency beats taste.
Always include created_at and updated_at as TIMESTAMPTZ NOT NULL DEFAULT now(). Add a trigger that bumps updated_at on every UPDATE. You will need this for debugging, syncing, and audit. Add it now or regret it later.
NOT NULL by default; allow NULL by exception.
Constraints are documentation — every CHECK, UNIQUE, and FK encodes a rule the application can rely on.
Soft delete instead of hard delete when audit trails matter — add deleted_at TIMESTAMPTZ and exclude rows with non-null deleted_at from queries. (Don't soft-delete by default; pick when it's worth it.)

The auto-bumping updated_at trigger

Worth memorising — you'll write this trigger on most tables.

Code

The 'always have these columns' template·sql

CREATE TABLE things (
    id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- ... your business columns here ...
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Auto-bumping updated_at trigger·sql

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER things_updated_at
BEFORE UPDATE ON things
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Soft-delete pattern·sql

ALTER TABLE things ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE INDEX things_active_idx ON things (id) WHERE deleted_at IS NULL;

-- Application convention: filter out deleted rows in views / reusable WHEREs.
CREATE VIEW active_things AS
  SELECT * FROM things WHERE deleted_at IS NULL;

The blueprint phase

Six habits that keep schemas honest

The auto-bumping updated_at trigger

Code

External links

Exercise

Progress

Comments 0