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)  -- 'alice@example.com, alice@work.com'
);

-- ✅ 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

---

Related Skills

• 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)