Design the Store — SQL Milestone Project

You have finished Part III. Now you will design a tiny store database you would trust with checkout data: tables, constraints, seed rows, and one transactional purchase.

This is a single-session project. You will not simulate concurrent buyers in the playground; 12.2 and 12.3 showed why those interleavings need real two-session transcripts. Your job here is to build the schema and the checkout transaction that those concurrency lessons can reason about.

What you'll build

You will produce a schema and checkout notebook with four sections:

Store schema: customers, products, inventory, orders, and order lines.
Seed verification: row counts, inactive products, low-stock products, and one seed order total.
Transactional checkout: reserve the last starter kit for customer 4 with visible rehearsal and verification.
Final acceptance: prove the new order, line item, inventory movement, and final table counts.

Keep the store deliberately small. The point is not a full storefront. The point is a database design that refuses bad rows and a checkout transaction that tells one coherent story.

Workspace

Run the starter SQL first. Then replace or extend the TODO blocks with your own answers. The starter code creates the schema and seed data, then leaves the checkout scaffold commented so the rehearsal ritual stays visible.

sql — playgroundlive

-- Design the Store workspace
-- Fixed checkout date: 2026-08-04
-- Tables: customers, products, inventory, orders, order_lines

CREATE TABLE customers (
customer_id integer PRIMARY KEY,
email text NOT NULL UNIQUE,
full_name text NOT NULL,
active boolean NOT NULL DEFAULT true
);

CREATE TABLE products (
product_id integer PRIMARY KEY,
sku text NOT NULL UNIQUE,
product_name text NOT NULL,
unit_price numeric(8,2) NOT NULL CHECK (unit_price >= 0),
active boolean NOT NULL DEFAULT true
);

CREATE TABLE inventory (
product_id integer PRIMARY KEY
  REFERENCES products(product_id)
  ON DELETE RESTRICT,
available integer NOT NULL CHECK (available >= 0),
reserved integer NOT NULL DEFAULT 0 CHECK (reserved >= 0)
);

CREATE TABLE orders (
order_id integer PRIMARY KEY,
customer_id integer NOT NULL
  REFERENCES customers(customer_id)
  ON DELETE RESTRICT,
ordered_on date NOT NULL,
status text NOT NULL DEFAULT 'reserved'
  CHECK (status IN ('reserved', 'paid', 'canceled')),
total_amount numeric(10,2) NOT NULL CHECK (total_amount >= 0)
);

CREATE TABLE order_lines (
order_id integer NOT NULL
  REFERENCES orders(order_id)
  ON DELETE CASCADE,
line_no integer NOT NULL CHECK (line_no > 0),
product_id integer NOT NULL
  REFERENCES products(product_id)
  ON DELETE RESTRICT,
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(8,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, line_no)
);

INSERT INTO customers (customer_id, email, full_name, active)
VALUES
(1, 'ada@store.test', 'Ada Lovelace', true),
(2, 'ken@store.test', 'Ken Thompson', true),
(3, 'mina@store.test', 'Mina Patel', true),
(4, 'rosa@store.test', 'Rosa Diaz', true);

INSERT INTO products (product_id, sku, product_name, unit_price, active)
VALUES
(1, 'beans', 'Espresso beans', 14.50, true),
(2, 'cups', 'Paper cups', 6.00, true),
(3, 'starter-kit', 'Pour-over starter kit', 18.00, true),
(4, 'mug', 'Stoneware mug', 11.00, true),
(5, 'gift-card', 'Gift card', 25.00, false);

INSERT INTO inventory (product_id, available, reserved)
VALUES
(1, 6, 0),
(2, 20, 0),
(3, 1, 0),
(4, 2, 0),
(5, 0, 0);

INSERT INTO orders (order_id, customer_id, ordered_on, status, total_amount)
VALUES
(1001, 1, DATE '2026-08-01', 'paid', 12.00),
(1002, 2, DATE '2026-08-02', 'paid', 47.00),
(1003, 3, DATE '2026-08-03', 'paid', 11.00);

INSERT INTO order_lines (order_id, line_no, product_id, quantity, unit_price)
VALUES
(1001, 1, 2, 2, 6.00),
(1002, 1, 3, 1, 18.00),
(1002, 2, 1, 2, 14.50),
(1003, 1, 4, 1, 11.00);

SELECT 'customers' AS table_name, count(*) AS rows FROM customers
UNION ALL SELECT 'products', count(*) FROM products
UNION ALL SELECT 'inventory', count(*) FROM inventory
UNION ALL SELECT 'orders', count(*) FROM orders
UNION ALL SELECT 'order_lines', count(*) FROM order_lines
ORDER BY table_name;

SELECT p.sku,
     p.product_name,
     p.active,
     i.available,
     i.reserved
FROM products AS p
JOIN inventory AS i
ON i.product_id = p.product_id
ORDER BY p.sku;

