Provides essential database design patterns for relational databases based on SQL standards, PostgreSQL documentation, and industry best practices.
Essential database design patterns for relational databases. Based on SQL standards, PostgreSQL documentation, and industry best practices.
Sources: PostgreSQL Documentation, ISO/IEC 9075 (SQL Standard), "Use The Index, Luke" by Markus Winand, Database Design Best Practices
1NF: Atomic values, no repeating groups, each row unique 2NF: 1NF + no partial dependencies (all non-key attributes depend on entire primary key) 3NF: 2NF + no transitive dependencies (non-key attributes depend only on primary key) BCNF: 3NF + every determinant is a candidate key
When to denormalize: Read-heavy workloads, aggregations, reporting tables (materialized views preferred)
1:1 - FK in either table with UNIQUE constraint 1:N - FK in child table pointing to parent PK M:N - Junction table with composite PK (parent_id, child_id)
-- 1:N with cascade delete
CREATE TABLE posts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_posts_user_id ON posts(user_id);
B-tree (default): Equality and range queries (>, <, BETWEEN, ORDER BY) Hash: Equality only, faster for exact matches GIN: Arrays, JSONB, full-text search GiST: Geometric data, range types, nearest-neighbor searches BRIN: Large tables with natural ordering (time-series, append-only) Partial: Index subset matching WHERE condition Covering: INCLUDE columns to avoid table lookups
Composite index rule: Leftmost columns used first (WHERE a=? AND b=? uses index on (a,b); WHERE b=? does not)
-- Partial index for active records only
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;
See references/indexing-strategies.md for detailed index types, column ordering rules, maintenance strategies, and common mistakes.
EXPLAIN ANALYZE: Shows actual execution plan with timing
N+1 Prevention: Use JOINs, batch loading (DataLoader), or eager loading (Supabase client with select/join)
Pagination: Cursor-based for large datasets (WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT n)
See references/postgresql-performance.md for EXPLAIN interpretation, VACUUM strategies, connection pooling, partitioning, materialized views, and common performance pitfalls.
Atomicity: All operations succeed or all fail Consistency: Database moves from valid state to valid state Isolation: Concurrent transactions don't interfere Durability: Committed changes persist after crash
Isolation Levels (PostgreSQL default: Read Committed):
Safe operations:
Unsafe operations:
Large table batching: Process in chunks with cursor pagination to avoid long locks
See references/migration-patterns.md for zero-downtime techniques, changing column types safely, backfilling strategies, rollback patterns, and blue-green deployment with migrations.
Code templates for this domain (in templates/):
migration.sql.template — Database migration script with rollback