Database Schema Design Principles
Core Principles
- Normalize first - Eliminate data duplication
- Define relationships clearly (1:1, 1:N, N:N)
- Choose data types carefully (varchar vs text, int vs bigint)
- Index strategically - On columns used in WHERE and JOIN
- Plan for migrations - Schema will evolve
Common Patterns
Users and Authentication
- Separate users and profiles tables
- Store hashed passwords only
- Use UUID for public identifiers
Multi-Tenant SaaS
- Organization table as tenant root
- Foreign key to organization on all tenant data
- Row-level security for data isolation
Soft Deletes
- Add deleted_at column instead of deleting rows
- Filter by deleted_at IS NULL in queries
- Periodically clean up old soft-deleted records
Indexing Strategy
| Index When | Do Not Index When |
|---|---|
| Column in WHERE clause | Rarely queried columns |
| Foreign key columns | Small tables (under 1000 rows) |
| Columns used in ORDER BY | Columns with low cardinality |
| Unique constraints needed | Write-heavy columns |
Migration Best Practices
- Always version your migrations
- Make migrations reversible
- Test migrations on a copy of production data
- Never modify a deployed migration
Need database design help? I design schemas for scalable web applications.