-- TODO Checkpoint 1: schema review
-- TODO Checkpoint 2: seed verification
-- TODO Checkpoint 3: transactional checkout

-- Checkout scaffold: rehearse the customer, product, and inventory row first.
-- SELECT c.customer_id,
--        c.full_name,
--        c.active AS customer_active,
--        p.product_id,
--        p.sku,
--        p.active AS product_active,
--        p.unit_price,
--        i.available,
--        i.reserved
-- FROM customers AS c
-- CROSS JOIN products AS p
-- JOIN inventory AS i
--   ON i.product_id = p.product_id
-- WHERE c.customer_id = 4
--   AND p.sku = 'starter-kit';

-- BEGIN;
-- SELECT p.product_id,
--        p.sku,
--        p.unit_price,
--        i.available,
--        i.reserved
-- FROM products AS p
-- JOIN inventory AS i
--   ON i.product_id = p.product_id
-- WHERE p.sku = 'starter-kit'
--   AND p.active = true
-- FOR UPDATE;
-- UPDATE inventory
-- SET available = available - 1,
--     reserved = reserved + 1
-- WHERE product_id = 3
--   AND available >= 1;
-- INSERT INTO orders (order_id, customer_id, ordered_on, status, total_amount)
-- VALUES (1004, 4, DATE '2026-08-04', 'reserved', 18.00);
-- INSERT INTO order_lines (order_id, line_no, product_id, quantity, unit_price)
-- VALUES (1004, 1, 3, 1, 18.00);
-- SELECT p.sku, i.available, i.reserved
-- FROM products AS p
-- JOIN inventory AS i
--   ON i.product_id = p.product_id
-- WHERE p.sku = 'starter-kit';
-- COMMIT;

-- TODO Checkpoint 4: final acceptance queries

⌘/Ctrl + Enter to run

Checkpoint 1: design the schema

Build the five tables and inspect the constraints before you trust the seed.

Create customers, products, inventory, orders, and order_lines.
Give every table a primary key.
Add unique rules for customer email and product SKU.
Add foreign keys from inventory to products, orders to customers, and order lines to orders/products.
Add CHECK constraints for nonnegative money, positive quantities, nonnegative inventory, and allowed order statuses.

Acceptance criteria:

The database has exactly 5 user tables with these names: customers, inventory, order_lines, orders, and products.
orders.status accepts only reserved, paid, or canceled.
order_lines.quantity must be greater than 0.
Inventory values cannot be negative.

Checkpoint 2: trust the seed

Seed the store with deterministic rows and prove the data shape.

Acceptance criteria:

Seed counts are customers 4, inventory 5, order_lines 4, orders 3, and products 5.
There is 1 inactive product: gift-card.
There is 1 active low-stock item with available stock at or below 1: starter-kit, with available 1 and reserved 0.
Seed order 1002 has 2 line rows and a computed total of 47.00.

Checkpoint 3: run the transactional checkout

Create order 1004 for customer 4 on 2026-08-04. It buys 1 starter-kit at 18.00.

The safe shape is:

Rehearse the customer, product, and inventory row.
BEGIN.
SELECT ... FOR UPDATE the starter-kit inventory row.
Run a guarded UPDATE that moves inventory from available to reserved only when enough stock remains.
Insert the order and order line.
Verify the inventory row and the order line.
COMMIT.

Acceptance criteria:

Order 1004 belongs to customer 4, has status reserved, date 2026-08-04, and total 18.00.
Order 1004 has line 1 for product starter-kit, quantity 1, unit price 18.00, and computed line total 18.00.
Inventory for starter-kit moves from available 1 and reserved 0 to available 0 and reserved 1.

Checkpoint 4: final acceptance

After the checkout commits, prove the final state.

Acceptance criteria:

Total orders are 4.
Total order lines are 5.
starter-kit has available 0 and reserved 1.
There is still exactly 1 inactive product.

Stretch goals

Try these after the required checkpoints pass:

Add a payments table that records order 1004's payment on 2026-08-04.
Add a shipments table and a constraint that only allows queued, packed, or shipped.
Create a view that shows each order with its computed line total and compare it with orders.total_amount.
Add an out-of-stock checkout attempt for starter-kit after order 1004: rehearse the inventory row first, then prove the guarded UPDATE affects 0 rows.

How to get unstuck

Use Section 9 when a table definition feels mushy: name the fact, pick the smallest honest type, then make NULL and defaults deliberate. Use 9.2 and 9.3 for constraints and keys. Use Section 10 when you are unsure whether a fact belongs on products, inventory, orders, or order_lines: read the grain of the row.

Use Section 11 only when you are tempted to hide behavior behind a view or trigger; this project should keep checkout explicit. Use 12.1 for transaction choreography, 12.2 for retry thinking, and 12.3 for SELECT ... FOR UPDATE and lock-order discipline. Keep every write boring enough to review.