Schema Design Checklist

# 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