# Database Schema Design Checklist

Complete checklist for designing and reviewing database schemas.

---

## Pre-Design

- [ ] **Requirements Gathered**: Understand data entities and relationships
- [ ] **Access Patterns Identified**: Know how data will be queried
- [ ] **SQL vs NoSQL Decision**: Chosen appropriate database type
- [ ] **Scale Estimate**: Expected data volume and growth rate
- [ ] **Read/Write Ratio**: Understand if read-heavy or write-heavy

---

## Normalization (SQL)

- [ ] **1NF**: Atomic values, no repeating groups
- [ ] **2NF**: No partial dependencies on composite keys
- [ ] **3NF**: No transitive dependencies
- [ ] **Denormalization Justified**: If denormalized, reason documented

---

## Table Design

### Primary Keys

- [ ] **Primary Key Defined**: Every table has primary key
- [ ] **Key Type Chosen**: INT auto-increment or UUID
- [ ] **Meaningful Keys Avoided**: Not using email/username as PK

### Data Types

- [ ] **Appropriate Types**: Correct data types for each column
- [ ] **String Sizes**: VARCHAR sized appropriately
- [ ] **Numeric Precision**: DECIMAL for money, INT for counts
- [ ] **Dates in UTC**: TIMESTAMP for datetime columns

### Constraints

- [ ] **NOT NULL**: Required columns marked NOT NULL
- [ ] **Unique Constraints**: Unique columns (email, username)
- [ ] **Check Constraints**: Validation rules (price >= 0)
- [ ] **Default Values**: Sensible defaults where appropriate

---

## Relationships

### Foreign Keys

- [ ] **Foreign Keys Defined**: All relationships have FK constraints
- [ ] **ON DELETE Strategy**: CASCADE, RESTRICT, SET NULL chosen
- [ ] **ON UPDATE Strategy**: Usually CASCADE
- [ ] **Indexes on Foreign Keys**: All FKs are indexed

### Relationship Types

- [ ] **One-to-Many**: Modeled correctly
- [ ] **Many-to-Many**: Junction table created
- [ ] **Self-Referencing**: Parent-child relationships handled
- [ ] **Polymorphic**: Strategy chosen (separate FKs or type+id)

---

## Indexing

### Index Strategy

- [ ] **Primary Key Indexed**: Automatic, verify
- [ ] **Foreign Keys Indexed**: All FKs have indexes
- [ ] **WHERE Columns**: Columns in WHERE clauses indexed
- [ ] **ORDER BY Columns**: Sort columns indexed
- [ ] **Composite Indexes**: Multi-column queries optimized
- [ ] **Column Order**: Most selective column first

### Index Limits

- [ ] **Not Over-Indexed**: Only necessary indexes
- [ ] **Index Maintenance**: Aware of write impact

---

## Performance

- [ ] **Joins Optimized**: N+1 queries avoided
- [ ] **SELECT * Avoided**: Only fetch needed columns
- [ ] **Pagination**: LIMIT/OFFSET or cursor-based
- [ ] **Aggregations**: Pre-calculated for expensive queries

---

## Migrations

- [ ] **Backward Compatible**: New columns nullable initially
- [ ] **Up and Down**: Rollback scripts provided
- [ ] **Data Migrations Separate**: Schema vs data separated
- [ ] **Tested on Staging**: Migrations tested

---

## Security

- [ ] **Least Privilege**: Minimal database permissions
- [ ] **Separate Accounts**: Read-only vs read-write
- [ ] **Sensitive Data**: Passwords hashed, PII encrypted
- [ ] **Parameterized Queries**: SQL injection prevented

---

## Documentation

- [ ] **ERD Created**: Entity-relationship diagram
- [ ] **Schema Documented**: Column descriptions
- [ ] **Indexes Documented**: Why each index exists
- [ ] **Migration History**: Changelog of changes