Data Modeling and SQL

When a correct-looking total is wrong

Suppose a marketplace asks for a buyer’s recent paid orders. Each result needs the order date, item total, and captured-payment total.

The first order has three items and two captured payments. A plausible query joins all three tables and sums both amounts:

SELECT o.id,
       SUM(oi.quantity * oi.unit_price_cents) AS item_total_cents,
       SUM(p.amount_cents) AS paid_total_cents
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN payments p ON p.order_id = o.id
WHERE o.buyer_id = ?
  AND o.status = 'paid'
  AND p.status = 'captured'
GROUP BY o.id;

It runs. The types line up. The totals are false.

The three item rows combine with the two payment rows to produce six rows before aggregation. Every item is counted twice and every payment three times. The bug is not obscure SQL syntax; it is a failure to say what one row means at each stage of the query.

That is the central discipline of relational work. A data model gives durable facts an identity and a set of rules. A query must preserve the meaning and cardinality of those facts as it combines them. An index is justified by the work the query actually asks the database to do. A transaction protects the rules when writers overlap. A migration changes the representation without briefly making the facts untrustworthy.

In an interview, the schema sketch is only the beginning. The design becomes credible when you can follow one important fact through all of those boundaries.

Name the facts before the tables

For this marketplace, a buyer places an order containing products from one or more sellers. Prices can change after purchase. A payment provider may capture an order in more than one payment, and its response may be retried.

Before writing DDL, separate the facts:

• buyers, sellers, products, and orders have durable identity;
• an order item records what was purchased, in what quantity, at what price, and from which seller;
• a payment records a provider-confirmed movement of money, not merely the current value of orders.paid;
• an order’s item total and captured total are derived answers;
• the product’s current price and seller are not allowed to rewrite history.

This classification resolves several modeling choices. unit_price_cents on an order item is an intentional snapshot, not careless duplication. Captures need their own rows because retries, partial captures, refunds, disputes, and reconciliation have identities and histories that one Boolean cannot carry. The current order status may remain convenient, but it is not the sole record of what the payment provider did.

Now the tables can express those decisions:

