database-schema-and-migrationslisted

# Database schema & migrations Data is the part of the system you cannot roll back with `git revert`. Design for the write path you'll regret in a year, not just the query you need today. ## 1. Normalize by default; denormalize on evidence Start normalized (3NF: no repeated groups, every non-key column depends on the whole key, nothing but the key). Denormalize only when you have a specific, measured reason: - A read path is proven hot (via EXPLAIN or slow-query logs, §3) and the join cost is the bottleneck. - The duplicated data is effectively immutable, or you've accepted an explicit sync strategy (trigger, event, batch job) for keeping copies consistent. - Never denormalize speculatively "for performance" before measuring — that's premature optimization with a schema-migration cost attached. ## 2. Indexing strategy - Index columns used in `WHERE`, `JOIN ON`, and `ORDER BY` — not every column "just in case." Every index costs write throughput and disk. - **Composite index column order matters**: put the equality-filtered column first, range-filtered/sorted columns after. An index on `(a, b)` serves `WHERE a = ? AND b = ?` and `WHERE a = ? ORDER BY b`, but not efficiently for `WHERE b = ?` alone. - **Covering indexes** (include all columns a query needs) let the planner answer straight from the index without touching the table — worth it for hot, narrow read paths. - **When NOT to index**: low-cardinality columns (booleans, small enums) rarely hel