Files
skills/public/database-postgres-schema-design.md

15 KiB

name, description
name description
database-postgres-schema-design Designing new database schemas

PostgreSQL Schema Design

Scope: Table design, relationships, normalization, data types Lines: ~320 Last Updated: 2025-10-18

When to Use This Skill

Activate this skill when:

  • Designing new database schemas
  • Modeling relationships (1:1, 1:N, N:M)
  • Deciding normalization levels
  • Choosing primary key strategies
  • Handling polymorphic associations
  • Planning for soft deletes or audit trails
  • Selecting appropriate data types

Core Concepts

Database Normalization

Goal: Eliminate redundancy, maintain data integrity.

First Normal Form (1NF)

Rule: Each column contains atomic values (no arrays, no repeated groups).

-- ❌ VIOLATES 1NF: Multiple emails in one column
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    emails VARCHAR(500)  -- '[email protected], [email protected]'
);

-- ✅ 1NF: Atomic values
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE user_emails (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    email VARCHAR(255)
);

Second Normal Form (2NF)

Rule: 1NF + no partial dependencies (all non-key columns depend on entire primary key).

-- ❌ VIOLATES 2NF: teacher_name depends only on teacher_id, not (student_id, teacher_id)
CREATE TABLE enrollments (
    student_id INT,
    teacher_id INT,
    teacher_name VARCHAR(100),  -- Partial dependency
    PRIMARY KEY (student_id, teacher_id)
);

-- ✅ 2NF: Separate teacher data
CREATE TABLE teachers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id INT,
    teacher_id INT REFERENCES teachers(id),
    PRIMARY KEY (student_id, teacher_id)
);

Third Normal Form (3NF)

Rule: 2NF + no transitive dependencies (non-key columns don't depend on other non-key columns).

-- ❌ VIOLATES 3NF: country depends on city (transitive dependency)
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100),
    country VARCHAR(100)  -- Depends on city, not id
);

-- ✅ 3NF: Separate city/country
CREATE TABLE cities (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(100)
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    city_id INT REFERENCES cities(id)
);

When to Denormalize

Reasons:

  • Read performance (avoid JOINs)
  • Analytics/reporting
  • Immutable data (historical snapshots)

Example: Order totals

-- Normalized (calculate on query)
SELECT SUM(quantity * price) FROM order_items WHERE order_id = 123;

-- Denormalized (store total on order)
CREATE TABLE orders (
    id INT PRIMARY KEY,
    total DECIMAL(10,2)  -- Denormalized for performance
);

-- Update total when items change (trigger or application logic)

Relationship Patterns

One-to-One (1:1)

Use case: Splitting tables for optional/large data.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    bio TEXT,
    avatar_url VARCHAR(500),
    updated_at TIMESTAMP DEFAULT NOW()
);

Key points:

  • user_id is both PRIMARY KEY and FOREIGN KEY
  • ON DELETE CASCADE ensures orphan cleanup
  • Profile is optional (can have user without profile)

One-to-Many (1:N)

Use case: Most common relationship (users → posts, orders → items).

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

Key points:

  • Foreign key on "many" side (posts.user_id)
  • Index on foreign key for JOIN performance
  • NOT NULL if every post must have a user

Many-to-Many (N:M)

Use case: Tags, roles, followers.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

-- Junction table
CREATE TABLE user_tags (
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    tag_id INT REFERENCES tags(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, tag_id)
);

CREATE INDEX idx_user_tags_tag_id ON user_tags(tag_id);

Key points:

  • Junction table with composite primary key
  • Indexes on both foreign keys
  • Can add extra columns (created_at, metadata)

Self-Referencing (Hierarchical)

Use case: Comments, org charts, file systems.

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES comments(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_comments_parent_id ON comments(parent_id);

Querying hierarchies:

-- Recursive CTE for threaded comments
WITH RECURSIVE thread AS (
    -- Base case: top-level comments
    SELECT id, parent_id, content, 1 AS depth
    FROM comments WHERE parent_id IS NULL

    UNION ALL

    -- Recursive case: child comments
    SELECT c.id, c.parent_id, c.content, t.depth + 1
    FROM comments c
    JOIN thread t ON c.parent_id = t.id
)
SELECT * FROM thread ORDER BY depth, id;

Primary Key Strategies

Serial Integer (Auto-increment)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255)
);

Pros:

  • Simple, compact (4 bytes)
  • Sequential, good for indexes
  • Human-readable

