Design a Database Schema for an E-Commerce Website

Problem Design the database schema for an e-commerce marketplace. No constraints are given up front — surfacing the bottlenecks and stating the assumptions is part of the task.

Functional requirements

• Order products; track order status over time.
• A customer holds multiple delivery addresses.
• Multiple sellers, with the same product sellable by more than one seller.
• Register a new product; track inventory per seller.
• Search products.
• A shopping cart containing products from different sellers.

Non-functional requirements

• ~100M customers, ~1M sellers, ~50M distinct products, ~200M seller listings.
• ~1M orders/day (~12/sec average, ~200/sec during a sale event, ~5,000/sec in the first minute of a flash sale).
• Search and browse dominate: ~30k QPS reads vs ~200/sec order writes — a ~150:1 read/write ratio.
• Order write p99 < 300 ms; product page p99 < 200 ms.
• Inventory must never oversell: decrements are strongly consistent even at 5,000/sec on a single hot SKU.
• Orders retained 7 years for tax/audit: ~2.5B rows → time-partitioned, cold data archived.

Key components

• Users, Addresses (1-to-many with Users; the address must be snapshotted onto the order, not referenced — see below).
• Sellers; Products (the catalog entity: title, brand, specs, category — seller-independent); ProductListings (seller_id + product_id + price + inventory — the join that makes one product sellable by many sellers).
• Cart, CartItems (referencing listing_id, not product_id, since price and seller are properties of the listing).
• Orders, OrderItems (snapshotting price, title and seller at purchase time), OrderStatusHistory (append-only transitions).
• Inventory per listing, with a reservation mechanism.
• Search index (Elasticsearch) over the catalog, fed by CDC from the products/listings tables.

Deep dives / trade-offs

• Product vs Listing is the central modelling decision: collapsing them means the same physical item is duplicated per seller, so search returns twenty copies of one book and there is no canonical product page. Splitting them gives one product page with a seller/price list ("buy box"), at the cost of a matching problem — deciding that two sellers' uploads are the same product is genuinely hard.
• Snapshot vs reference: an order must never join live to Addresses or Listings. If a customer edits their address or a seller changes the price, historical orders would silently mutate — a correctness and legal problem. Denormalize the address, price and title onto OrderItems at purchase time. This is the case where denormalization is not an optimization but a requirement.
• Order status: a mutable status column loses history and makes "when did this ship?" unanswerable. An append-only OrderStatusHistory gives the audit trail; the current status is a projection or a cached column maintained alongside.
• Inventory and overselling: a naive read-check-decrement races and oversells at any real concurrency. Options are a conditional/atomic decrement (UPDATE ... WHERE qty >= n), SELECT FOR UPDATE (serializes the hot SKU — a queue forms at 5,000/sec), or a reservation model with TTL (cart holds stock for 10 min, needs a reaper for abandoned carts). Discuss the flash-sale hot row explicitly: one SKU is one row, and every strategy serializes there.
• Multi-seller cart: one cart becomes N orders (or one order with N fulfillments) since each seller ships, cancels, and refunds independently. Decide whether Order or Fulfillment is the unit — this choice propagates through payments, returns and status.
• Search: never run search off LIKE queries against the primary. A separate inverted index fed by CDC introduces lag — decide whether a new listing appearing in search 5 s late is acceptable (it is; an out-of-stock item still showing is worse).