database-design-patterns

Database Design Patterns

Relational database schema design expert. Covers normalization decisions, index selection, migration safety, and connection pooling — the structural foundations that determine whether a database performs well at scale or becomes a maintenance burden.

When to Use

Use for:

• Designing new schemas or refactoring existing ones
• Deciding whether to normalize or denormalize for a specific query pattern
• Choosing index types (B-tree, GIN, GiST, hash, partial, covering)
• Planning migrations that must not break running production systems
• Implementing soft deletes, polymorphic associations, or composite keys
• Configuring PgBouncer or Prisma connection pools

NOT for:

• Running EXPLAIN ANALYZE or reading query plans → use postgresql-optimization
• Document model design (MongoDB, DynamoDB) → use a NoSQL skill
• Database provisioning, replicas, or infrastructure → use a cloud/infra skill
• ORM-specific code generation → use the relevant ORM skill

---

Normalize vs. Denormalize Decision Tree

flowchart TD
    A[New data or query performance problem?] --> B{New data design?}
    B -->|Yes| C[Start normalized: 3NF]
    B -->|No — query too slow| D{Measured with EXPLAIN?}
    D -->|No| E[Measure first. Never guess.]
    D -->|Yes — proven join bottleneck| F{Read-heavy or write-heavy?}
    F -->|Read-heavy, joins are the bottleneck| G[Controlled denormalization:\nmaterialized view or cached column]
    F -->|Write-heavy or balanced| H[Keep normalized.\nOptimize query or add index first.]
    C --> I{Any repeated groups in a row?}
    I -->|Yes| J[1NF: Move to child table]
    I -->|No| K{Non-key cols depend on part of PK?}
    K -->|Yes| L[2NF: Extract to separate table]
    K -->|No| M{Transitive dependencies?}
    M -->|Yes| N[3NF: Extract lookup table]
    M -->|No| O[Schema is 3NF — ship it]

The rule: Start at 3NF. Denormalize only after measuring, and only the specific join that is provably too slow. Never denormalize speculatively.