Cons:

  • Predictable (security concern for public IDs)
  • Doesn't work well in distributed systems (ID conflicts)
  • Reveals record count

UUID (Universally Unique Identifier)

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255)
);

Pros:

  • Globally unique (safe for distributed systems)
  • Unpredictable (better security)
  • Can generate client-side

Cons:

  • Larger (16 bytes vs 4)
  • Random (worse index performance)
  • Not human-readable

ULID/KSUID (Sortable UUIDs)

-- Using ulid extension
CREATE EXTENSION IF NOT EXISTS ulid;

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT ulid_to_uuid(ulid_generate()),
    email VARCHAR(255)
);

Pros:

  • Globally unique + sortable by time
  • Better index performance than pure UUID
  • Unpredictable

Cons:

  • Requires extension
  • Larger than integer

Decision Tree

Do you need distributed ID generation?
├─ Yes → UUID or ULID
│   └─ Need time-ordering? → ULID
│       └─ Otherwise → UUID
└─ No → Is predictability a security concern?
    ├─ Yes → UUID
    └─ No → SERIAL (simplest, best performance)

Common Schema Patterns

Timestamps (Audit Trail)

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Trigger to auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

Soft Deletes

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    deleted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_posts_deleted_at ON posts(deleted_at) WHERE deleted_at IS NOT NULL;

-- Query active posts
SELECT * FROM posts WHERE deleted_at IS NULL;

-- "Delete" a post
UPDATE posts SET deleted_at = NOW() WHERE id = 123;

Pros:

  • Can recover deleted data
  • Audit trail of deletions

Cons:

  • Complicates queries (must filter deleted_at IS NULL)
  • UNIQUE constraints need to account for soft deletes
  • Table grows indefinitely (need archival strategy)

Alternative: Archived tables

-- Move to archive table on delete
CREATE TABLE posts_archive (LIKE posts INCLUDING ALL);

INSERT INTO posts_archive SELECT * FROM posts WHERE id = 123;
DELETE FROM posts WHERE id = 123;

Versioning/History

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    version INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE document_versions (
    id SERIAL PRIMARY KEY,
    document_id INT REFERENCES documents(id) ON DELETE CASCADE,
    version INT NOT NULL,
    title VARCHAR(255),
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE (document_id, version)
);

-- Save version on update (trigger or application logic)

Polymorphic Associations (Multiple Types)

Problem: Comments can belong to posts OR videos.

Option 1: Exclusive FKs (Best)

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
    video_id INT REFERENCES videos(id) ON DELETE CASCADE,
    content TEXT,
    CHECK (
        (post_id IS NOT NULL AND video_id IS NULL) OR
        (post_id IS NULL AND video_id IS NOT NULL)
    )
);

CREATE INDEX idx_comments_post_id ON comments(post_id) WHERE post_id IS NOT NULL;
CREATE INDEX idx_comments_video_id ON comments(video_id) WHERE video_id IS NOT NULL;

Pros: Type-safe, enforced by database Cons: Must add column for each new type

Option 2: Generic FK (Not Recommended)

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    commentable_type VARCHAR(50),  -- 'Post' or 'Video'
    commentable_id INT,             -- ID in posts or videos
    content TEXT
);

CREATE INDEX idx_comments_polymorphic ON comments(commentable_type, commentable_id);

Pros: Flexible, no schema changes for new types Cons: No foreign key constraint, can't JOIN directly, error-prone

Option 3: Supertable (Inheritance)

CREATE TABLE commentables (
    id SERIAL PRIMARY KEY,
    type VARCHAR(50) NOT NULL
);

CREATE TABLE posts (
    id INT PRIMARY KEY REFERENCES commentables(id)
    title VARCHAR(255)
);

CREATE TABLE videos (
    id INT PRIMARY KEY REFERENCES commentables(id),
    url VARCHAR(500)
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    commentable_id INT REFERENCES commentables(id),
    content TEXT
);

Pros: Type-safe, can query all commentables Cons: Complex, requires joins for post/video data


Data Type Selection

Text/String Types

Type Max Size Use Case
CHAR(n) n chars (fixed) Fixed-length codes (e.g., country codes 'US')
VARCHAR(n) n chars Most text (emails, names, titles)
TEXT Unlimited Large text (blog posts, descriptions)

Recommendation: Use TEXT or VARCHAR(n) (for validation), avoid CHAR unless fixed-length.

Numeric Types

