DesignBeginner
Design an E-Commerce Database
A shop with a catalogue, carts, orders and payments. Design the schema from the access patterns, decide what to normalise and what to duplicate on purpose, and name the indexes each pattern needs.
Requirements
- Customers browse products by category and search by name.
- A customer places an order containing several products with quantities.
- An order records the price paid, which must not change if the catalogue price changes later.
- An order is paid by one or more payment attempts and may be refunded.
- Show a customer their orders, newest first; show what is in an order.
- Report revenue per month and best-selling products.
Access patterns
These drive the whole design.
| Pattern | Frequency | Note |
|---|---|---|
| Find a product by id / list products in a category | constant | Point lookup and a 1-N list; index products.category_id. |
| A customer’s orders, newest first | constant | The list view; composite index (user_id, created_at DESC). |
| What is in this order | constant | Read the junction table by order_id. |
| Revenue per month | hourly, may take seconds | Aggregate over orders; a rollup table if it grows. |
| Best-selling products | daily | Aggregate over order_items; may scan. |
Sketch your entities, keys and indexes from the access patterns above, then reveal the reference design.