Physical OLTP

Bazaar uses two suffixes consistently, and the consistency is the point: *_ts is a moment in time (order_ts, created_ts, txn_ts), *_date is a calendar day (joined_date, launched_date). Anybody reading a query knows whether comparing to '2026-06-10' is going to behave.

• Booleans say what they mean: is_guest, not flag. A boolean named after its meaning can be summed into a numerator later, which session b7 relies on.
• Amounts say which amount: list_price on the product, unit_price on the order line, discount_amt beside it. Session b2 argued these are different facts; the naming is what stops them being treated as one.
• Signed amounts say so in a comment: transactions.amount is negative for refunds. A sign convention nobody documented is a bug waiting for a SUM.

Open sql/01_oltp_ddl.sql in the repo. Every table carries a comment saying which session owns the decision above it, so the file doubles as a map of the track. Three worth reading closely:

• orders stores no amount. An order total is derivable from its lines, and a stored total that disagrees with its lines is the most common ecommerce data bug there is. Derive it, or store it as an explicitly reconciled snapshot - never as a casually maintained copy.
• order_items carries merchant_id, which products already knows. That is point-in-time capture, not redundancy: a product can be transferred between merchants, and the sale belongs to whoever sold it.
• transactions.decline_reason is nullable, and it is the only nullable column that earns it: the reason genuinely does not exist for an approved payment. Every other absence in this schema is either forbidden or given a real value.

This is the trap that catches experienced teams. SQLite parses and stores REFERENCES clauses but does not enforce them unless foreign keys are switched on for the connection. A schema can be full of relationships that enforce nothing, and the only symptom is orphan rows appearing months later.

PRAGMA foreign_keys = OFF;

CREATE TABLE merchants (merchant_id TEXT PRIMARY KEY, merchant_name TEXT NOT NULL);
CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  merchant_id TEXT NOT NULL REFERENCES merchants(merchant_id),
  sku         TEXT NOT NULL UNIQUE,
  list_price  REAL NOT NULL CHECK (list_price > 0)
);
INSERT INTO merchants VALUES ('M07', 'Nimbus Audio');

-- M99 does not exist. With foreign keys off, this is accepted.
INSERT INTO products VALUES (1, 'M99', 'M99-1', 129.00);

SELECT p.product_id, p.merchant_id, m.merchant_name AS resolves_to
FROM products p
LEFT JOIN merchants m ON m.merchant_id = p.merchant_id;

The resolves_to column comes back NULL: an orphan. Now switch enforcement on and watch the same INSERT get rejected.

PRAGMA foreign_keys = ON;

CREATE TABLE merchants (merchant_id TEXT PRIMARY KEY, merchant_name TEXT NOT NULL);
CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  merchant_id TEXT NOT NULL REFERENCES merchants(merchant_id),
  sku         TEXT NOT NULL UNIQUE,
  list_price  REAL NOT NULL CHECK (list_price > 0)
);
INSERT INTO merchants VALUES ('M07', 'Nimbus Audio');

-- Same statement, now rejected. Comment it out to see the rest run.
INSERT INTO products VALUES (1, 'M99', 'M99-1', 129.00);

SELECT 'never reached' AS note;

A CHECK constraint sees one row of one table. That makes it perfect for quantity > 0 and useless for anything cross-row or cross-table:

• "A return cannot exceed the quantity sold" needs the order line as well as the return. A CHECK cannot express it, so the rule has to live in application logic, a trigger, or a validation job - and wherever you put it, write down that you put it there. The capstone in b10 makes you make this exact call.
• "An order's total equals the sum of its lines" is cross-row. This is precisely why orders stores no total: the safest way to enforce an invariant is to remove the ability to violate it.
• "Only one current version per merchant" is cross-row too, which is why session b6's slowly-changing dimension gets a validation check in python/validate_model.py rather than a constraint.

The pattern: constraints for what a single row can prove, tests for everything else. A model with no tests is trusting the parts of itself that constraints cannot reach.

SELECT name AS index_name, tbl_name AS on_table
FROM sqlite_master
WHERE type = 'index' AND name NOT LIKE 'sqlite_%'
ORDER BY tbl_name, name;

Read them as questions the application asks: orders by time, orders by user, lines by product, lines by merchant, payments by order, payments by time, cart items by product. Nothing indexed speculatively. Analytical access paths get solved by the star schema in b6 and b7, not by adding indexes here.

SELECT 'users with no email'          AS check_name, COUNT(*) AS offending_rows FROM users        WHERE email IS NULL
UNION ALL SELECT 'orders with no timestamp',       COUNT(*) FROM orders       WHERE order_ts IS NULL
UNION ALL SELECT 'duplicate emails',               COUNT(*) - COUNT(DISTINCT email) FROM users
UNION ALL SELECT 'duplicate SKUs',                 COUNT(*) - COUNT(DISTINCT sku) FROM products
UNION ALL SELECT 'duplicate payment references',   COUNT(*) - COUNT(DISTINCT psp_ref) FROM transactions
UNION ALL SELECT 'non-positive quantities',        COUNT(*) FROM order_items  WHERE quantity <= 0
UNION ALL SELECT 'decline reason on an approval',  COUNT(*) FROM transactions WHERE status = 'approved' AND decline_reason IS NOT NULL;

This is the shape of every data test worth writing: name the promise, count the rows that break it, expect zero. python/validate_model.py is 27 of these, run over the analytical model, and it exits non-zero when any of them fails.

No SQL. python/gen_bazaar_data.py seeds its random generator with 42 and never calls the clock. That single decision is what makes every number on every page of this course reproducible: 985 orders, 1,434 lines, 111,906.48 SGD of settled net revenue, the same on your laptop as in the browser lab.

It also makes the four buried stories teachable. A demand shock on 2026-06-18, a checkout incident on 2026-06-10, one merchant's collapse and a stockout are all deliberate, and they land on the same dates every time you regenerate - so session b8 can ask you to find them and then tell you whether you were right.

The habit to steal: test data with a fixed seed, a documented story, and no wall-clock dependency. Random test data that changes every run cannot be reasoned about, and a model you cannot reason about is a model you cannot trust.