Skip to content
Tutorials Center
My learning

Topics

Lesson 68 of 75 ยท Projects

Project: E-commerce Orders and Inventory

Design MySQL tables for products, carts, orders and stock, check out inside a transaction, avoid overselling with an atomic decrement, and write sales reports.

Online shops fail in a very specific way: two customers buy the last item at the same moment, and both orders are accepted. The database then promises more stock than exists. This project builds the core of a shop (products, carts, orders and inventory) and puts the emphasis on that problem, so that checkout is correct even when many requests run at once.

You will design the tables, place an order inside a transaction, update stock with a single atomic statement, keep a ledger of every stock change, and finish with sales reports. The ideas are the ones from keys and constraints, INSERT, UPDATE and DELETE and window functions.

What runs live here. The examples run in the SQLite stand-in with a fresh database per run, so there are no real concurrent sessions. SQLite uses BEGIN, changes() and last_insert_rowid() where MySQL uses START TRANSACTION, ROW_COUNT() and LAST_INSERT_ID(). Locking behaviour (SELECT ... FOR UPDATE, deadlocks) is shown as read-only MySQL code and explained.

The five tables

products Price and units in stock.
customers Who buys.
orders A cart first, then paid.
order_items Quantity and price at the time.
1 / 3

Design: the tables

A few choices carry most of the design:

  • Money in integer cents. price_cents INTEGER avoids rounding errors. In MySQL you could also use DECIMAL(10,2); never FLOAT (choosing data types).
  • A cart is an order with status cart. The same order_items table serves both, and checkout simply changes the status. This avoids copying rows between two near-identical tables.
  • The price is copied into each order line. A customer who paid 4.50 must still see 4.50 when the list price changes next week.
  • stock >= 0 as a CHECK. The application should never rely on it, but if a bug slips through, the database refuses to go negative. MySQL enforces CHECK from 8.0.16.
  • Composite primary key (order_id, product_id) on the lines means a product appears at most once per order. Adding the same product again becomes an update of the quantity.

Create the schema and a small catalog:

SQL Schema and catalog

Press Run (or Ctrl + Enter) to see the result here.

The catalog lists three products: Notebook at 4.50 with 10 in stock, Pen at 1.20 with 50, and Backpack at 39.00 with only 2. The Backpack is deliberately scarce, because it is the item we will fight over later.

Carts and checkout

Adding to a cart copies the current price into the line. Checkout then does four things that must succeed together: reduce the stock of every product in the cart, record the movements in the ledger, mark the order paid, and stamp the date. Wrapping them in one transaction makes the whole checkout atomic.

SQL Checkout in one transaction

Press Run (or Ctrl + Enter) to see the result here.

After checkout the stock is 8 notebooks, 45 pens and 2 backpacks (untouched). The ledger column shows -2, -5 and 0, which is exactly the difference from the starting stock, and the order total is 2 x 450 + 5 x 120 = 1,500 cents. Comparing the ledger with the stock column is also a cheap integrity check you can run from a nightly job.

The AND status = 'cart' in the last update is a guard against double submission: if the customer clicks "Pay" twice, the second update changes zero rows and the application can detect that.

In MySQL the same flow, driven from application code, looks like this (read-only, with comments):

SQL
START TRANSACTION;

-- Lock the product rows in a fixed order so two checkouts cannot deadlock.
SELECT id, stock FROM products WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

UPDATE products p
JOIN order_items oi ON oi.product_id = p.id AND oi.order_id = 1
SET p.stock = p.stock - oi.qty;

INSERT INTO stock_movements (product_id, delta, reason, order_id)
SELECT product_id, -qty, 'sale', order_id FROM order_items WHERE order_id = 1;

UPDATE orders SET status = 'paid', placed_on = CURDATE()
WHERE id = 1 AND status = 'cart';

COMMIT;

MySQL allows a multi-table UPDATE ... JOIN, which is more readable than the correlated subquery needed in portable SQL (joins in MySQL).

The race: why read-then-write is wrong

The tempting way to check stock is two steps: SELECT stock, test it in application code, then UPDATE with the new number. Two requests can both read stock = 2, both decide that a purchase of 2 is fine, and both write. The classic timeline:

sequenceDiagram
  participant A as Request A
  participant DB as products.stock
  participant B as Request B
  A->>DB: SELECT stock (2)
  B->>DB: SELECT stock (2)
  A->>DB: UPDATE stock = 0
  B->>DB: UPDATE stock = 0
  Note over A,B: Two orders accepted, one backpack exists

