Modeling Data Around Access Patterns

By NTh Hai - 7 min read

Beautiful third-normal-form diagrams look rigorous. They also produce chatty queries when the UI needs a screen’s worth of data in one round trip. At the other extreme, a single mega-document is easy to fetch until updates, indexing, and multi-tenant queries collapse under their own weight.

Good solution architecture models data around access patterns: the concrete reads and writes your product must support well.

List the Patterns Before the Tables

Write them in plain language:

  • Create order with N line items in one transaction
  • Show order history for a user, newest 20, paginated
  • Look up product by SKU
  • Recalculate inventory when shipment is confirmed
  • Admin search products by name and category

Each pattern implies:

  • Keys you will look up by
  • Cardinality (one-to-many fan-out)
  • Freshness (must be strongly consistent vs. eventually OK)
  • Transaction boundary (what must succeed or fail together)

If a pattern is not listed, do not optimize for it yet. Hypothetical “maybe someday analytics” should not dictate the OLTP schema.

Normalization Is a Tool, Not a Religion

Normalize when:

  • The same fact would otherwise be copied and drift
  • Many entities share a reference (users, tax rates, SKUs)
  • Updates to shared facts are frequent

Denormalize (or precompute) when:

  • A read path is hot and joins dominate latency
  • The denormalized fields change rarely compared to reads
  • You can define a clear owner of the derived copy

Classic example: store product_name on a line item snapshot at purchase time. The catalog name may change later; the order receipt should not.

Aggregates and Consistency Boundaries

In domain-driven terms, an aggregate is a cluster of data that changes together under one consistency rule. Modeling around aggregates keeps transactions small and clear:

  • Order + line items: often one aggregate
  • Customer profile vs. their orders: usually separate
  • Inventory stock level: its own carefully controlled aggregate

Cross-aggregate updates need explicit strategies: sagas, outbox events, or accepting eventual consistency. Pretending everything is one giant transaction does not scale across services or databases.

Indexes Are Part of the Model

An access pattern without an index plan is incomplete. For each filter/sort:

  • Which columns (or document fields) participate?
  • Is the cardinality selective enough?
  • Will write amplification from the index be acceptable?

Unused indexes slow writes and confuse operators. Missing indexes turn “simple list pages” into production incidents.

Evolving the Model Safely

Schemas change. Plan for it:

  • Expand/contract migrations (add nullable column → backfill → enforce)
  • Version events if you publish integration messages
  • Avoid renames that break readers; prefer new fields then deprecate

Access-pattern-first modeling makes migrations easier because you know which queries must keep working during the transition.

Closing Thought

Tables and collections are implementation. Access patterns are the requirement. Design the schema so the patterns you care about are cheap, correct, and operable — and resist both academic purity and unstructured dumps that only look simple on day one.