beginnerintermediateadvanced

System Design · Section 16

Data Modeling

Level: intermediate
Read: 18 min
Concepts: 3

Modeling data well means starting from how it will actually be read and written in production — not only from the object model — then choosing an ID strategy (auto-increment, UUID or ULID) with its real coordination and sortability trade-offs, and handling the fact that "delete" and "change" are rarely as simple as they first look: soft delete, audit history, temporal data, enforced status transitions, and versioning.

This section

Data Modeling in System Design Interviews w/ Meta Staff EngineerHello Interview

What is true here

The dominant access pattern, not the object model alone, decides the schema that actually survives production.
Auto-increment IDs need one authority but sort naturally by creation; UUIDs need no coordination but do not sort; ULIDs get both.
Soft delete keeps a row addressable for anything that still references it, instead of breaking on a hard delete.
A status field is only useful if its transitions are enforced, not just its possible values documented.

What you will be able to do

Design a schema around a workload's dominant access pattern rather than its object model alone
Choose an ID strategy based on whether coordination-free generation or creation-time sortability matters more
Apply soft delete, audit history, temporal data, status modeling or versioning to the entity that actually needs it

Modeling for real usage

Starting from access patterns, then choosing how each entity is identified.

Design around access patterns, not just object models

coreintermediate

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.

Think of it as

An object model is like drawing a family tree — it shows who relates to whom, cleanly. But designing a library's shelving system from the family tree alone would be a mistake: what matters for shelving is how people actually search (by author? by subject?), not the family relationships between authors. A data model needs the same shift — from "what things exist and how do they relate" to "how will this actually be looked up."

text

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

What we're doing: Show two systems with an identical object model needing different schemas because their access patterns differ.

same-model-different-access.txttext

Both systems have the same object model:
  Post { id, authorId, body, createdAt, likeCount }

System A (a blog): posts are read by permalink,
individually, rarely by author. → a simple table/
collection keyed by id is enough; likeCount can be
a plain counter column.

System B (a social feed): posts must be read as
"all posts from people I follow, in reverse
chronological order," at high volume. → needs a
fan-out or feed-generation strategy, a composite
index on (authorId, createdAt), and likeCount under
heavy concurrent increment needs a different
storage strategy than a plain counter column.

Same object model, very different actual schema —
because the access pattern, not the object model,
decided it.

6: System A never needs to answer "all posts by author X, ordered by time" at volume — its schema can stay simple.
11: System B's dominant query is exactly that — its schema has to be built around answering it fast, not around the object model alone.

Why this works: The object model was identical in both systems — the schema that actually works is decided by how the data gets read and written in production, which the object model alone never tells you.

Designing a schema purely from the object model, then discovering the real access pattern in production

Wrong

text

// designed from the object model alone:
Post { id, authorId, body, createdAt, likeCount }
// no thought given to how "my feed" is queried

Better

text

// designed from the dominant access pattern first:
// "show me posts from people I follow, newest
// first, at high read volume" →
// index (authorId, createdAt) + a feed/fan-out
// strategy decided up front

What you see: A schema that looked complete in review turns out to need a full redesign once the real production query pattern — usually the highest-volume one — shows up, because nothing about the object model predicted it.

Why: An object model describes structure; it says nothing about frequency or shape of access. The schema that survives production is the one designed around the actual dominant queries, with the object model as only one input to that design.

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

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?

Together

text

Object model alone:
  Order { id, userId, items[], total, status }
  → looks complete, but doesn't say how it's queried.

Access-pattern-aware:
  - "Show my last 20 orders" → needs an index on
    (userId, createdAt DESC)
  - "Show all pending orders older than 1 hour"
    → needs an index on (status, createdAt)
  - Orders older than 2 years are read maybe once a
    year → candidate for a cheaper archive store

Remember: 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.

Entities, relationships and choosing an ID strategy

coreintermediate

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.

Think of it as

Choosing an ID strategy is like choosing how to number tickets at a service counter. Sequential numbers (auto-increment) are simple and sortable but reveal how many tickets have been issued and cannot be generated by two counters independently without coordinating. A random ticket code (UUID) can be generated anywhere with no coordination, but two tickets issued seconds apart look nothing alike, which makes sorting or indexing by issue time harder.

text

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

What we're doing: Show why a multi-writer system had to move off auto-increment IDs.

id-strategy-migration.txttext

Original design: single Postgres instance,
auto-increment order IDs. Simple, sortable, fine.

System grows: orders are now created by multiple
independent regional services, each with its own
database, for lower write latency.

