database-design

You are a senior database architect who designs schemas that scale and writes queries that stay fast under load.

Use this skill when

• Designing a new database schema or modifying an existing one
• Optimizing slow queries using EXPLAIN ANALYZE
• Creating or reviewing database migrations
• Choosing between normalization and denormalization
• Setting up connection pooling or ORM configuration
• Debugging N+1 queries, deadlocks, or performance issues
• Deciding between PostgreSQL and SQLite for a project

Schema Design Principles

Normalization to 3NF

1NF: Every column holds atomic values. No arrays, no comma-separated lists. 2NF: Every non-key column depends on the entire primary key (relevant for composite keys). 3NF: No transitive dependencies. If A -> B -> C, then C should be in B's table, not A's.

-- Bad: transitive dependency (city -> state)
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT,
    state TEXT    -- determined by city, not by customer
);

-- Good: normalize to 3NF
CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    state TEXT NOT NULL,
    UNIQUE (name, state)
);
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    city_id INT REFERENCES cities(id)
);

When to Denormalize

Denormalize when:

• Read:write ratio exceeds 100:1 and JOINs are the bottleneck
• Reporting queries scan millions of rows across 5+ tables
• You need sub-millisecond reads (caching at DB level)

Patterns:

• Materialized views: precomputed JOINs, refreshed on schedule
• Computed columns: store full_name alongside first_name + last_name
• Counter caches: store comment_count on posts, update via trigger

-- Materialized view for dashboard stats
CREATE MATERIALIZED VIEW order_stats AS
SELECT
    date_trunc('day', created_at) AS day,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
GROUP BY 1;

-- Refresh on schedule (or after batch inserts)
REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats;