Design an E-commerce Database (Relational)

Design a relational database schema for an e-commerce platform that handles products, orders, customers, and inventory management.

Functional Requirements

• Store products with categories, variants (size, color), and pricing
• Manage customer accounts and addresses
• Track orders, order items, and payment status
• Handle inventory levels and stock reservations
• Support promotions, discounts, and coupon codes
• Store product reviews and ratings

Non-Functional Requirements

• Support millions of products and customers
• Handle thousands of concurrent orders during peak sales
• Maintain data consistency for inventory and payments
• Enable efficient reporting and analytics queries

Questions to Consider

• How will you model product variants efficiently?
• What strategy will you use for inventory management during checkout?
• How do you handle price history and audit trails?