Database Schema Design Principles

"Schema design debt compounds faster than code debt. Fix it now or pay 10x later."

Foundation Principles

Primary Keys

• Every table MUST have a primary key
• Prefer surrogate keys (auto-increment, UUIDv7) over composite natural keys
• UUIDv7 for distributed systems, auto-increment BIGINT for monoliths

Foreign Keys

• Use FK constraints unless specific reason not to (high-volume logging, sharded DBs)
• ON DELETE: RESTRICT (safest), CASCADE (use sparingly), SET NULL (breaks audit)

Data Types

• Choose smallest sufficient type (BIGINT vs INT = 4 bytes * rows)
• Money: DECIMAL (never FLOAT/DOUBLE)
• Dates without time: DATE not DATETIME
• Small sets: ENUM not VARCHAR

Constraints

• NOT NULL on required columns
• UNIQUE on natural keys
• CHECK for business rules
• DEFAULT where appropriate

Quality Checklist

Structural Integrity

• Every table has primary key
• Foreign key constraints defined
• Appropriate data types (smallest sufficient)
• NOT NULL, UNIQUE, CHECK constraints

Anti-Pattern Scan

• No EAV (entity-attribute-value) patterns
• No god tables (> 50 columns)
• No multi-valued fields (CSV in columns)
• No DATETIME for date-only data

Performance

• Indexes match query patterns
• Foreign keys indexed
• Composite index column order optimized

Decision Trees

"Should I denormalize this?"

Have evidence of query performance problem?
├─ NO → DON'T denormalize (premature optimization)
└─ YES → Tried indexes, query optimization, caching?
   ├─ NO → Try those first
   └─ YES → Read-heavy (> 100:1)?
      ├─ NO → Normalize, optimize queries
      └─ YES → Denormalize specific fields

"UUID or auto-increment?"

Distributed system (multiple write nodes)?
├─ YES → UUIDv7 (time-ordered, better than v4)
└─ NO → Exposed to users (issue-123)?
   ├─ YES → Auto-increment (better UX)
   └─ NO → Auto-increment (better performance)

"Soft or hard delete?"

GDPR "right to erasure" applies?
├─ YES → Hard delete or audit table
└─ NO → Need audit trail?
   ├─ YES → Audit table pattern (recommended)
   └─ NO → High deletion rate (> 20%)?
      ├─ YES → Hard delete
      └─ NO → Soft delete acceptable

References

Detailed patterns and examples:

• references/anti-patterns.md — EAV, god tables, multi-valued fields, red flags
• references/normalization.md — 1NF/2NF/3NF, when to denormalize, OLTP vs OLAP
• references/advanced-patterns.md — Soft delete, temporal data, JSON columns
• references/naming-conventions.md — Tables, columns, indexes, constraints
• references/performance-patterns.md — Indexing strategy, partitioning, data types

Remember

"The best schema is one you can understand in 6 months and modify with confidence."

Design schemas that:

1. Enforce integrity — Constraints, foreign keys, data types
2. Optimize for common patterns — Indexes, denormalization where proven
3. Enable evolution — Proper normalization, migration strategy
4. Prevent known anti-patterns — No EAV, god tables, multi-valued fields