Database Design Interview: A Tested Order-System Schema

Design a small order system and prove its rules

A database-design interview is easier to evaluate when requirements become an executable schema. This PostgreSQL 16 case models customers, products, orders, and order lines. It covers identities, historical prices, cardinalities, constraints, query patterns, and the limits of what ordinary constraints enforce. Run the setup and subsequent examples in one disposable session, ending with ROLLBACK.

Start with requirements and relationship counts

Each order belongs to one customer. Each line belongs to one order and references one product. An order may contain several lines, including repeated occurrences of the same product. The line number is unique only within its order. The catalog has a current price, while every order line stores its agreed historical unit price. This fixture supports one currency, USD; tax, shipping, discounts, partial refunds, inventory, and payment processing need additional requirements.

customers 1 ── many orders 1 ── many order_items many ── 1 products
customer_id       order_id           (order_id, line_no)          product_id

Names and email addresses are attributes, not the chosen customer identity. Shared or changing email addresses should not silently merge customers. SKU is a unique non-NULL catalog business key here, while product_id is the stable referenced key. Ask whether real SKUs can be recycled before adopting that policy.

Create the schema and complete fixture

BEGIN;
CREATE TEMP TABLE design_customers(
 customer_id integer PRIMARY KEY,name text NOT NULL,email text
) ON COMMIT DROP;
CREATE TEMP TABLE design_products(
 product_id integer PRIMARY KEY,sku text NOT NULL UNIQUE,
 name text NOT NULL,current_price numeric(12,2) NOT NULL CHECK(current_price>=0 AND current_price<>'NaN'::numeric)
) ON COMMIT DROP;
CREATE TEMP TABLE design_orders(
 order_id integer PRIMARY KEY,
 customer_id integer NOT NULL REFERENCES design_customers(customer_id),
 created_at timestamptz NOT NULL,status text NOT NULL CHECK(status IN ('draft','paid','refunded')),
 currency text NOT NULL CHECK(currency='USD')
) ON COMMIT DROP;
CREATE TEMP TABLE design_items(
 order_id integer NOT NULL REFERENCES design_orders(order_id),
 line_no integer NOT NULL CHECK(line_no>0),
 product_id integer NOT NULL REFERENCES design_products(product_id),
 quantity integer NOT NULL CHECK(quantity>0),
 unit_price numeric(12,2) NOT NULL CHECK(unit_price>=0 AND unit_price<>'NaN'::numeric),
 PRIMARY KEY(order_id,line_no)
) ON COMMIT DROP;
INSERT INTO design_customers VALUES (1,'Ada','[email protected]'),
 (2,'Ben','[email protected]'),(3,'Cara',NULL);
INSERT INTO design_products VALUES (1,'BOOK','Book',11),(2,'GAME','Game',15);
INSERT INTO design_orders VALUES
 (101,1,'2026-01-01 10:00+00','paid','USD'),
 (102,2,'2026-01-02 10:00+00','paid','USD');
INSERT INTO design_items VALUES (101,1,1,2,10),(101,2,2,1,15),(102,1,1,1,12);
CREATE INDEX design_orders_customer_time ON design_orders(customer_id,created_at DESC,order_id DESC);

NOT NULL matters alongside CHECK: a CHECK expression that evaluates to unknown does not reject a NULL by itself. Prices must be finite, nonnegative amounts. PostgreSQL numeric accepts NaN and sorts it above ordinary numbers, so a nonnegative check alone is insufficient: both prices explicitly exclude NaN. The numeric(12,2) precision already rejects infinities and out-of-range magnitudes; it rounds extra fractional digits to two places. Validate raw precision separately if rounding is unacceptable. Foreign keys prevent references to missing parents. This design uses the default NO ACTION behavior rather than automatically cascading deletion through order history. The primary keys also establish the intended row grain.

Read historical totals from the line snapshot

SELECT o.order_id,o.currency,SUM(i.quantity*i.unit_price) AS subtotal
FROM pg_temp.design_orders o JOIN pg_temp.design_items i USING(order_id)
WHERE o.status='paid' GROUP BY o.order_id,o.currency ORDER BY o.order_id;

order_id | currency | subtotal
101 | USD | 35.00
102 | USD | 12.00

Order 101 totals two Books at 10 plus one Game at 15. Order 102 bought its Book at 12. Neither total uses today’s catalog Book price of 11. This is an intentional historical fact, not careless redundancy. Recomputing old purchases from a mutable catalog price would rewrite history.

UPDATE pg_temp.design_products SET current_price=99 WHERE product_id=1;
SELECT o.order_id,SUM(i.quantity*i.unit_price) AS unchanged_subtotal
FROM pg_temp.design_orders o JOIN pg_temp.design_items i USING(order_id)
GROUP BY o.order_id ORDER BY o.order_id;

order_id | unchanged_subtotal
101 | 35.00
102 | 12.00

Keep customers without orders in customer reports

