NOTE
Data Modeling for System Design
Choosing data models from invariants and access patterns: relational, document, key-value, search/index, event log, graph, denormalization, and derived views.
This is a historical learning note and may contain outdated or incomplete understanding.
1. Model from Access Patterns and Invariants
Do not choose a database category first and force the domain into it.
Define:
- entities and identity;
- relationships;
- uniqueness/constraints;
- transaction boundaries;
- read/write patterns;
- retention and history requirements.
2. Relational Model
Relational databases are strong when constraints, joins, transactions, and flexible secondary queries matter.
Normalization reduces update anomalies; denormalization can improve read paths at the cost of synchronization.
3. Key-Value / Document
Useful when the primary access path is by key/document and aggregate boundaries are clear.
They can scale simple access patterns well, but secondary/global queries may need explicit indexes or another system.
4. Search Index
Search engines are derived indexes optimized for text/relevance/filtering/aggregation. They should usually not be the sole source of truth for transactional invariants.
5. Event Log
An append-only log records changes/events and supports replay/stream processing. It is complementary to materialized state, not automatically a replacement for it.
6. Graph
Graph models are valuable when traversing relationships is the dominant operation and path queries are difficult to express efficiently in other stores.
7. Multiple Models
Real systems often use polyglot persistence:
transactional DB → source of truth
cache → latency/load reduction
search index → query projection
event log → integration/replayEvery derived copy needs a synchronization and rebuild strategy.