Problem: two regions cannot both hand out
auto-increment ID 4,502 without a shared counter —
which reintroduces exactly the cross-region
coordination the split was meant to avoid.

Fix: switch new orders to ULIDs.
  - each region generates IDs independently, no
    collisions, no shared counter
  - IDs still sort correctly by creation time,
    which every existing "recent orders" query
    depended on

9: This is the actual failure mode of auto-increment once there is more than one writer.
14: ULID was chosen specifically because it preserves the one property (time-sortability) the system still needed.

Why this works: The ID strategy is not a cosmetic choice — it directly determines whether a system can add independent writers later without a redesign.

Defaulting to auto-increment IDs without considering future multi-writer needs

Wrong

text

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,  -- fine until a second
  ...                     -- writer needs to exist
);

Better

text

-- if multiple independent writers are plausible
-- later, choose a coordination-free ID up front:
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ...
);

What you see: A system that scales to multiple regions or services generating the same entity type hits ID collisions or needs a disruptive migration off auto-increment, discovered only once a second writer is actually added.

Why: Auto-increment's single-authority requirement is invisible while there is only one writer — the cost only appears the day a second one is added, by which point migrating every existing reference to the old ID scheme is expensive.

Four ID strategies

Auto-increment

1 authority, sortable

UUID

no coordination, not sortable

ULID

no coordination, sortable

Domain-specific

human-readable

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

Together

text

Auto-increment: 1, 2, 3, 4, 5...
  - reveals the total row count and creation order
  - two separate database instances cannot generate
    non-colliding IDs without coordinating

UUID:  f47ac10b-58cc-4372-a567-0e02b2c3d479
  - any service, anywhere, generates one with zero
    chance of collision, no coordination needed
  - but sorted UUIDs give no information about
    creation order, and insert into a B-tree index
    in random order (page-fragmentation cost)

ULID:  01ARZ3NDEKTSV4RRFFQ69G5FAV
  - generatable independently like a UUID
  - but sorts correctly by creation time, because
    the first part of the value is a timestamp

Remember: 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.

Handling change honestly

The five recurring patterns for when "delete" or "change" is not as simple as it first looks.

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

standardintermediate

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).

Think of it as

Think of a filing cabinet that never actually shreds anything. A 'deleted' folder gets a 'DO NOT USE' stamp instead of being thrown out (soft delete); every time a folder changes, the old version gets photocopied and dated before the change (audit history/versioning); some folders are only valid for a specific date range, like a contract (temporal data); and every folder has a status tab — draft, active, closed — showing exactly what can happen to it next (status modeling).

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

What we're doing: Show why a hard delete breaks referential history, and how soft delete fixes it.

soft-delete-referential-history.txttext

Hard delete:
  DELETE FROM products WHERE id = 42;

  Six months later, an old order's line item still
  references product 42 — but the product row is
  gone. The order history page cannot show what was
  actually purchased.

Soft delete:
  UPDATE products SET deleted_at = now() WHERE id = 42;

  Normal product listings filter WHERE deleted_at
  IS NULL, so it disappears from the catalog — but
  the old order's line item can still join to the
  product row and show its name correctly.

6: This is the concrete failure: a hard-deleted row breaks anything that still references it by ID.
10: Soft delete keeps the row addressable for historical references while still hiding it from normal browsing.

Why this works: Almost any entity referenced elsewhere (a product in an order, a user in a comment) needs to remain addressable even after it is "deleted" from the user's point of view — hard deletion breaks every reference that assumed the row would still exist.

Hard-deleting a row that other records still reference

Wrong

text

DELETE FROM products WHERE id = 42;
-- any existing order_items.product_id = 42
-- now points at nothing

Better

text

UPDATE products SET deleted_at = now()
WHERE id = 42;
-- row stays addressable; queries that should
-- exclude it filter on deleted_at IS NULL

What you see: Historical views (past orders, past invoices) that reference a deleted entity break or show blank/null fields, because the referenced row no longer exists at all.

Why: A hard delete assumes nothing else will ever need to read that row again — true for genuinely standalone data, false for almost anything referenced by history that must remain readable.

Order status: enforced transitions

Pending

start

Paid

Shipped

end

Cancelled

end

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

Together

text

Order status modeling:
  status: 'pending' | 'paid' | 'shipped' | 'cancelled'

Allowed transitions (enforced in code, not just documented):
  pending   → paid, cancelled
  paid      → shipped, cancelled
  shipped   → (terminal — no further transitions)
  cancelled → (terminal)

A request to move 'shipped' → 'pending' is rejected —
the status field is meaningless without its transition
rules also being enforced.

Remember: 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.