# Patterns — Designing Database Schemas

# Contents

Canonical table template
One-to-many and many-to-many
Partial unique index with soft delete
Audit table (hard-delete alternative)
Enum strategies
updated_at trigger
Dialect deviations (MySQL, SQLite)
Gotchas

# Canonical table template

sql

CREATE TABLE app_user (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text NOT NULL UNIQUE,
    display_name text NOT NULL,
    status      text NOT NULL DEFAULT 'active'
                CHECK (status IN ('active', 'suspended', 'closed')),
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

# One-to-many and many-to-many

sql

CREATE TABLE user_order (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     bigint NOT NULL REFERENCES app_user (id) ON DELETE RESTRICT,
    total_cents integer NOT NULL CHECK (total_cents >= 0),
    created_at  timestamptz NOT NULL DEFAULT now()
);
-- Rule 6: index the FK immediately.
CREATE INDEX user_order_user_id_idx ON user_order (user_id);

-- N-N: junction table, composite PK, both FKs indexed (PK covers the first).
CREATE TABLE order_tag (
    order_id bigint NOT NULL REFERENCES user_order (id) ON DELETE CASCADE,
    tag_id   bigint NOT NULL REFERENCES tag (id) ON DELETE CASCADE,
    PRIMARY KEY (order_id, tag_id)
);
CREATE INDEX order_tag_tag_id_idx ON order_tag (tag_id);

ON DELETE decision: RESTRICT (default choice — force explicit cleanup), CASCADE only for true child rows (junction rows, line items), SET NULL when the relationship is optional history.

# Partial unique index with soft delete

sql

ALTER TABLE app_user ADD COLUMN deleted_at timestamptz NULL;

-- Plain UNIQUE(email) would block re-registering a deleted email:
DROP INDEX IF EXISTS app_user_email_key;
CREATE UNIQUE INDEX app_user_email_live_key
    ON app_user (email) WHERE deleted_at IS NULL;

Every live-row query now needs WHERE deleted_at IS NULL — encode it in a view if the application layer can't be trusted to remember.

# Audit table (hard-delete alternative)

sql

CREATE TABLE app_user_audit (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    bigint NOT NULL,          -- no FK: row may be gone
    action     text NOT NULL CHECK (action IN ('insert','update','delete')),
    old_row    jsonb,
    changed_at timestamptz NOT NULL DEFAULT now()
);

# Enum strategies

Default — text + CHECK (cheap to extend: one ALTER TABLE … DROP/ADD CONSTRAINT):

sql

status text NOT NULL CHECK (status IN ('draft','published','archived'))

Escape hatch — native CREATE TYPE … AS ENUM when many tables share the set; note that removing enum values is painful. Lookup table when values carry attributes (label, sort order) or are user-editable.

# updated_at trigger

sql

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

CREATE TRIGGER app_user_touch BEFORE UPDATE ON app_user
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

# Dialect deviations (MySQL, SQLite)

PostgreSQL	MySQL 8	SQLite
`bigint GENERATED ALWAYS AS IDENTITY`	`BIGINT AUTO_INCREMENT`	`INTEGER PRIMARY KEY` (rowid)
`timestamptz`	`TIMESTAMP` (stored UTC) — store app-side UTC	`TEXT` ISO-8601 UTC
`text` freely	prefer `VARCHAR(n)`; `TEXT` can't be fully indexed	`TEXT`
partial indexes	none — emulate with generated column	supported
`CHECK` enforced	enforced 8.0.16+	enforced, but FKs need `PRAGMA foreign_keys = ON`

# Gotchas

PostgreSQL folds unquoted identifiers to lowercase — never create quoted CamelCase names.
UNIQUE allows multiple NULLs (PostgreSQL < 15 semantics); use UNIQUE NULLS NOT DISTINCT (15+) or NOT NULL if that's wrong for the domain.
FK constraints don't create indexes (rule 6) — the referenced side's PK is indexed, the referencing column is not.
serial is legacy; GENERATED ALWAYS AS IDENTITY is the standard-conforming replacement.
Random UUIDv4 PKs fragment B-tree indexes at scale; prefer UUIDv7 (time-ordered) when using uuid.
varchar(255) has no performance benefit over text in PostgreSQL — the limit is only a constraint.
Money as float fails equality checks and loses cents in aggregation — numeric or integer cents, always.