database-conventions

Database Conventions

Design and operational guidelines for relational databases (PostgreSQL primary dialect; MySQL/SQLite notes included where they differ). Apply these when creating schemas, reviewing migrations, or writing queries.

---

Naming

• Tables: snake_case, plural (users, audit_logs, order_items)
• Columns: snake_case, singular (user_id, created_at, is_active)
• Primary keys: id (serial/UUID)
• Foreign keys: <referenced_table_singular>_id (user_id, order_id)
• Booleans: is_ or has_ prefix (is_active, has_verified_email)
• Timestamps: created_at, updated_at, deleted_at (UTC, with timezone)

---

Schema design

• Every table has a primary key. Prefer UUID (gen_random_uuid()) for distributed or externally-exposed IDs; serial/bigserial for internal join tables where IDs never leave the DB.
• Always store timestamps with timezone (TIMESTAMPTZ in Postgres).
• Soft-delete via deleted_at IS NULL filter, not DELETE — unless you have a clear data retention policy that requires hard deletes.
• Avoid TEXT for constrained values — use an ENUM or a lookup table so the DB enforces the constraint.

-- Good: constrained status field
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

CREATE TABLE orders (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id),
    status      order_status NOT NULL DEFAULT 'pending',
    total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

---

Indexes

• Every foreign key column gets an index (Postgres doesn't auto-index FKs).
• Add an index on any column used in a WHERE, ORDER BY, or JOIN in frequently-run queries.
• Composite indexes: column order matters — put equality conditions first, range/sort columns last.
• Don't index every column — indexes slow writes and consume storage.