OLTP Modeling: Designing Operational Schemas

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.

primary key (order_id, line_no)

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.

Start with verbs: customer registers, places an order, payment is captured, a refund is issued. Verbs expose writes, state changes, and history; a list of nouns does not.

For each verb, ask what must stay true — then ask where that rule can actually live. Work the four events below: the writes are given, and you decide whether the invariant belongs to a constraint, to transaction logic, or to nothing but application code.

-- EAV stores one row per attribute. Reading a single
-- product back requires a pivot per attribute:
select
  max(case when attribute_name = 'name'
    then attribute_value end) as name,
  max(case when attribute_name = 'price'
    then attribute_value end) as price
from eav
where entity_id = 'p1';

The commerce model has customers, products, orders, and order items. Each table needs one row meaning and one identity, and the identity is what turns that meaning into something the database can enforce.

orders is one row per order. order_items is the interesting one: assemble its primary key below, then watch three writes — a genuine new line, a replayed submit, and the first line of a new order — land or bounce.

create table customers (customer_id uuid primary key, email text not null unique);
create table products (product_id uuid primary key, sku text not null unique, current_price numeric(12,2) not null check (current_price >= 0));
create table orders (order_id uuid primary key, customer_id uuid not null references customers(customer_id), status text not null default 'draft', created_at timestamptz not null default now());
create table order_items (order_id uuid not null references orders(order_id), line_no integer not null, product_id uuid not null references products(product_id), quantity integer not null check (quantity > 0), unit_price numeric(12,2) not null check (unit_price >= 0), primary key (order_id, line_no));

Constraints turn business rules into database behavior. Primary keys identify rows, unique constraints protect alternate identities, foreign keys protect relationships, not-null protects required facts, and checks protect row-level validity.

Defaults provide a safe initial value, but they are not validation. A default draft status does not prove every later status transition is valid.

Switch the four constraints on order_items off one at a time below. Each one is the only thing standing in front of one specific bad row.

-- Scenario: a bulk load arrives child-rows-first
-- (or two tables reference each other circularly).
-- An immediate FK rejects the child insert; a
-- deferred FK waits until commit to validate.
alter table order_items
  drop constraint order_items_order_id_fkey,
  add constraint order_items_order_id_fkey
    foreign key (order_id)
    references orders(order_id)
    deferrable initially deferred;

begin;
-- Child first: orders row does not exist yet.
-- Immediate FK would fail HERE; deferred waits.
insert into order_items
  (order_id, line_no, product_id, quantity, unit_price)
  values ('o200', 1, 'p5', 2, 19.99);
insert into orders (order_id, customer_id, status)
  values ('o200', 'c1', 'draft');
-- At commit PostgreSQL validates the FK: both rows exist.
commit;

deferred constraint

A constraint marked DEFERRABLE INITIALLY DEFERRED that PostgreSQL checks at commit rather than on each row write, so a bulk load or circular insert may violate it mid-transaction.

EXCLUDE constraint

A PostgreSQL constraint that rejects two rows when a stated operator expression between them is true — generalizing uniqueness beyond equality, for example forbidding two bookings whose time ranges overlap.

ON DELETE behavior is a business policy encoded in a relationship. It is not a convenience option.

Three parents below have children that mean three different things: a draft line, a line on a shipped order, and an optional coupon. Cross each parent with each action — exactly one cell per parent is right, and two of the wrong ones fail at runtime rather than at declaration.

create table order_items (
  order_id uuid not null references orders(order_id) on delete cascade,
  product_id uuid not null references products(product_id) on delete restrict,
  line_no integer not null,
  primary key (order_id, line_no)
);

An order placement is not four unrelated inserts. It is one business action: create the order, create its lines, reserve inventory, record status, then commit together — and if a required step fails, the rollback stops a half-order from ever becoming visible.

Atomicity is only half the job. Two sessions can each be perfectly atomic and still corrupt the data between them. Step the race below under each of the three guards, and watch the last unit of stock get sold once — or twice.

begin;
insert into orders
  (order_id, customer_id, status)
  values (:order_id, :customer_id, 'draft');
insert into order_items
  (order_id, line_no, product_id, quantity, unit_price)
  values (:order_id, 1, :product_id, :qty, :price);
update inventory
  set available = available - :qty
  where product_id = :product_id
    and available >= :qty;
-- if the inventory update affected 0 rows: rollback
update orders set status = 'placed' where order_id = :order_id;
commit;

-- BROKEN: application checks uniqueness, then inserts.
-- Two sessions can both pass the check before either
-- commits, producing a duplicate username.

