kid-sid/claude-spellbook/database-design

-- BAD: repeating groups in a single column
CREATE TABLE orders (
    id        BIGSERIAL PRIMARY KEY,
    user_id   BIGINT NOT NULL,
    item_ids  TEXT NOT NULL  -- "1,2,3" stored as a string
);
 
-- GOOD: each value gets its own row
CREATE TABLE orders (
    id      BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL
);
 
CREATE TABLE order_items (
    id         BIGSERIAL PRIMARY KEY,
    order_id   BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id)
);

-- BAD: composite key (order_id, product_id), but product_name depends only on product_id
CREATE TABLE order_items (
    order_id     BIGINT NOT NULL,
    product_id   BIGINT NOT NULL,
    product_name TEXT NOT NULL,   -- partial dependency: only on product_id
    quantity     INT  NOT NULL,
    PRIMARY KEY (order_id, product_id)
);
 
-- GOOD: move product_name to the products table
CREATE TABLE products (
    id   BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
 
CREATE TABLE order_items (
    order_id   BIGINT NOT NULL,
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- BAD: zip_code -> city is a transitive dependency; city is not a key
CREATE TABLE users (
    id       BIGSERIAL PRIMARY KEY,
    name     TEXT NOT NULL,
    zip_code VARCHAR(10),
    city     TEXT          -- city depends on zip_code, not on id
);
 
-- GOOD: extract the transitive dependency into its own table
CREATE TABLE zip_codes (
    zip_code VARCHAR(10) PRIMARY KEY,
    city     TEXT NOT NULL,
    state    CHAR(2) NOT NULL
);
 
CREATE TABLE users (
    id       BIGSERIAL PRIMARY KEY,
    name     TEXT NOT NULL,
    zip_code VARCHAR(10) REFERENCES zip_codes(zip_code)
);

-- BIGSERIAL
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY
);
 
-- UUID v7 (requires pg_uuidv7 extension or application-generated)
CREATE EXTENSION IF NOT EXISTS "pg_uuidv7";
 
CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7()
);

-- BAD: float for money, timestamp without timezone, int for boolean
CREATE TABLE invoices (
    id         SERIAL PRIMARY KEY,
    amount     FLOAT,                     -- BAD: floating point
    created_at TIMESTAMP,                 -- BAD: no timezone
    is_paid    INT DEFAULT 0              -- BAD: use BOOLEAN
);
 
-- GOOD
CREATE TABLE invoices (
    id         BIGSERIAL PRIMARY KEY,
    amount     NUMERIC(12,2) NOT NULL,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    is_paid    BOOLEAN      NOT NULL DEFAULT FALSE
);

CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT        NOT NULL,
    total      NUMERIC(12,2) NOT NULL,
    created_at TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
 
    CONSTRAINT fk_orders_users        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT ck_orders_total_pos    CHECK (total >= 0)
);
 
CREATE UNIQUE INDEX uq_users_email    ON users (email);
CREATE        INDEX idx_orders_user_id ON orders (user_id);

CREATE TABLE posts (
    id         BIGSERIAL    PRIMARY KEY,
    user_id    BIGINT       NOT NULL,
    title      TEXT         NOT NULL,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
 
    CONSTRAINT fk_posts_users FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE user_roles (
    user_id    BIGINT      NOT NULL,
    role_id    BIGINT      NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 
    PRIMARY KEY (user_id, role_id),
 
    CONSTRAINT fk_user_roles_users FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_roles_roles FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

-- BAD: no FK enforcement, fragile JOIN logic
CREATE TABLE comments (
    id          BIGSERIAL PRIMARY KEY,
    entity_type TEXT   NOT NULL, -- 'post' | 'photo' | 'video'
    entity_id   BIGINT NOT NULL, -- could reference any table
    body        TEXT   NOT NULL
);
 
-- No FK here — the database has no idea what entity_id points to.

-- GOOD: separate relationship tables, full FK enforcement
CREATE TABLE post_comments (
    id         BIGSERIAL PRIMARY KEY,
    post_id    BIGINT NOT NULL REFERENCES posts(id)   ON DELETE CASCADE,
    body       TEXT   NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE photo_comments (
    id         BIGSERIAL PRIMARY KEY,
    photo_id   BIGINT NOT NULL REFERENCES photos(id)  ON DELETE CASCADE,
    body       TEXT   NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- B-tree (default): range query on created_at
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);
 
-- GIN: query inside JSONB column
CREATE INDEX CONCURRENTLY idx_events_payload ON events USING GIN (payload);
 
-- Partial: only index active (non-deleted) rows
CREATE INDEX CONCURRENTLY idx_users_email_active ON users (email)
    WHERE deleted_at IS NULL;
 
-- Full-text search
CREATE INDEX CONCURRENTLY idx_articles_fts ON articles USING GIN (to_tsvector('english', title || ' ' || body));

-- Query: WHERE tenant_id = $1 AND created_at > $2 ORDER BY created_at DESC
 
-- BAD: range column first — tenant_id predicate cannot use the index efficiently
CREATE INDEX idx_orders_bad ON orders (created_at, tenant_id);
 
-- GOOD: equality column first, then range column
CREATE INDEX idx_orders_tenant_created ON orders (tenant_id, created_at);

-- Without INCLUDE: index scan + heap fetch for each row to retrieve status
CREATE INDEX idx_orders_user ON orders (user_id, created_at);
 
-- With INCLUDE: index-only scan if status is the only extra column needed
CREATE INDEX idx_orders_user_covering ON orders (user_id, created_at)
    INCLUDE (status, total);

-- Check index bloat with pgstattuple (requires extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
 
SELECT * FROM pgstatindex('idx_orders_user_id');
-- free_percent > 30 is a sign of bloat worth addressing
 
-- Rebuild index without locking (safe for production)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;