Design efficient, maintainable database schemas with safe migration strategies.
Key Principles
- Start from requirements: identify entities, attributes, and relationships first
- Normalize for data integrity, denormalize selectively for read performance
- Design indexes based on actual query patterns, not guesses
- Migrations must be reversible and safe for zero-downtime deployments
- Choose the right ORM — Prisma for type safety, Drizzle for SQL-close, TypeORM for enterprise
Quick Start Checklist
- Identify entities and relationships from requirements
- Design normalized schema (3NF minimum)
- Add indexes for known query patterns
- Plan migration strategy (up + down)
- Choose ORM/query builder based on project needs
- Set up seed data for development
References
| Reference | Description |
|---|---|
| schema-patterns.md | Normalization, relationships, naming conventions |
| index-design.md | Index types, composite indexes, partial indexes |
| migration-strategies.md | Safe migrations, zero-downtime, rollback |
| query-optimization.md | EXPLAIN, N+1 queries, join strategies |
| postgresql.md | PostgreSQL-specific features and patterns |
| prisma-patterns.md | Prisma schema, relations, transactions |
| tdd-patterns.md | Test-first patterns for migrations, constraints, factories |
| review-checklist.md | Database design review checklist (schema, indexes, integrity) |