DATA ARCHITECTUREOLTP

OLTP Modeling: Designing Operational Schemas

How data engineers design data that lasts — pick a topic on the left and its full breakdown loads here: the mental model, ERDs and worked schemas, trade-offs, edge cases, and the decisions that separate a durable model from a fragile one.

⏱ 18 min readTopics chapter readerLevel · Foundations
01 · Orientation

What You'll Master Here

Model the write workflow first, enforce invariant facts in the database, and make one business action commit atomically.

⏱ 4 min · Topic 1 of 12

OLTP modeling designs the database behind operational workflows: create an order, reserve inventory, update status, issue a refund, and find the current answer quickly.

Step through the five defenses below. One checkout write has to survive all of them, and each one is a topic in this chapter. Chapter 5 gave you a normalized commerce model; this chapter asks whether concurrent applications can write to it safely.

One checkout write, five defensesCustomer c1 buys two Trail Boots. Walk the layers the write has to survive.
The schema that provides it
primary key (order_id, line_no)
One row means one product line inside one order. Every later rule has something precise to attach to.
Layer 1 of 5 is holdingEvery layer above is a separate design decision. Remove it to see the incident it prevents.
Core mental model

Model the write workflow first, enforce invariant facts in the database, and make one business action commit atomically.

Why it matters

Operational data is the source of truth. A weak schema makes every API and service reimplement integrity rules inconsistently.

OLTP
Online transaction processing: many small, concurrent writes and current-state reads.
constraint
A database-enforced rule such as primary key, foreign key, unique, not null, or check.
transaction
A set of changes that commits together or rolls back together.
access path
The common lookup/filter pattern an index is designed to serve.
Common mistake

Designing tables from a dashboard instead of from operational writes. The source system cannot enforce correct state transitions or identities.

Better habit

Write business events before DDL.

Map each invariant to enforcement.

Make transaction boundaries explicit.

What to say

I start from the write workflow, state the grain and identity of each table, enforce invariants with constraints, and wrap one business action in a transaction.

Remember this

OLTP schemas are contracts for safe concurrent writes.