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
Anti-Pattern Scan
Performance
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 flagsreferences/normalization.md — 1NF/2NF/3NF, when to denormalize, OLTP vs OLAPreferences/advanced-patterns.md — Soft delete, temporal data, JSON columnsreferences/naming-conventions.md — Tables, columns, indexes, constraintsreferences/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:
- Enforce integrity — Constraints, foreign keys, data types
- Optimize for common patterns — Indexes, denormalization where proven
- Enable evolution — Proper normalization, migration strategy
- Prevent known anti-patterns — No EAV, god tables, multi-valued fields
1---2name: schema-design3description: Universal database schema design principles: normalization strategy, data types, primary keys, constraints, anti-patterns, and red flags. Apply when designing schemas, reviewing database architecture, or refactoring data models.4---5
6# Database Schema Design Principles
7
8**"Schema design debt compounds faster than code debt. Fix it now or pay 10x later."**
9
10## Foundation Principles
11
12### Primary Keys
13- Every table MUST have a primary key
14- Prefer surrogate keys (auto-increment, UUIDv7) over composite natural keys
15- UUIDv7 for distributed systems, auto-increment BIGINT for monoliths
16
17### Foreign Keys
18- Use FK constraints unless specific reason not to (high-volume logging, sharded DBs)
19- ON DELETE: RESTRICT (safest), CASCADE (use sparingly), SET NULL (breaks audit)
20
21### Data Types
22- Choose smallest sufficient type (BIGINT vs INT = 4 bytes * rows)
23- Money: DECIMAL (never FLOAT/DOUBLE)
24- Dates without time: DATE not DATETIME
25- Small sets: ENUM not VARCHAR
26
27### Constraints
28- NOT NULL on required columns
29- UNIQUE on natural keys
30- CHECK for business rules
31- DEFAULT where appropriate
32
33## Quality Checklist
34
35### Structural Integrity
36- [ ] Every table has primary key
37- [ ] Foreign key constraints defined
38- [ ] Appropriate data types (smallest sufficient)
39- [ ] NOT NULL, UNIQUE, CHECK constraints
40
41### Anti-Pattern Scan
42- [ ] No EAV (entity-attribute-value) patterns
43- [ ] No god tables (> 50 columns)
44- [ ] No multi-valued fields (CSV in columns)
45- [ ] No DATETIME for date-only data
46
47### Performance
48- [ ] Indexes match query patterns
49- [ ] Foreign keys indexed
50- [ ] Composite index column order optimized
51
52## Decision Trees
53
54### "Should I denormalize this?"
55```
56Have evidence of query performance problem?
57├─ NO → DON'T denormalize (premature optimization)
58└─ YES → Tried indexes, query optimization, caching?
59 ├─ NO → Try those first
60 └─ YES → Read-heavy (> 100:1)?
61 ├─ NO → Normalize, optimize queries
62 └─ YES → Denormalize specific fields
63```
64
65### "UUID or auto-increment?"
66```
67Distributed system (multiple write nodes)?
68├─ YES → UUIDv7 (time-ordered, better than v4)
69└─ NO → Exposed to users (issue-123)?
70 ├─ YES → Auto-increment (better UX)
71 └─ NO → Auto-increment (better performance)
72```
73
74### "Soft or hard delete?"
75```
76GDPR "right to erasure" applies?
77├─ YES → Hard delete or audit table
78└─ NO → Need audit trail?
79 ├─ YES → Audit table pattern (recommended)
80 └─ NO → High deletion rate (> 20%)?
81 ├─ YES → Hard delete
82 └─ NO → Soft delete acceptable
83```
84
85## References
86
87Detailed patterns and examples:
88- `references/anti-patterns.md` — EAV, god tables, multi-valued fields, red flags
89- `references/normalization.md` — 1NF/2NF/3NF, when to denormalize, OLTP vs OLAP
90- `references/advanced-patterns.md` — Soft delete, temporal data, JSON columns
91- `references/naming-conventions.md` — Tables, columns, indexes, constraints
92- `references/performance-patterns.md` — Indexing strategy, partitioning, data types
93
94## Remember
95
96**"The best schema is one you can understand in 6 months and modify with confidence."**
97
98Design schemas that:
991. **Enforce integrity** — Constraints, foreign keys, data types
1002. **Optimize for common patterns** — Indexes, denormalization where proven
1013. **Enable evolution** — Proper normalization, migration strategy
1024. **Prevent known anti-patterns** — No EAV, god tables, multi-valued fields
majiayu000