Project 1: E-commerce Order System

What you're building#

A small but realistic backend database for an online shop: customers, products, orders, items, and an audit trail. The goal is a schema that protects itself and an order flow that stays correct under concurrent buyers.

Requirements#

1. Tables: customers, addresses, products, orders, order_items, payments, price_history.
2. Money uses DECIMAL. Order status is one of new, paid, shipped, cancelled.
3. Constraints: unique email, non-negative price and stock, positive quantity, foreign keys with RESTRICT deletes.
4. Order items store a snapshot of the unit price.
5. Placing an order must be atomic and must never oversell stock.
6. Changing a product price writes a row to price_history.
7. Reporting views for order totals and revenue per day.
8. Indexes justified by EXPLAIN on at least three queries.

Step 1: Schema#

Write schema.sql. Name every constraint. Include created_at and updated_at. Decide, and write down, why each foreign key uses the delete rule it does.

Step 2: Seed data#

Write seed.sql with at least 10 customers, 20 products across 4 categories, and 30 orders. Include edge cases: a customer with no orders, a product with zero stock, an order with a cancelled status.

Step 3: Place an order safely#

Build a procedure with this shape:

CALL sp_place_order(customer_id, product_id, qty, @new_order_id);

Inside it: start a transaction, create the order, insert the item copying the current price, run UPDATE products SET stock = stock - qty WHERE id = ? AND stock >= qty, check ROW_COUNT(), and SIGNAL an error and roll back if no row changed. Add an exit handler so any error rolls back.

Test oversell protection with two terminals buying the last unit at the same moment. Exactly one must succeed.

Step 4: Triggers and views#

Add the price audit trigger from Module 6. Create v_order_totals and v_daily_revenue, excluding cancelled orders.

Step 5: Reporting queries#

Write and save these queries:

• Top 5 customers by lifetime spend, with their rank.
• Products never sold.
• Each customer's time between their last two orders.
• Month-over-month revenue change using LAG.

Step 6: Performance pass#

Generate 200,000 orders with a recursive CTE or a script. Run EXPLAIN ANALYZE on your reports. Add indexes where the plan shows a scan, and record before and after row estimates and timings in NOTES.md.

Step 7: Security and backup#

Create a migration user, an app user, and a read-only analyst user with the privileges from Module 7. Dump the database, restore into a new one, and compare row counts.

Acceptance checklist#

• All constraints reject bad data with the expected error.
• Concurrent last-unit purchase leaves stock at 0, never negative.
• Reports return correct totals, checked against hand-calculated numbers on the seed data.
• Every index is justified by a query.
• The restore matches the original.

Common pitfalls#

• Reading the product price live for historical totals instead of using the snapshot.
• Updating stock with a read-then-write in application code.
• Counting after a join and inflating revenue.

Practice#

Extend the project: add a returns table and a procedure that restocks items and records a negative payment, keeping every report correct. Describe which constraints and triggers you had to adjust.