Design an E-Commerce Platform

Problem Statement

An e-commerce platform enables buyers to browse products, add items to a cart, and complete purchases. The distinct challenges are: (1) a product catalog that must serve millions of queries per second with fast search, (2) inventory management that prevents overselling, and (3) flash sales that create extreme traffic spikes. The correctness requirement for payments and inventory makes this more demanding than a typical read-heavy system.

Requirements

High-Level Architecture

Product Catalog

Product data is stored in MySQL (structured: SKU, price, seller, category) and indexed in Elasticsearch for full-text search with faceted filtering. Product pages are cached aggressively in Redis (TTL: 5 minutes) and at the CDN layer for static assets. The read:write ratio is typically 100:1 or higher — optimize relentlessly for reads.

CREATE TABLE products (
  product_id  BIGINT PRIMARY KEY,
  seller_id   BIGINT NOT NULL,
  title       VARCHAR(500) NOT NULL,
  description TEXT,
  price_cents INT NOT NULL,       -- store money as integers (cents)
  category_id INT NOT NULL,
  status      ENUM('active','inactive','draft') DEFAULT 'active',
  created_at  TIMESTAMP DEFAULT NOW(),
  updated_at  TIMESTAMP DEFAULT NOW() ON UPDATE NOW()
);

CREATE TABLE inventory (
  product_id    BIGINT PRIMARY KEY,
  warehouse_id  INT NOT NULL,
  quantity      INT NOT NULL DEFAULT 0,
  reserved      INT NOT NULL DEFAULT 0,  -- items in active carts/checkouts
  CHECK (quantity >= 0),
  CHECK (reserved >= 0),
  CHECK (quantity >= reserved)
);

Shopping Cart

The cart is a temporary, user-specific data structure. Redis Hashes are ideal: `HSET cart:{userId} productId quantity`. Key design decisions:

• Guest carts: use a cookie-based `sessionId` as the key; merge into user cart on login.
• Cart TTL: expire guest carts after 7 days (`EXPIRE cart:{sessionId} 604800`).
• No inventory reservation at cart time: only reserve when checkout begins to avoid holding inventory for abandoned carts.
• Idempotent updates: use `HSET` (set) not `HINCRBY` (increment) for quantity to handle duplicate requests.

Inventory and Checkout — Preventing Oversell

Preventing overselling is the most critical correctness requirement. Three approaches exist:

Recommended: use Redis `DECRBY` as a fast reservation gate. If Redis quantity >= requested amount, decrement atomically and proceed. If it fails, return 'out of stock' immediately without hitting the DB. Sync Redis inventory to MySQL asynchronously. For final DB commit, use optimistic concurrency to catch any rare discrepancies.

Checkout Flow

Flash Sales

Flash sales (e.g., Black Friday, product launches) cause traffic spikes of 100x normal. Strategies to handle them:

• Pre-register interest: use a waiting room / virtual queue page to absorb the spike. Issue time-stamped tokens; process in batches.
• Redis inventory gate: pre-load the sale inventory count into Redis. Use Lua script for atomic check-and-decrement to prevent race conditions.
• Rate limiting: apply aggressive per-user rate limits on the checkout endpoint during flash sales.
• Pre-scale: provision extra capacity 30 minutes before the sale starts using auto-scaling or pre-warmed instances.
• Static product pages: serve the product page from CDN — don't hit the origin for static content during peak.

-- Redis Lua script for atomic inventory reservation
local key = KEYS[1]           -- e.g., "inventory:product:42"
local requested = tonumber(ARGV[1])
local current = tonumber(redis.call("GET", key) or "0")

if current >= requested then
  redis.call("DECRBY", key, requested)
  return 1  -- success
else
  return 0  -- out of stock
end