Project: E-Commerce Schema and a Transaction Lab

Build the mental model

This project combines three lessons rarely exercised together: tables, keys, and relationships for the schema; indexing for speed as tables grow; and transactions with ACID so a multi-step write fully happens or not at all.

Tables, keys, relationships - the schema itself
Indexing - keeping lookups fast as tables grow
Transactions and ACID - all-or-nothing multi-step writes

E-commerce is the textbook case, because a single 'place order' click touches several tables at once and money is on the line if any one of those writes fails silently.

An order can contain many products and a product can appear in many orders, which needs a join table, Order_Items, that also carries quantity and the price at purchase time.

Order_Items will be queried by both order_id and product_id constantly, so both need supporting indexes or those lookups scan the whole table.

All or nothing

If a product runs out of stock partway through, every write attempted so far for that order rolls back, leaving stock counts exactly as they were before, the same guarantee ACID gives a bank transfer.

Notice how the three lessons stack: keys and relationships decide the shape, indexing decides the speed, and ACID decides what happens when part of a transaction cannot go through.

text

E-COMMERCE SCHEMA AND TRANSACTION FLOW
--------------------------------------
E-COMMERCE SCHEMA
-------------------------
USERS
  user_id     PK
  name

PRODUCTS
  product_id  PK
  name
  price
  stock

ORDERS
  order_id    PK
  user_id     FK -> USERS.user_id

ORDER_ITEMS
  order_item_id PK
  order_id      FK -> ORDERS.order_id
  product_id    FK -> PRODUCTS.product_id
  quantity
  price_at_purchase

RELATIONSHIPS
  USERS    1---* ORDERS
  ORDERS   1---* ORDER_ITEMS  *---1 PRODUCTS

TRANSACTION FLOW: createOrder(user, items)
-------------------------------------------
  BEGIN
    check stock for every item first
    if any item short  -> ROLLBACK (no writes)
    else:
      create ORDERS row
      create ORDER_ITEMS rows
      decrement PRODUCTS.stock
    COMMIT

Connect it to a real scenario

Work through both the schema design and the transaction simulation in this order:

Design Users and Products

Both tables stand alone; other tables will point back to them.

Add Orders

order_id as the primary key, plus a user_id FK back to Users.

Add Order_Items as the join table

It holds order_id, product_id, quantity, and price_at_purchase, so history never changes when price does.

Index the join table

Add indexes on both order_id and product_id.

Check stock before writing anything

Begin the transaction and verify every item's stock is sufficient before any write.

Commit or roll back

If sufficient, create the rows, decrement stock, and commit; otherwise roll back with zero writes.

Prove it

Run a successful and a failing order against the same starting inventory and compare how stock changes for each.

Order_Items is a join table that also carries its own data (quantity, price).
Checking stock for every item before writing anything makes rollback simple.
A transaction's write set only becomes real, in full, at COMMIT.

Try the working example

javascript

const products = {
  p1: { name: "Mechanical Keyboard", price: 49.99, stock: 5 },
  p2: { name: "Wireless Mouse", price: 19.99, stock: 2 }
};

const orders = [];
const orderItems = [];
let nextOrderId = 1;

function createOrder(userId, items) {
  // BEGIN TRANSACTION
  for (const item of items) {
    const product = products[item.productId];
    if (!product || product.stock < item.qty) {
      // ROLLBACK: no writes have been applied yet
      return {
        status: "ROLLBACK",
        reason: `insufficient stock for ${item.productId} (requested ${item.qty}, have ${product ? product.stock : 0})`
      };
    }
  }

  const orderId = nextOrderId++;
  const createdItems = [];
  for (const item of items) {
    products[item.productId].stock -= item.qty;
    createdItems.push({
      orderId,
      productId: item.productId,
      qty: item.qty,
      price: products[item.productId].price
    });
  }
  orders.push({ orderId, userId });
  orderItems.push(...createdItems);

  // COMMIT
  return { status: "COMMIT", orderId, items: createdItems };
}

console.log("Order 1 (sufficient stock):");
console.log(JSON.stringify(createOrder("u1", [{ productId: "p1", qty: 2 }])));
console.log("Stock after order 1:", JSON.stringify(products));

console.log("\nOrder 2 (insufficient stock):");
console.log(JSON.stringify(createOrder("u2", [{ productId: "p2", qty: 5 }])));
console.log("Stock after order 2 (unchanged):", JSON.stringify(products));

You should see

Order 1 (sufficient stock):
{"status":"COMMIT","orderId":1,"items":[{"orderId":1,"productId":"p1","qty":2,"price":49.99}]}
Stock after order 1: {"p1":{"name":"Mechanical Keyboard","price":49.99,"stock":3},"p2":{"name":"Wireless Mouse","price":19.99,"stock":2}}

Order 2 (insufficient stock):
{"status":"ROLLBACK","reason":"insufficient stock for p2 (requested 5, have 2)"}
Stock after order 2 (unchanged): {"p1":{"name":"Mechanical Keyboard","price":49.99,"stock":3},"p2":{"name":"Wireless Mouse","price":19.99,"stock":2}}

5-minute try-it

Modify the transaction lab so createOrder also checks that quantity is a positive integer before touching stock, and add a test case with quantity 0 to confirm it rolls back for the same reason as insufficient stock.

One important caution

Decrementing stock for each item as you loop, instead of checking every item's stock first — this can leave some products decremented and others rejected, exactly the partial write ACID exists to prevent.

Modeling Order_Items as a plain foreign key from Orders to Products instead of its own join table, losing the ability to store quantity and price-at-purchase per line item.

PostgreSQL Documentation: Transactions — How Databases Work