Data Modeling
Choose the storage model and schema that make your access patterns fast and your future changes cheap.
Why
The data model is the part of the architecture hardest to change later — every query, every index, every migration flows from it. A well-modeled schema makes the common queries index-served and the rare ones tolerable; a poorly modeled one makes every query a full scan and every new feature a migration nightmare. The choice between relational, document, wide-column, and graph is driven by your access patterns (reads vs writes, joins vs aggregations), not by fashion. Getting this right in design saves months of rework; getting it wrong means you're stuck with it because migrating a live datastore is the most expensive refactor there is.
When
Early in system design, before the first query is written. Takes 1-2 days. Revisit only if access patterns change fundamentally.
How
1. List your access patterns (the queries, not the entities): - "fetch user by id", "list orders by user sorted by date", "aggregate spend by month" 2. For each, note: read-heavy or write-heavy? needs joins? needs transactions? 3. Pick the store: - Joins + transactions → relational (Postgres) - Flexible nested docs, read-heavy → document (Mongo) - Time-series, wide rows → wide-column (Cassandra) - Relationships-first → graph (Neo4j) 4. Design the schema to serve the top 5 access patterns with an index 5. Denormalize only where a join is too slow and the data is read-heavy 6. Plan the migrations: every table needs a created_at + a migration path
Example
Access patterns: 1. fetch user by id (read-heavy, by-key) → Postgres users table, PK index 2. list orders by user, sorted by date (read-heavy, range) → orders(user_id, created_at) composite index 3. aggregate spend by month (read-heavy, scan) → pre-materialized monthly_spend table, refreshed nightly Schema: users(id PK, email UNIQUE, created_at) orders(id PK, user_id FK, amount, created_at) — index (user_id, created_at DESC) monthly_spend(month, user_id, total) — PK (month, user_id) No joins at read time (denormalized monthly_spend); migrations via expand-contract.
Common Problems
- ⚠Modeling entities, not access patterns — you get a normalized schema that's correct in theory and slow in practice because every read joins 5 tables.
Best Practices
- ✓Write the 5 most frequent queries before finalizing the schema — if any can't be index-served, redesign.
- ✓Prefer expand-contract migrations over in-place alters — they're zero-downtime and reversible, which matters on a live DB.