-- Session A                 Session B
-- SELECT count(*) ...       SELECT count(*) ...
-- (both see 0 rows)         (both see 0 rows)
-- INSERT username='ali'      INSERT username='ali'
-- COMMIT                    COMMIT -- duplicate!

-- FIX 1: declare a UNIQUE constraint (preferred).
alter table accounts
  add constraint accounts_username_uq unique (username);
-- Now one INSERT raises a unique-violation error;
-- the application retries or surfaces the conflict.

-- Note: FOR UPDATE cannot help here. It locks rows that
-- exist, and the row being raced over does not yet.

READ COMMITTED

PostgreSQL's default isolation level: each statement sees only rows committed before it started, but two statements in the same transaction may see different values if a concurrent commit happens between them.

SELECT FOR UPDATE

A locking read (PostgreSQL and most SQL engines; syntax varies) that holds a row-level lock until commit. It only locks rows that already exist — it cannot guard an insert race unless you lock an existing parent row.

orders keeps the current status so operational screens can read it in one row. A separate status-history table records each transition, its actor, and its time.

Play the three transitions below, then ask both tables the same three questions. Both answer "what is it now"; only one answers "who" and "how long". Keep the transition policy itself in transaction logic when it depends on the previous state.

create table order_status_history (
  order_id uuid not null references orders(order_id),
  changed_at timestamptz not null default now(),
  old_status text,
  new_status text not null,
  changed_by text not null,
  primary key (order_id, changed_at)
);

Indexes support the reads and integrity checks your write model requires. Primary and unique constraints create unique indexes automatically in PostgreSQL.

Design from query shapes, not from a blanket rule to index every column. Match each of the four queries below to the index that actually serves it — one of the five candidates is a decoy that looks right and never gets used.

create index orders_customer_created_at_idx
  on orders (customer_id, created_at desc);
create index order_items_product_id_idx on order_items (product_id);
-- primary key and unique constraints already create their own unique indexes.

-- Partial: only index active orders;
-- avoids bloating the index with completed history.
create index orders_active_customer_idx
  on orders (customer_id, created_at desc)
  where status not in ('shipped', 'cancelled');

-- Covering (INCLUDE): serve the lookup query
-- without touching the heap row.
create index orders_id_covering_idx
  on orders (order_id)
  include (status, created_at);

-- Expression: case-insensitive email lookup.
create index customers_lower_email_idx
  on customers (lower(email));
-- Query must use lower(email) = lower($1)
-- to hit this index.

B-tree index

PostgreSQL's default index structure; supports equality, range, and ORDER BY on indexed columns.

hash index

An index that stores only a hash of each key value; supports equality comparisons only, with smaller on-disk size for large-key columns.

covering index

An index that carries extra non-key columns via INCLUDE, so a covered query reads no heap rows at all (PostgreSQL 11+).

A multi-tenant schema has to decide where tenant identity lives and how a relationship proves that a record never crosses a tenant boundary.

Below is the invoices table of an agency billing product. Two of its five rows are cross-tenant: find them, then declare the composite foreign key and watch them become unwritable. Application filters cannot do this job, because a missing WHERE clause is exactly the bug.

create table clients (
  tenant_id uuid not null, client_id uuid not null, email text not null,
  primary key (tenant_id, client_id),
  unique (tenant_id, email)
);
create table invoices (
  tenant_id uuid not null, invoice_id uuid not null, client_id uuid not null,
  primary key (tenant_id, invoice_id),
  foreign key (tenant_id, client_id) references clients(tenant_id, client_id)
);

Hard delete removes a row. Soft delete marks it no longer active. The right choice depends on legal retention, audit needs, recoverability, and whether the entity has historical references.

Run the same three operations against all three lifecycle policies below. Only one gets all three right, and it still costs you something on every query you write afterwards.

Retention is broader than deletion: history, backups, derived models, and downstream analytical copies may have their own policy.

alter table customers add column deleted_at timestamptz;

-- Drop the global rule; it would keep the retired email reserved forever.
alter table customers drop constraint customers_email_key;

-- Uniqueness now applies only to the rows that are still active.
create unique index customers_active_email_uq
on customers (email)
where deleted_at is null;

Before shipping an OLTP schema, walk the critical writes: create, update, cancel, delete, retry, and concurrent attempt.

Review the parcel-tracking schema below. Five things in it will cause a production incident and two look wrong but are not — a review that flags everything is as useless as one that flags nothing.

Six design scenarios for this chapter, on the ERD canvas. Each one is graded against a real rubric, seeded with real rows, and each has one fault planted in the data rather than in the diagram.

Build them with the chapter closed. If one goes wrong, come back to the section it belongs to rather than re-reading the whole thing.