CREATE TABLE buyers (
  id BIGINT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE sellers (
  id BIGINT PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  seller_id BIGINT NOT NULL REFERENCES sellers(id),
  name TEXT NOT NULL,
  current_price_cents BIGINT NOT NULL
    CHECK (current_price_cents >= 0)
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  buyer_id BIGINT NOT NULL REFERENCES buyers(id),
  status TEXT NOT NULL
    CHECK (status IN ('created', 'paid', 'cancelled', 'refunded')),
  created_at TIMESTAMP NOT NULL,
  paid_at TIMESTAMP
);

CREATE TABLE order_items (
  id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  seller_id BIGINT NOT NULL REFERENCES sellers(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price_cents BIGINT NOT NULL CHECK (unit_price_cents >= 0)
);

CREATE TABLE payments (
  id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id),
  idempotency_key TEXT NOT NULL,
  provider_payment_id TEXT NOT NULL UNIQUE,
  amount_cents BIGINT NOT NULL CHECK (amount_cents > 0),
  status TEXT NOT NULL
    CHECK (status IN ('captured', 'refunded', 'disputed')),
  recorded_at TIMESTAMP NOT NULL,
  UNIQUE (order_id, idempotency_key)
);

Keys say which identity survives

The numeric primary keys give rows stable internal identity. Email is still unique, but other tables do not depend on it: an address can change, and its reassignment policy may change as the product evolves. Provider payment identity is different. It names an external fact that must not be counted twice, so it earns a uniqueness constraint.

The same reasoning applies to relationships. If the product may appear only once in an order, add UNIQUE (order_id, product_id). If the same product may appear in separate shipment groups or under distinct promotions, that constraint would reject valid facts. Cardinality comes from the product rule, not from a preference for tidy schemas.

Foreign keys prevent references to missing buyers, products, sellers, and orders. NOT NULL and CHECK constraints protect local truths from every writer: HTTP handlers, jobs, imports, repair scripts, and future services. Application validation should still produce useful errors, but it is not a substitute for storage-level protection.

Some rules cross rows and cannot be expressed by a simple CHECK. The sum of captured payments must not exceed the amount the order permits. A seller snapshot should agree with the product at the moment an item is created. A paid order should have capture evidence. Those invariants need a controlled write transaction, a different representation, or later reconciliation. Say which boundary owns them; do not imply that DDL alone has solved them.

Normalize authority; duplicate history deliberately

Normalization keeps an updateable fact in one authoritative place. The product name and current price belong to products; copying them to every open shopping-cart row would create avoidable drift.

An order item serves a different purpose. Once purchased, its price is historical evidence. Reading today’s product price would change yesterday’s order total. The seller snapshot has the same justification if product ownership can change. These values have an explicit source at creation time and thereafter describe the sale, not the current catalog.

A cached buyer total would be true denormalization: a derived value stored to avoid repeated aggregation. It earns a column or projection only when a real read path needs it and the design names its source of truth, acceptable staleness, update mechanism, and rebuild path. “Joins are slow” names none of those things.

Repair the query by protecting cardinality

Return to the false total. One result row should represent one order. Items are many per order, and payments are also many per order. Each many-side must be reduced to one row per order before the two sides meet:

WITH item_totals AS (
  SELECT order_id,
         SUM(quantity * unit_price_cents) AS item_total_cents
  FROM order_items
  GROUP BY order_id
),
payment_totals AS (
  SELECT order_id,
         SUM(amount_cents) AS paid_total_cents
  FROM payments
  WHERE status = 'captured'
  GROUP BY order_id
)
SELECT o.id,
       o.paid_at,
       it.item_total_cents,
       pt.paid_total_cents
FROM orders o
JOIN item_totals it ON it.order_id = o.id
JOIN payment_totals pt ON pt.order_id = o.id
WHERE o.buyer_id = ?
  AND o.status = 'paid'
ORDER BY o.paid_at DESC, o.id DESC
LIMIT 20;

The two common-table expressions establish a useful invariant: each produces at most one row per order_id. Their join cannot multiply totals.

Using inner joins here makes a paid order without items or captures disappear. That may be appropriate for a customer-facing list, but it can also conceal corruption. A reconciliation query would begin with orders, use left joins, and select rows whose item or payment total is missing. Join type follows the question: is absence impossible, irrelevant, or exactly what we are trying to find?

Filters after a left join deserve the same care. This condition silently removes orders with no payment and turns the join into an inner one:

LEFT JOIN payments p ON p.order_id = o.id
WHERE p.status = 'captured'

Place the child condition in the join, or filter and aggregate the child in a subquery, when the parent must remain even if no child qualifies.

Use a window when the row must remain a row

Aggregation answers “how much?” Sometimes the question is “which payment row was most recently recorded for each order?” A window function can rank rows without collapsing away their columns:

WITH ranked_payments AS (
  SELECT p.*,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY recorded_at DESC, id DESC
         ) AS rn
  FROM payments p
)
SELECT order_id,
       provider_payment_id,
       status,
       amount_cents,
       recorded_at
FROM ranked_payments
WHERE rn = 1;

The id tie-breaker makes the choice deterministic when timestamps match. Use ROW_NUMBER when exactly one row must win. Use DENSE_RANK when tied values should share a rank, such as sellers tied for monthly revenue. The function should follow the requested semantics rather than familiarity with a particular SQL feature.

An index is a claim about work

The recent-orders query filters one buyer and one status, then reads in descending paid-time order with the ID as a tie-breaker. A plausible index is:

CREATE INDEX orders_buyer_status_paid_id_idx
  ON orders (buyer_id, status, paid_at DESC, id DESC);

This is not a universal formula. It is a claim that the engine can narrow to one buyer and status, then read the required order without a separate sort. The index costs storage and work on every affected insert and update. A lone index on a low-selectivity status column would usually be much less useful.

For deeper pagination, carry the last (paid_at, id) pair in a cursor and seek beyond it instead of making the engine skip a growing offset:

WHERE buyer_id = ?
  AND status = 'paid'
  AND (paid_at, id) < (?, ?)
ORDER BY paid_at DESC, id DESC
LIMIT 20;

