Project: E-Commerce Orders

Introduction

E-commerce schemas tie products, inventory, orders, and order lines with strict transactional integrity—stock must not go negative when two buyers checkout together. This project builds tables, implements checkout in a transaction, and writes sales reports with aggregates and window functions.

Prerequisites

• Transactions Basics
• Isolation Levels and Locks
• Group By and Aggregates

Schema

Seed one customer, two SKUs, inventory qty 10 each.

Checkout Transaction

Purchase 2 units of SKU 1:

Code explanation:

• qty >= 2 in WHERE makes update atomic—concurrent checkout gets 0 rows updated if stock insufficient
• App retries or shows "out of stock"

Use SELECT … FOR UPDATE on inventory row first for stricter locking under high contention.

Concurrent Stock (Demo)

Two sessions:

Session A:

START TRANSACTION;
SELECT qty FROM inventory WHERE sku_id = 1 FOR UPDATE;
-- pause

Session B waits on same FOR UPDATE until A commits or rolls back.

Report: Sales by Day

SELECT
  DATE(o.created_at) AS day,
  COUNT(DISTINCT o.id) AS order_count,
  SUM(o.total) AS revenue
FROM orders o
WHERE o.status = 'paid'
  AND o.created_at >= '2026-05-01'
GROUP BY DATE(o.created_at)
ORDER BY day;

Report: Top SKUs

SELECT
  s.sku_code,
  SUM(oi.qty) AS units_sold,
  SUM(oi.qty * oi.unit_price) AS gross
FROM order_items oi
INNER JOIN skus s ON s.id = oi.sku_id
INNER JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'paid'
GROUP BY s.id, s.sku_code
ORDER BY gross DESC
LIMIT 10;

Coupons (Design Sketch)

CREATE TABLE coupons (
  code VARCHAR(30) PRIMARY KEY,
  discount_pct DECIMAL(5,2) NOT NULL,
  valid_until DATE NULL
);
 
CREATE TABLE order_coupons (
  order_id BIGINT UNSIGNED NOT NULL,
  coupon_code VARCHAR(30) NOT NULL,
  PRIMARY KEY (order_id),
  FOREIGN KEY (order_id) REFERENCES orders (id)
);

Apply discount in transaction before final total update.

Deliverables

• Successful checkout reduces inventory
• Failed checkout (qty too high) rolls back order rows
• Daily revenue query runs
• EXPLAIN on report queries

FAQ

Idempotency?

Use client order id unique constraint to prevent double submit.

Payment gateway?

Store payment_ref on orders—outside DB scope.

Shipment tables?

shipments FK to orders—extend schema.

DECIMAL for money?

Always DECIMAL—never float.

Scale inventory?

Partition by warehouse; row-level locks per sku_id.

Event sourcing?

Alternative architecture—relational model still teaches ACID basics.