Type Range Storage Use Case
INT / INTEGER ±2 billion 4 bytes Most integers
BIGINT ±9 quintillion 8 bytes Large counts (user IDs at scale)
SERIAL Auto-increment INT 4 bytes Primary keys
BIGSERIAL Auto-increment BIGINT 8 bytes Primary keys at scale
DECIMAL(p,s) Exact Variable Money, exact calculations
NUMERIC(p,s) Same as DECIMAL Variable Money, exact calculations
REAL 6 decimal digits 4 bytes Approximate (avoid for money)
DOUBLE PRECISION 15 decimal digits 8 bytes Scientific calculations

Money: Always use DECIMAL(10,2) or store cents as BIGINT.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL(10,2) NOT NULL  -- $12,345,678.99
);

-- Or store cents as integer (avoids floating-point issues)
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price_cents BIGINT NOT NULL  -- 1234567899 = $12,345,678.99
);

Date/Time Types

Type Range Precision Use Case
DATE 4713 BC to 5874897 AD Day Birthdays, event dates
TIME 00:00:00 to 24:00:00 Microsecond Time of day
TIMESTAMP 4713 BC to 294276 AD Microsecond Most timestamps
TIMESTAMPTZ Same as TIMESTAMP Microsecond Recommended (timezone-aware)

Always use TIMESTAMPTZ for timestamps (stores UTC, displays in session timezone).

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    starts_at TIMESTAMPTZ NOT NULL,
    ends_at TIMESTAMPTZ
);

Boolean

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN DEFAULT TRUE,
    email_verified BOOLEAN DEFAULT FALSE
);

JSON/JSONB

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    metadata JSONB  -- Use JSONB (not JSON) for performance
);

-- Query JSONB
SELECT * FROM events WHERE metadata @> '{"status": "active"}';
SELECT * FROM events WHERE metadata->>'user_id' = '123';

-- Index JSONB
CREATE INDEX idx_events_metadata_status ON events USING GIN ((metadata->'status'));

JSONB vs JSON:

  • JSONB: Binary format, supports indexing, faster queries, recommended
  • JSON: Text format, preserves formatting, slower

Schema Design Checklist

Primary Keys:
[ ] Every table has a primary key
[ ] Primary key type chosen (SERIAL vs UUID vs ULID)

Foreign Keys:
[ ] Foreign keys defined with REFERENCES
[ ] ON DELETE behavior specified (CASCADE, SET NULL, RESTRICT)
[ ] Indexes on foreign key columns

Indexes:
[ ] Foreign keys indexed
[ ] Frequently queried columns indexed
[ ] Composite indexes for multi-column queries
[ ] UNIQUE constraints where appropriate

Timestamps:
[ ] created_at on all tables
[ ] updated_at on mutable tables
[ ] Trigger to auto-update updated_at

Constraints:
[ ] NOT NULL on required columns
[ ] CHECK constraints for validation
[ ] UNIQUE constraints for uniqueness

Data Types:
[ ] TIMESTAMPTZ (not TIMESTAMP) for dates
[ ] DECIMAL (not FLOAT) for money
[ ] TEXT or VARCHAR(n) for strings
[ ] JSONB (not JSON) for JSON data

Normalization:
[ ] At least 3NF (unless intentionally denormalized)
[ ] Denormalization justified with comments

Soft Deletes (if applicable):
[ ] deleted_at column
[ ] Index on deleted_at
[ ] UNIQUE constraints account for soft deletes

Common Pitfalls

❌ Using TIMESTAMP without timezone - Causes timezone bugs ✅ Always use TIMESTAMPTZ

❌ No index on foreign keys - Slow JOINs ✅ Create index on every foreign key column

❌ FLOAT/REAL for money - Floating-point errors ✅ Use DECIMAL(10,2) or store cents as BIGINT

❌ No primary key - Can't update/delete reliably ✅ Every table must have a primary key

❌ Generic polymorphic FKs - No referential integrity ✅ Use exclusive FKs or supertable pattern

❌ VARCHAR without length - Behaves like TEXT (misleading) ✅ Use TEXT or VARCHAR(n) with explicit length

❌ Not specifying ON DELETE - Orphaned rows ✅ Always specify ON DELETE CASCADE or SET NULL or RESTRICT


  • postgres-migrations.md - Evolving schemas safely
  • postgres-query-optimization.md - Index strategies for performance
  • orm-patterns.md - ORM-specific schema patterns
  • database-selection.md - When to use Postgres vs other databases

Last Updated: 2025-10-18 Format Version: 1.0 (Atomic)