database-design

# Database Design Design efficient, scalable database schemas. ## Normalization | Normal Form | Rule | Violation Example | |-------------|------|-------------------| | 1NF | Atomic columns, no repeating groups | `phone_numbers: "555-0100,555-0200"` | | 2NF | 1NF + all non-key cols depend on full PK | Orders table with product details | | 3NF | 2NF + no transitive dependencies | Employee with department_name (dept info in dept table) | **When to denormalize**: Read-heavy workloads, reporting, caching layers. ## Indexing Strategy ### Index Types | Index | Use Case | Tradeoff | |-------|----------|----------| | B-Tree | General purpose, equality + range | Default, usually best | | Hash | Equality only | Fast lookups, no sorting | | GIN | Array/JSON | Slightly slower writes | | BRIN | Huge sorted tables | Very small size | ### Rules - Index columns used in WHERE, JOIN, ORDER BY - Index foreign keys - Covering indexes for frequent queries - Don't over-index (slows writes, uses space) - Drop unused indexes (use pg_stat_user_indexes) ## Migrations - One file per change, sequential naming - Always reversible (up/down pair) - Test on staging before production - Run in transactions where possible - Avoid locking tables on large tables (use pt-online-schema-change) ## Query Optimization - EXPLAIN ANALYZE every slow query - Look for: Seq scans, nested loops, temp files - Common fixes: missing index, bad join order, OR conditions - N+1 problem: batch find / includes / joins - Limit offsets for pagination (use cursor-based)