databaselisted

# Database Patterns Domain expertise for database design and optimization. Use alongside `devflow:review-methodology` for complete database reviews. ## Iron Law > **EVERY QUERY MUST HAVE AN EXECUTION PLAN** > > Never deploy a query without understanding its execution plan. Every WHERE clause needs > an index analysis. Every JOIN needs cardinality consideration. "It works in dev" is not > validation. Production data volumes will expose every missing index and inefficient join. ## Database Categories ### 1. Schema Design Issues | Issue | Problem | Solution | |-------|---------|----------| | Missing Foreign Keys | No referential integrity, orphaned records | Add FK with ON DELETE action | | Denormalization | Unnecessary duplication, update anomalies | Normalize unless performance requires | | Poor Data Types | VARCHAR for everything, lost precision | Use appropriate types (DECIMAL, BOOLEAN, TIMESTAMP) | | Missing Constraints | No data validation at DB level | Add NOT NULL, CHECK, UNIQUE constraints | **Example - Missing Constraints:** ```sql -- VIOLATION CREATE TABLE products (id SERIAL, name VARCHAR(100), price DECIMAL); -- CORRECT CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL CHECK (LENGTH(TRIM(name)) > 0), price DECIMAL(10, 2) NOT NULL CHECK (price >= 0) ); ``` ### 2. Query Optimization Issues | Issue | Problem | Solution | |-------|---------|----------| | N+1 Queries | Query per iteration, O(n) round trips | JOIN or batch with IN/A