database-design

1# Database Design
2 
3### When to Load
4 
5- **Trigger**: Schema design, migrations, query optimization, indexing strategies, data modeling, N+1 fixes
6- **Skip**: No database work involved in the current task
7 
8## Database Design Workflow
9 
10Copy this checklist and track progress:
11 
12```
13Database Design Progress:
14- [ ] Step 1: Identify entities and relationships
15- [ ] Step 2: Normalize schema (3NF minimum)
16- [ ] Step 3: Evaluate denormalization needs
17- [ ] Step 4: Design indexes for query patterns
18- [ ] Step 5: Write and optimize critical queries
19- [ ] Step 6: Plan migration strategy
20- [ ] Step 7: Configure connection pooling
21- [ ] Step 8: Validate against anti-patterns checklist
22```
23 
24## Schema Design Principles
25 
26### Normalization Forms
27 
28```
291NF: Atomic values, no repeating groups
302NF: 1NF + no partial dependencies (all non-key columns depend on full PK)
313NF: 2NF + no transitive dependencies (non-key columns don't depend on other non-key columns)
32```
33 
34```sql
35-- WRONG: Unnormalized
36CREATE TABLE orders (
37  id SERIAL PRIMARY KEY,
38  customer_name TEXT,
39  customer_email TEXT,        -- duplicated across orders
40  product1_name TEXT,         -- repeating groups
41  product1_qty INT,
42  product2_name TEXT,
43  product2_qty INT
44);
45 
46-- CORRECT: Normalized to 3NF
47CREATE TABLE customers (
48  id SERIAL PRIMARY KEY,
49  name TEXT NOT NULL,
50  email TEXT UNIQUE NOT NULL
51);
52 
53CREATE TABLE orders (
54  id SERIAL PRIMARY KEY,
55  customer_id INT REFERENCES customers(id),
56  created_at TIMESTAMPTZ DEFAULT NOW()
57);
58…