Chapter 12: Schema Design Patterns for Real Applications

Theory is great, but how do you actually design schemas for real systems? This chapter covers the patterns you'll use daily.

Pattern 1: One-to-Many

The most common relationship. One parent has many children.

-- One customer has many orders
CREATE TABLE customers (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    total       NUMERIC(10,2),
    created_at  TIMESTAMPTZ DEFAULT NOW()
);
-- FK goes on the "many" side

Pattern 2: Many-to-Many

Requires a junction (join/bridge) table.

-- Students ↔ Courses (many-to-many)
CREATE TABLE students (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE courses  (id SERIAL PRIMARY KEY, title TEXT);

-- Junction table
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id) ON DELETE CASCADE,
    course_id  INTEGER REFERENCES courses(id) ON DELETE CASCADE,
    enrolled_at TIMESTAMPTZ DEFAULT NOW(),
    grade       CHAR(1),
    PRIMARY KEY (student_id, course_id)  -- composite PK prevents duplicates
);

Pattern 3: One-to-One

Used to split a table (separate sensitive data, or optional extensions).

-- User profile is optional/separate from auth credentials
CREATE TABLE users (
    id       SERIAL PRIMARY KEY,
    email    TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL
);

CREATE TABLE profiles (
    user_id  INTEGER PRIMARY KEY REFERENCES users(id),  -- PK = FK!
    bio      TEXT,
    avatar   TEXT,
    location TEXT
);

Pattern 4: Self-Referencing (Hierarchies)

-- Org chart / category tree
CREATE TABLE categories (
    id        SERIAL PRIMARY KEY,
    name      TEXT NOT NULL,
    parent_id INTEGER REFERENCES categories(id)  -- points to same table
);

-- Electronics → Phones → Smartphones
INSERT INTO categories (id, name, parent_id) VALUES
    (1, 'Electronics', NULL),
    (2, 'Phones', 1),
    (3, 'Smartphones', 2);

Pattern 5: Polymorphic Associations

When multiple tables need the same child (e.g., comments on posts AND comments on photos):

-- Option A: Separate FK columns (simple, nullable)
CREATE TABLE comments (
    id       SERIAL PRIMARY KEY,
    body     TEXT,
    post_id  INTEGER REFERENCES posts(id),
    photo_id INTEGER REFERENCES photos(id),
    CHECK (
        (post_id IS NOT NULL AND photo_id IS NULL) OR
        (post_id IS NULL AND photo_id IS NOT NULL)
    )
);

-- Option B: Separate tables (cleaner, no NULLs)
CREATE TABLE post_comments (
    id      SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(id),
    body    TEXT
);
CREATE TABLE photo_comments (
    id       SERIAL PRIMARY KEY,
    photo_id INTEGER NOT NULL REFERENCES photos(id),
    body     TEXT
);

Pattern 6: Audit Trail / History

-- Track all changes to a table
CREATE TABLE users_history (
    history_id  SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL,
    name        TEXT,
    email       TEXT,
    changed_at  TIMESTAMPTZ DEFAULT NOW(),
    changed_by  TEXT,
    operation   CHAR(1) CHECK (operation IN ('I', 'U', 'D'))
);
-- Populated via triggers on INSERT/UPDATE/DELETE

Pattern 7: Soft Delete

-- Don't actually delete, just mark as deleted
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    name       TEXT,
    email      TEXT,
    deleted_at TIMESTAMPTZ  -- NULL = active, non-NULL = deleted
);

-- "Delete" a user
UPDATE users SET deleted_at = NOW() WHERE id = 1;

-- Query only active users
SELECT * FROM users WHERE deleted_at IS NULL;

Real-World Example: E-Commerce Schema