E-Commerce Database Design
E-commerce Database Design
E-commerce is one of MySQL's most common use cases, spanning product management, inventory control, order processing, and payment flows.
Product Model: SPU vs SKU
Separate the product concept (SPU — Standard Product Unit, e.g. "Nike AirMax 2024") from the purchasable variant (SKU — Stock Keeping Unit, e.g. "Nike AirMax Red Size 42"). Store SKU attributes as JSON columns rather than EAV for better query performance.
Inventory Control
Track total_stock, locked_stock (paid but not shipped), and sold_stock separately. Use atomic UPDATE ... WHERE (total - locked - sold) >= qty to prevent overselling. For flash sales, pre-deduct in Redis with a Lua script, then create the order asynchronously.
Order Design
Key principles: snapshot the delivery address and product name/price at order creation time — never rely on joins to current data for historical orders. Partition the order table by year. Use a separate order_item table for line items.
Index Summary
| Query | Index |
|---|---|
| User order list | (user_id, status, created_at) |
| Expired unpaid orders | (status, expire_time) |
| Product by category | (category_id, status) |
| Payment deduplication | txn_no UNIQUE |