WITH totals AS (
 SELECT o.customer_id,COUNT(DISTINCT o.order_id) AS orders,
 SUM(i.quantity*i.unit_price) AS subtotal
 FROM pg_temp.design_orders o JOIN pg_temp.design_items i USING(order_id)
 WHERE o.status='paid' GROUP BY o.customer_id
)
SELECT c.customer_id,COALESCE(t.orders,0) AS orders,
 COALESCE(t.subtotal,0) AS subtotal
FROM pg_temp.design_customers c LEFT JOIN totals t USING(customer_id)
ORDER BY c.customer_id;

customer_id | orders | subtotal
1 | 1 | 35.00
2 | 1 | 12.00
3 | 0 | 0.00

The fixture’s paid orders all have lines. The schema itself does not ensure that every paid order has at least one line, nor that changing status follows an allowed transition graph. Those cross-row or workflow rules need a carefully designed transactional operation or other explicit enforcement. Naming an order status paid does not prove a payment actually settled.

Justify one index from one access pattern

The index on customer_id, created_at descending, and order_id descending supports the intended pattern “recent orders for this customer,” including a stable tie-breaker. The leading customer key is relevant to that lookup. It is not a claim that every date-only query will use this index efficiently. PostgreSQL automatically indexes primary and unique keys, but does not automatically create every useful referencing-side foreign-key index. Inspect plans and write costs before adding more.

For a tiny fixture, a sequential scan may be the correct plan. Benchmark with representative data and a real query, then decide whether a covering index, archival policy, or partitioning is justified. Partitioning a table inside one database and sharding across independent database systems are different architectural decisions; neither is a default requirement for this order model.

Prove the failure paths, then remove the fixture

DO $$ BEGIN
 BEGIN
  INSERT INTO pg_temp.design_items VALUES (101,1,1,1,10);
  RAISE EXCEPTION 'Expected duplicate-line rejection';
 EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'Duplicate line rejected'; END;
 BEGIN
  INSERT INTO pg_temp.design_items VALUES (101,3,999,1,10);
  RAISE EXCEPTION 'Expected unknown-product rejection';
 EXCEPTION WHEN foreign_key_violation THEN RAISE NOTICE 'Unknown product rejected'; END;
 BEGIN
  INSERT INTO pg_temp.design_items VALUES (101,3,1,0,10);
  RAISE EXCEPTION 'Expected zero-quantity rejection';
 EXCEPTION WHEN check_violation THEN RAISE NOTICE 'Zero quantity rejected'; END;
 BEGIN
  UPDATE pg_temp.design_products SET current_price='NaN'::numeric WHERE product_id=1;
  RAISE EXCEPTION 'Expected NaN catalog-price rejection';
 EXCEPTION WHEN check_violation THEN RAISE NOTICE 'NaN catalog price rejected'; END;
 BEGIN
  UPDATE pg_temp.design_items SET unit_price='NaN'::numeric WHERE order_id=101 AND line_no=1;
  RAISE EXCEPTION 'Expected NaN line-price rejection';
 EXCEPTION WHEN check_violation THEN RAISE NOTICE 'NaN line price rejected'; END;
END $$;
SELECT COUNT(*) AS item_rows FROM pg_temp.design_items;
ROLLBACK;

NOTICE: Duplicate line rejected
NOTICE: Unknown product rejected
NOTICE: Zero quantity rejected
NOTICE: NaN catalog price rejected
NOTICE: NaN line price rejected
item_rows
3

The exception blocks catch only the expected constraint failures. An unexpectedly accepted insert raises a different exception and stops the script. The failed test rows never become part of the valid fixture. The final rollback removes all setup and the demonstration catalog-price change.

Keep access and lifecycle requirements concrete

Define which role can read customer contact fields, create orders, change catalog prices, and transition order status. Keep credentials outside SQL examples and application source. Retention, deletion requests, backups, auditing, and encryption depend on the operating environment and business obligations; a four-table DDL snippet does not certify them. Collect those requirements rather than listing security product names as a substitute for a design.

Use a review rubric

Score a design on five concrete questions: Are entity identities stable? Do foreign keys and uniqueness match the cardinalities? Are historical facts preserved? Are required reports correct for zero-match cases? Are workflow rules and index assumptions stated honestly? A normalized core can reduce update anomalies while preserving intentional facts such as the agreed unit price.

Explain what the schema guarantees and what it does not

The fixture proves unique line identities, valid product references, positive quantities, and stable historical totals after a catalog update. It does not yet implement payment settlement, minimum order-line counts, or refund accounting. Making that boundary explicit is part of a complete interview answer.

FAQ

Why not use product_id as the line key? The requirements allow repeated product lines. Why store the price twice? Current catalog price and agreed sale price are different facts. Why not cascade deletes? Historical orders need an explicit lifecycle policy rather than automatic destruction.

Primary references: constraints, numeric precision and special values, and multicolumn indexes.