# 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.