An e-commerce platform touches nearly every backend concept that appears in system design interviews: relational data modeling, inventory consistency, distributed payments, search, and caching. This post builds a low-level design from the core entities outward.
Core Entities
Product:
product_id UUID PK
name String
description Text
category_id FK
brand_id FK
created_at Timestamp
SKU (Stock Keeping Unit):
sku_id UUID PK
product_id FK
attributes JSONB # {"color": "red", "size": "M"}
base_price Decimal
sale_price Decimal nullable
sale_ends_at Timestamp nullable
Inventory:
sku_id FK PK
warehouse_id FK PK
quantity_on_hand Int
quantity_reserved Int # items in pending orders
Order:
order_id UUID PK
user_id FK
status Enum # PENDING | CONFIRMED | SHIPPED | DELIVERED | CANCELLED | FAILED
total_amount Decimal
created_at Timestamp
OrderItem:
order_id FK
sku_id FK
quantity Int
unit_price Decimal # snapshot of price at order time
Payment:
payment_id UUID PK
order_id FK
idempotency_key String UNIQUE
provider Enum # STRIPE | PAYPAL
status Enum # INITIATED | SUCCESS | FAILED | REFUNDED
amount Decimal
Cart Design
Carts are ephemeral and read-heavy. Store them in Redis rather than the primary DB:
Key: cart:{user_id}
Type: Redis Hash
Field: {sku_id}
Value: {quantity}
TTL: 30 days (reset on every write)
On checkout, the cart contents are read from Redis, validated (prices, availability), and written to the Orders table. The Redis cart is deleted after a successful order creation. For guest carts, use a session token as the key and migrate it to the user’s key on login.
Inventory: Reservation and Locking
Race conditions in inventory are a classic interview topic. The safe approach is pessimistic locking during reservation:
BEGIN TRANSACTION;
SELECT quantity_on_hand, quantity_reserved
FROM Inventory
WHERE sku_id = ? AND warehouse_id = ?
FOR UPDATE; -- row-level lock
available = quantity_on_hand - quantity_reserved;
IF available < requested_qty THEN
ROLLBACK; RAISE InsufficientInventory;
END IF;
UPDATE Inventory
SET quantity_reserved = quantity_reserved + requested_qty
WHERE sku_id = ? AND warehouse_id = ?;
COMMIT;
The reservation is released (decremented) on order cancellation or payment failure, and converted to a deduction from quantity_on_hand on shipment.
Order Flow
- Client calls
POST /orders→ server creates order in PENDING state. - Inventory reservation runs inside a DB transaction (see above).
- Server calls payment provider with an idempotency key =
order_id. - Payment provider webhook fires:
POST /webhooks/payment. - On
SUCCESS: order → CONFIRMED. OnFAILED: order → FAILED, inventory reservation released.
Payment Idempotency
Network timeouts can cause the client to retry a payment request. Without an idempotency key, the user gets charged twice. Use order_id as the idempotency key passed to the payment provider (Stripe supports this natively). On the webhook side, use payment_id as a unique constraint in the Payments table — a second webhook delivery for the same event is a no-op.
Product Search with Elasticsearch
Index: products
Fields:
product_id, name (text + keyword), description (text),
category (keyword), brand (keyword), tags (keyword[]),
price_min (float), # min SKU price
price_max (float), # max SKU price
in_stock (boolean),
rating (float)
Query example (faceted search):
{
"query": {
"bool": {
"must": [{"match": {"name": "running shoes"}}],
"filter": [
{"term": {"in_stock": true}},
{"range": {"price_min": {"gte": 50, "lte": 150}}},
{"term": {"category": "footwear"}}
]
}
},
"aggs": {
"brands": {"terms": {"field": "brand"}},
"price_ranges": {"range": {"field": "price_min", "ranges": [...]}}
}
}
Pricing: Sale Prices and B2B Tiers
Resolve effective price at read time:
def effective_price(sku, user):
if user.tier == "WHOLESALE":
return sku.wholesale_price
if sku.sale_price and now() < sku.sale_ends_at:
return sku.sale_price
return sku.base_price
Always snapshot the resolved price into OrderItem.unit_price at order creation time. Never recalculate from the SKU after the fact — prices change.
Key APIs
GET /products?q=&category=&brand=&price_min=&price_max=&page=
GET /products/{product_id}
POST /cart/items body: {sku_id, quantity}
DELETE /cart/items/{sku_id}
POST /orders body: {shipping_address_id, payment_method_id}
GET /orders/{order_id}
POST /webhooks/payment (called by payment provider)
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do you prevent overselling inventory in an e-commerce system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Use pessimistic locking (SELECT FOR UPDATE) within a database transaction. Reservation flow: BEGIN TRANSACTION; SELECT quantity_on_hand, quantity_reserved FROM inventory WHERE sku_id=X AND warehouse_id=W FOR UPDATE; check available = quantity_on_hand – quantity_reserved >= requested_qty; if yes, UPDATE inventory SET quantity_reserved = quantity_reserved + requested_qty; COMMIT. This serializes concurrent reservations for the same SKU. On order confirmation (payment success): UPDATE inventory SET quantity_on_hand = quantity_on_hand – qty, quantity_reserved = quantity_reserved – qty. On order cancellation: UPDATE inventory SET quantity_reserved = quantity_reserved – qty. A background job reconciles quantity_on_hand with physical warehouse counts nightly.”}},{“@type”:”Question”,”name”:”How do you design the cart for an e-commerce platform?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Store the cart in Redis as a hash: key=cart:{user_id}, field=sku_id, value=quantity. TTL=30 days, refreshed on every cart interaction. Redis hash supports O(1) get, set, increment, and delete per item. The cart holds only sku_id and quantity – product details (name, price, images) are fetched at cart read time from the product service with a short TTL cache. This avoids stale data in the cart (prices can change). On checkout: transfer cart contents to a pending Order record in the database, then clear the cart. For guest users: store cart in a browser cookie or a guest session key, merge into user cart on login.”}},{“@type”:”Question”,”name”:”How do you handle payment idempotency in order processing?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Generate an idempotency_key = hash(order_id + user_id + amount) before initiating payment. Pass this key to the payment processor (Stripe, Braintree) with each charge request. If the HTTP request times out or errors, retry with the same idempotency_key – the payment processor will return the original result if the charge already went through. Store idempotency_key on the Order record and check for duplicates on re-entry. Payment webhooks: the processor sends a webhook on charge success/failure. The webhook handler updates order status atomically: UPDATE orders SET status='CONFIRMED' WHERE order_id=X AND status='PENDING' (compare-and-swap to prevent duplicate processing). Store processed webhook event IDs to deduplicate retried webhooks.”}},{“@type”:”Question”,”name”:”How do you implement product search and filtering in an e-commerce platform?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Index all products in Elasticsearch. Document structure: product_id, title, description, category_path, brand, tags, attributes (JSON), price (float), rating, inventory_status. Full-text search: match query on title and description with boosting (title weight 3x). Faceted filtering: use Elasticsearch aggregations for category, brand, price range, and attribute values. Filter by inventory_status=IN_STOCK to hide out-of-stock items. Relevance ranking: combination of text match score, sales velocity (units sold last 30 days), and rating. Sync product data to Elasticsearch on every update via a Kafka consumer – never query the primary DB for search. For autocomplete on the search box, maintain a separate suggestions index with product titles and popular search queries.”}},{“@type”:”Question”,”name”:”How do you model products with variants (size, color) in the database?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Two-level model: Product (product_id, name, description, category_id, brand) and SKU/Variant (sku_id, product_id, attributes JSON, price, weight, images). Attributes JSON stores the variant dimensions: {"size": "L", "color": "blue"}. Each SKU has its own inventory record. The Product record is a logical grouping with shared content (description, brand). Pricing: base price on SKU level. Sale price: add sale_price and sale_end_date columns; active sale price is MIN(price, sale_price) if sale_end_date > NOW(). For display: fetch Product with all its SKUs in one query using JOIN; client-side renders variant selector from SKU attributes. Index (product_id, attributes) for efficient variant lookup.”}}]}
Shopify system design interviews cover e-commerce platform architecture. See common questions for Shopify interview: e-commerce platform and product catalog system design.
Amazon system design covers e-commerce catalog, inventory, and orders. Review design patterns for Amazon interview: e-commerce inventory and order system design.
Stripe system design interviews cover payment flows in e-commerce. See design patterns for Stripe interview: payment processing and order system design.