beginnerintermediateadvanced

System Design · Data Modeling

Data Modeling

Designing around how data is actually used, not just what entities exist

Access patterns firstthe dominant query decides the schema
ID strategyauto-increment, UUID, or ULID — each a trade-off
Soft deletestays addressable for history
Status + transitionsenforced, not just a free-text field

1.Design around access patterns, not just object models

Modeling data by asking "what objects exist" (a User, an Order, a Product) is only half the job. The other half — often the more important half at scale — is asking how that data will actually be queried, updated and how long it lives, because those access patterns decide the actual schema, not the object model alone.

Key features

Object model answers: what entities exist and how do they relate
Access pattern answers: how will this data be read and written, and how often, in production
Invariants: rules that must always hold (an order total must match its line items) — the model must make violating them hard
Lifecycle: how long data lives, whether it is ever deleted, archived or must be retained
Two systems with an identical object model can need very different schemas if their access patterns differ

Object model vs access-pattern-driven model

Object model vs access-pattern-driven model
Question	Object-model-only answer	Access-pattern-aware answer
"What is an Order?"	Has an id, items, total, status	Same, plus: how is it looked up — by user, by date range, by status?
Where do line items live?	A related table/collection	Embedded if always read with the order; separate if queried independently
How long does it live?	Not asked	Forever? Archived after a year? Deleted on request?

Same object model, different access pattern

System A: a blog

Posts read by permalink, individually
Simple table keyed by id is enough
likeCount can be a plain counter

System B: a social feed

"All posts from people I follow", high volume
Needs a composite index on (authorId, createdAt)
likeCount needs a different storage strategy

Command

text

object model:    entities + relationships
access pattern:  how each entity is actually read/written, how often, how long it lives

An object model says what exists; an access pattern says how it is actually read and written in production — the schema that survives is designed around the second, using the first as one input, not the other way around.

2.Entities, relationships and choosing an ID strategy

Modeling data starts with naming the entities (User, Order, Product), how they relate (an Order belongs to a User), and what constraints and indexes each needs. Part of that is choosing how each entity is identified — an auto-increment integer, a UUID, a ULID, or a domain-specific key — a choice with real, different consequences.

Key features

Auto-increment: simple, compact, naturally sortable by creation order — but requires one authority (usually one database) to hand them out
UUID: globally unique, generatable anywhere with no coordination — but large, not sortable by creation time, can fragment an index
ULID: like a UUID, but time-ordered — sortable by creation time while still being generatable without coordination
Domain-specific ID (e.g. an order number visible to a customer): chosen for human readability, not just uniqueness
Constraints (foreign keys, uniqueness) and indexes are what make relationships and lookups actually enforced and fast, not just documented

Choosing an ID strategy

Choosing an ID strategy
Strategy	Coordination needed?	Sortable by creation?	Good fit when
Auto-increment	Yes — one database authority	Yes	Single-writer system, order matters, size matters
UUID (v4, random)	No	No	Multiple writers generating IDs independently, order does not matter
ULID	No	Yes	Multiple writers, but still want time-ordered IDs
Domain-specific (e.g. INV-2026-00042)	Depends	Depends	ID must be human-readable or externally meaningful

Four ID strategies

Auto-increment

1 authority, sortable

UUID

no coordination, not sortable

ULID

no coordination, sortable

Domain-specific

human-readable

Command

text

auto-increment: needs one authority, compact, sortable
UUID:           no coordination, not sortable, larger
ULID:           no coordination, sortable, larger than an int

Auto-increment is simple and sortable but needs one authority; UUIDs need no coordination but are not sortable and can fragment an index; ULIDs get both — coordination-free and time-sortable.

3.Soft delete, audit history, temporal data, status and versioning

Five recurring patterns handle the fact that "delete" and "change" are rarely as simple as removing or overwriting a row: soft delete (mark instead of remove), audit history (record every change), temporal data (model validity over time), status modeling (an explicit state field with defined transitions), and versioning (keep old versions addressable).

Key features

Soft delete: mark a row as deleted (a `deletedAt` timestamp or `isDeleted` flag) instead of removing it
Audit history: a separate log of every change, who made it and when — for compliance or debugging
Temporal data: a record has a validity range (`validFrom`/`validTo`), useful for prices, contracts, assignments
Status modeling: an explicit field with a known, finite set of states and defined transitions between them
Versioning: keep prior versions of a record addressable, rather than overwriting them in place

Five lifecycle patterns and what each solves

Five lifecycle patterns and what each solves
Pattern	Problem it solves	Typical shape
Soft delete	Need to "undelete" or preserve references	`deletedAt: timestamp \| null`
Audit history	Need to know who changed what, when	Append-only log table, one row per change
Temporal data	A fact is only true for a date range	`validFrom`, `validTo` on the row
Status modeling	Need an explicit, enforceable lifecycle	`status: enum` + a transition table
Versioning	Need old versions still readable	A version number or a separate versions table

Order status: enforced transitions

paymentsucceedscancelledfulfilledcancelled

Pending

start

Paid

Shipped

end

Cancelled

end

Command

text

soft delete:     deletedAt timestamp, filtered out of normal queries
audit history:   append-only log, one row per change
temporal data:   validFrom / validTo on the row
status:          enum + an enforced transition table
versioning:       version number, or separate immutable version rows

Soft delete keeps a row addressable, audit history records every change, temporal data models validity over time, status modeling needs enforced transitions (not just a free-text field), and versioning keeps old versions readable.

Spotted a mistake on this page? Tell us.

Read Sheet