Database Design

Decision Tree

Data storage needed → What type?
    ├─ Structured, relational → PostgreSQL (server) or SQLite (embedded/mobile)
    ├─ Document-oriented, flexible schema → MongoDB or PostgreSQL JSONB
    ├─ Key-value / caching → Redis
    ├─ Full-text search → PostgreSQL tsvector or Elasticsearch
    └─ Time-series → TimescaleDB (PostgreSQL extension)

Schema Design Checklist

• • Every table has a primary key (prefer UUID or BIGSERIAL)
• • Foreign keys have ON DELETE behavior defined (CASCADE, SET NULL, RESTRICT)
• • Timestamps: created_at and updated_at on every table
• • Columns NOT NULL by default, nullable only when intentional
• • Text fields have reasonable length constraints
• • Sensitive data columns identified for encryption

Indexing Strategy

Migration Rules

1. •Always reversible - Every up has a down
2. •No data loss - Add columns before removing old ones
3. •No locks on large tables - Use CREATE INDEX CONCURRENTLY
4. •One concern per migration - Don't mix schema + data changes
5. •Test with production-size data - Migrations that work on 100 rows may lock on 10M

Anti-Patterns

For detailed patterns see:

• •PostgreSQL patterns
• •Schema design patterns