E-Commerce Orders and Inventory Schema

Project roadmap

1. 01

   Design the schema

   ~2h

   Write schema.sql with customers, categories, products (with a category and a price as numeric(10,2)), inventory (product and quantity on hand, with a check that it's never negative), orders (customer, created time and status) and order_items (order, product, quantity and the unit price at the time of the order). Add primary and foreign keys, and explain in comments why the price is copied into order items. Done when: the schema loads without errors.

2. 02

   Add sample data

   ~1.5h

   Write seed.sql with about 100 customers, 10 categories, 200 products with stock, and a few months of orders with 1 to 5 items each (using generate_series and random() for volume), including some customers with no recent orders. Done when: the data loads, and the relationships are consistent.

3. 03

   Report revenue by category

   ~1.5h

   Write a query joining order items, products and categories to compute revenue per category for a date range (quantity × unit price), with the number of orders and the share of total revenue, and a query for the 10 best-selling products. Count only completed orders. Done when: the totals are correct for the sample data.

4. 04

   Find inactive customers

   ~1h

   Write a query using a subquery (with NOT EXISTS) to find customers with no orders in the last 90 days, with the date of their last order (or none), and compare it with a version using a LEFT JOIN. Done when: both versions return the same customers.

5. 05

   Place orders safely

   ~2h

   Write a PL/pgSQL function place_order(customer, items) that, in one transaction, locks the needed inventory rows with SELECT ... FOR UPDATE (in product id order, to avoid deadlocks), checks there's enough stock for every item, creates the order and its items with current prices, and reduces the stock, raising an error that rolls everything back if any item is short. Test it with two psql sessions buying the last item at the same time. Done when: only one of two simultaneous orders for the last item succeeds, and stock never goes negative.

6. 06

   Add indexes and check plans

   ~1.5h

   Run the reports with EXPLAIN ANALYZE, add indexes on foreign keys and dates used by them (such as orders(customer_id, created_at) and order_items(product_id)), and compare the plans and timings before and after, noting them in README.md. Done when: README.md shows the reports using the new indexes and running faster.

Resources

• DocsPostgreSQL: constraints (foreign keys)
• DocsPostgreSQL tutorial: joins between tables
• DocsPostgreSQL: explicit locking
• DocsPostgreSQL: using EXPLAIN
• DocspgAdmin documentation