wehub-resource-sync
3.3 KiB
3.3 KiB
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