Design the schema for a simple e-commerce store (customers, products, orders).
Answer
customers(id PK, email UNIQUE, name, created_at)products(id PK, sku UNIQUE, name, price_cents, stock)orders(id PK, customer_id FK, status, total_cents, created_at)order_items(order_id FK, product_id FK, quantity, unit_price_cents, PK(order_id, product_id)): copy the price at purchase time.- Money as integer cents or DECIMAL, never float.
- Indexes:
orders(customer_id, created_at) and the foreign keys. - Decrement stock in a transaction with a check (
UPDATE ... WHERE stock >= qty) to avoid overselling. - Mention soft deletes, timestamps, and status as an enum/state machine.
Practice this question in Repaso