Both requests read the same value before either writes, so the check is useless.

There are two sound fixes:

  1. Atomic conditional update. Let the database check and subtract in one statement: UPDATE products SET stock = stock - 2 WHERE id = 3 AND stock >= 2. The row lock InnoDB takes during the update serializes the two requests, and the second one finds stock = 0, matches no row, and reports 0 affected rows.
  2. Pessimistic lock. SELECT ... FOR UPDATE inside the transaction holds the row lock, so other checkouts wait before they read.

The first is simpler and usually faster, because the lock is held only for the single update. Here it is in action, with two buyers who each want both backpacks:

SQL Atomic decrement stops the oversell

Press Run (or Ctrl + Enter) to see the result here.

Buyer A changes 1 row. Buyer B changes 0 rows, which is the signal to roll back and tell the customer the item is sold out. The stock ends at 0, never negative. In MySQL you read this number with ROW_COUNT() right after the update, or from the driver's "affected rows" value. Be careful with one detail: with some client settings MySQL reports changed rows rather than matched rows, but here a successful update always changes the value, so the two agree.

If your checkout buys many lines, run the conditional update per line and check each count. Any line with 0 rows means ROLLBACK for the whole order.

Deadlocks

When two checkouts update the same products in different orders (A locks product 1 then 2, B locks 2 then 1), each waits for the other and InnoDB aborts one of them with a deadlock error. Two habits prevent most of this: always touch rows in a consistent order (sort by product id), and be ready to retry a transaction that fails with a deadlock. The locking and deadlocks lesson goes deeper.

Sales reports

With paid orders and a ledger in place, the reports are ordinary queries. This example seeds a few paid orders and answers three business questions: revenue per day, best-selling products with a rank, and which products are running low.

SQL Sales reports

Press Run (or Ctrl + Enter) to see the result here.

Revenue is 1,500 + 3,900 = 5,400 cents on 2026-05-04 (two orders) and 1,200 + 450 = 1,650 on 2026-05-05 (one order). The cancelled order and the open cart are excluded by the status = 'paid' filter, which is the most important line in every revenue report. The Backpack ranks first by revenue (3,900) even though it sold one unit, the Pen second (1,800 from 15 units) and the Notebook third (1,350 from 3 units). Only the Backpack is below the low-stock threshold.

In MySQL you would add an index on orders (status, placed_on) for the daily report and on order_items (product_id) for the product report. The foreign key on product_id already creates the second one automatically in InnoDB.

Extending the design

  • Reservations. Hold stock for 15 minutes while the customer pays by keeping a reserved column or a reservations table, and release it with an event or a job.
  • Returns and restocks are new ledger rows (reason = 'return'), never edits of old ones.
  • Multiple warehouses turn stock into a (product_id, warehouse_id) table. The conditional update works the same way on that row.
  • Idempotent checkout. Store a client-generated token with the order and make it UNIQUE, so a retried request cannot create a second order.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is "SELECT stock, check in code, then UPDATE" unsafe?

    The check and the write are separate steps, so another request can slip in between them. The database must check and subtract in one statement.
  2. 2. What tells you the atomic decrement "UPDATE ... SET stock = stock - 2 WHERE id = 3 AND stock >= 2" failed?

    When there is not enough stock the WHERE clause matches no row, so zero rows are affected. The application then rolls back and reports the item as sold out.
  3. 3. Why is the unit price copied into order_items?

    An order is a historical record. Reading the live product price would silently change old invoices.
  4. 4. How do you reduce deadlocks when a checkout touches several products?

    Deadlocks need two transactions waiting for each other in opposite orders. A fixed order removes the cycle, and a retry handles the rare leftover case.
  5. 5. Which filter keeps carts and cancelled orders out of revenue?

    Only paid orders are revenue. Forgetting this filter is a very common reporting bug.

Summary

  • Model products, customers, orders (a cart is an order in status cart), order lines and a stock ledger. Store money as integer cents.
  • Copy the price into each order line so history never changes.
  • Check out in one transaction: decrement stock, write ledger rows, mark the order paid.
  • Prevent overselling with an atomic conditional UPDATE ... WHERE stock >= qty and check the affected-row count, or lock rows with SELECT ... FOR UPDATE. A CHECK (stock >= 0) is the last line of defence.
  • Touch rows in a consistent order and retry after a deadlock.
  • Reports must filter on order status, and the ledger can reconcile with the stock column.

Next: Project: Audit Trail and History Tables.