Design project: an online store

Step 1: the requirements

A small brand wants its own store website, selling groceries, kitchenware and electronics across India. After talking to the owner, you write these sentences:

1. Customers sign up with a name and a unique email. They can save several delivery addresses.
2. Products belong to one category and have a price. Prices change during sales.
3. An order belongs to one customer, is delivered to one of their addresses, and contains one or more products with quantities.
4. An order moves through statuses: Placed, Paid, Shipped, Delivered, or Cancelled.
5. A customer pays by UPI, card or cash on delivery. A payment can fail and be retried.
6. Customers rate products from 1 to 5 stars, at most once per product, with an optional comment.

Step 2: entities and relationships

customers  ||--o< addresses        a customer saves zero or many addresses
customers  ||--o< orders           a customer places zero or many orders
addresses  ||--o< orders           an order ships to one address
categories ||--o< products         a product is in one category
orders     ||--|< order_items      an order has one or many lines
products   ||--o< order_items      a product appears in many lines
orders     ||--o< payments         an order can have several payment attempts
products   ||--o< reviews >o--|| customers   a review links one product and one customer

Step 3: tables, keys and constraints

CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  category_id INTEGER NOT NULL REFERENCES categories(id),
  name        TEXT NOT NULL,
  price_paise INTEGER NOT NULL CHECK (price_paise > 0)
);
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  address_id  INTEGER NOT NULL REFERENCES addresses(id),
  status      TEXT NOT NULL CHECK (status IN ('Placed', 'Paid', 'Shipped', 'Delivered', 'Cancelled')),
  created_at  TEXT NOT NULL            -- UTC
);
CREATE TABLE order_items (
  order_id         INTEGER NOT NULL REFERENCES orders(id),
  product_id       INTEGER NOT NULL REFERENCES products(id),
  qty              INTEGER NOT NULL CHECK (qty > 0),
  unit_price_paise INTEGER NOT NULL,   -- the price paid, copied at order time
  PRIMARY KEY (order_id, product_id)
);
CREATE TABLE reviews (
  id          INTEGER PRIMARY KEY,
  product_id  INTEGER NOT NULL REFERENCES products(id),
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  rating      INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  body        TEXT,
  UNIQUE (product_id, customer_id)     -- one review per product per customer
);

The design decisions, and why

• Money is integer paise everywhere (topic 4), so totals and payments match exactly.
• order_items copies unit_price_paise, because "the price paid" is a different fact from today's price (topic 2). A sale next month must not change old bills.
• payments is its own table, not columns on orders, because one order can have a failed UPI attempt and then a successful card payment.
• Requirement 6 becomes UNIQUE (product_id, customer_id) on reviews: the rule lives in the database, not only in the app.
• addresses are treated as append-only: editing an address creates a new row, so an old order still points to where it really went. Many real stores go further and copy the shipping address into the order.
• Every status and rating is limited by CHECK, every foreign key is NOT NULL because each link is required, and the email is UNIQUE.

Step 4: test the design with real questions

A design is not finished when the tables exist. It is finished when it answers the business questions simply. Write the reports the owner will want and run them against sample data:

• Revenue per category for paid, shipped and delivered orders.
• Average rating and number of reviews per product, including products with no reviews.
• Orders whose payments do not cover the order total (to chase or cancel).
• Orders per delivery city, to plan courier partners.

SELECT c.name AS category,
       printf('%.2f', SUM(oi.qty * oi.unit_price_paise) / 100.0) AS revenue_rupees
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
JOIN categories c   ON c.id = p.category_id
WHERE o.status IN ('Paid', 'Shipped', 'Delivered')
GROUP BY c.id, c.name
ORDER BY c.name;
-- Electronics | 1999.00
-- Grocery     | 1780.00
-- Kitchen     | 1450.00

The next module adds indexes to this design: on every foreign key used in joins (orders.customer_id, order_items.product_id), and on the columns the reports filter by, such as orders.status and orders.created_at.

Common mistakes

• Reading the product's current price for old orders. Fix: copy the price into order_items.
• A payment_status column on orders instead of a payments table. Fix: a table, so every attempt is recorded.
• Forgetting rules the business stated ("one review per customer"). Fix: turn every "at most" or "must" sentence into a constraint.
• Never testing the design with queries. Fix: write the top five reports before you call the schema done.