database-designlisted

# Database Design ### When to Load - **Trigger**: Schema design, migrations, query optimization, indexing strategies, data modeling, N+1 fixes - **Skip**: No database work involved in the current task ## Database Design Workflow Copy this checklist and track progress: ``` Database Design Progress: - [ ] Step 1: Identify entities and relationships - [ ] Step 2: Normalize schema (3NF minimum) - [ ] Step 3: Evaluate denormalization needs - [ ] Step 4: Design indexes for query patterns - [ ] Step 5: Write and optimize critical queries - [ ] Step 6: Plan migration strategy - [ ] Step 7: Configure connection pooling - [ ] Step 8: Validate against anti-patterns checklist ``` ## Schema Design Principles ### Normalization Forms ``` 1NF: Atomic values, no repeating groups 2NF: 1NF + no partial dependencies (all non-key columns depend on full PK) 3NF: 2NF + no transitive dependencies (non-key columns don't depend on other non-key columns) ``` ```sql -- WRONG: Unnormalized CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_name TEXT, customer_email TEXT, -- duplicated across orders product1_name TEXT, -- repeating groups product1_qty INT, product2_name TEXT, product2_qty INT ); -- CORRECT: Normalized to 3NF CREATE TABLE customers ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL ); CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), created_at TIMESTAMPTZ DEFAULT NOW() ); CREA