Tuple-comparison syntax and null ordering vary by database, so adapt the expression to the chosen engine. The durable idea is a stable order and a cursor containing every ordering key.

Then inspect the plan. With the engine’s EXPLAIN or equivalent, compare estimated rows with actual rows when available. Check whether the index narrows the order set, whether either aggregation scans far more history than the request needs, which join strategy is chosen, whether a sort remains, and how many rows are read to return twenty.

The plan may reveal a deeper problem. As written, the common-table expressions could aggregate all items and payments before filtering to one buyer’s recent orders, depending on the engine and version. A derived recent_orders step can first select the twenty order IDs, then aggregate children only for those IDs. The plan tells you whether that rewrite changes real work. Performance reasoning starts with a measured access path, not a reflex to add caches.

Make concurrent writes prove the same rules

Read queries can be false when joins multiply rows. Writes can be false when two transactions both act on the same stale observation.

Suppose the marketplace keeps one inventory row per SKU. This sequence is unsafe:

read available quantity
if enough remains, increase reserved quantity

Two requests can both see the last three units and both reserve them. Put the condition in the mutation:

UPDATE inventory
SET reserved = reserved + ?
WHERE sku = ?
  AND total - reserved >= ?;

One affected row means the reservation succeeded. Zero means the invariant was false when the database tried to commit the change. The order item, reservation record, and idempotency record should be written in the same local transaction so they cannot disagree after a crash.

Do not hold that transaction open while calling a payment provider. Use the provider’s idempotency contract for the remote operation, then record its stable payment ID under a unique constraint. If the process loses an outcome, retry or reconcile through that identity. A database transaction cannot make an external service atomic with local storage.

Transactions are not incantations. Name the invariant, the rows that carry it, the concurrent operations that threaten it, and the isolation or locking behavior on which the answer depends. A conditional update, uniqueness constraint, row lock, serializable transaction, or single writer can each be right for a different representation.

Change the schema while both versions are alive

Now the marketplace becomes multi-tenant. Every order needs an account_id, but millions of existing rows lack one, and old application instances will run during deployment.

Adding a non-null column and immediately switching the code treats a migration as a single statement. In production it is a period when old data, new data, old code, and new code coexist.

Expand first:

1. Add nullable account_id and its foreign key in the least disruptive form supported by the database.
2. Deploy code that writes account_id for every new order while reads still tolerate the old shape.
3. Backfill old rows in bounded, restartable batches from an authoritative buyer-to-account relationship. Record rows that cannot be mapped instead of guessing.
4. Verify null counts, orphan counts, tenant-level samples, and the important order queries. Add the index needed by the new access path without assuming index creation is harmless on a hot table.
5. Switch reads and authorization checks to account_id, then observe the new path long enough to find stale writers.
6. Enforce NOT NULL only after the data and every writer satisfy it. Remove the fallback path in a later release.

Rollback belongs to the design. During expansion, old readers can ignore the new column and new writers can keep the previous source facts intact. After the contract phase removes those facts, rollback may require a forward fix or data restoration rather than redeploying an old binary. State that boundary before destructive cleanup.

A compact interview artifact

Under time pressure, carry one slice far enough to make its risks inspectable:

• say what one durable row represents and which identity survives change;
• put business uniqueness, references, required values, and local domain limits into constraints;
• explain every snapshot or derived value by its authority and repair policy;
• define what one output row means before joining;
• derive an index from one query, then say what the plan should confirm;
• identify the write that races and the transaction boundary that protects it;
• describe how the next schema version coexists with old rows and old code.

For practice, vary the marketplace instead of starting a new toy problem. Allow one product to ship from several sellers, then decide which identities and uniqueness rules must change. Next, ask for every buyer and their latest successful payment, including buyers with none; preserve the buyer row while using a deterministic window. Finally, split one account into two and design the migration without rewriting historical orders into false ownership.

A strong relational answer does more than arrange nouns into tables. It keeps the same fact true when rows are joined, requests overlap, access paths grow, and the schema changes underneath running software. The next chapter takes one of those facts—the order’s current status—and asks a harder question: who is allowed to move it, and what durable evidence should each move leave behind?