ER Design Project: E-commerce System

Requirement

An online shop sells products. Each product has a code, a name, a description, a current price and a stock quantity, and belongs to exactly one category. Categories may have parent categories, forming a hierarchy.

Customers register with a customer number, a name, an email address and a password, and may store several delivery addresses, one of which is marked default.

A customer has one shopping cart holding several products with quantities. Placing an order converts the cart into an order.

An order records an order number, an order date, a status, the delivery address used and the total. Each order line records the product, the quantity ordered and the price charged.

An order is paid by one or more payments, each with a date, an amount, a method and a status. Customers may review a product they have bought, giving a rating and a comment, once per product.

Step 1 — Entities

Step 2 — Relationships

Step 3 — The relational schema

  categories ( cat_id PK, cat_name NOT NULL
             , parent_id NULL -> categories )   -- recursive

  products   ( product_code PK, product_name NOT NULL
             , description, price NOT NULL, stock NOT NULL
             , cat_id NOT NULL -> categories )

  customers  ( cust_no PK, cust_name NOT NULL
             , email UNIQUE NOT NULL
             , password_hash NOT NULL )

  addresses  ( cust_no -> customers ON DELETE CASCADE
             , address_no
             , line1 NOT NULL, city, pincode
             , is_default NOT NULL DEFAULT 0
             , PK ( cust_no, address_no ) )

  cart_items ( cust_no -> customers, product_code -> products
             , quantity NOT NULL, added_on NOT NULL
             , PK ( cust_no, product_code ) )

  orders     ( order_no PK
             , cust_no NOT NULL -> customers
             , ordered_on NOT NULL
             , status NOT NULL
             , ship_cust_no, ship_address_no
             , total NOT NULL
             , FK ( ship_cust_no, ship_address_no )
                  -> addresses )

  order_lines( order_no -> orders ON DELETE CASCADE
             , line_no
             , product_code NOT NULL -> products
             , quantity NOT NULL
             , unit_price NOT NULL      -- the PRICE CHARGED
             , PK ( order_no, line_no ) )

  payments   ( payment_id PK
             , order_no NOT NULL -> orders
             , paid_on NOT NULL, amount NOT NULL
             , method NOT NULL, status NOT NULL )

  reviews    ( cust_no -> customers, product_code -> products
             , rating NOT NULL, comment, reviewed_on NOT NULL
             , PK ( cust_no, product_code ) )   -- once per product

  9 tables.

Step 4 — The two decisions that matter

1. The order line stores the price

  If order_lines did NOT store unit_price:

    a customer buys a shirt at 800 in March
    the shop raises the price to 950 in April
    the March invoice now reads 950

  Every historical order silently changes whenever a price
  changes. Refunds, accounts and tax records are all wrong.

  RULE: an order line records what was CHARGED, not a
  reference to what the price IS. This is deliberate,
  correct redundancy - the value is a historical fact,
  not a duplicate.

The same reasoning applies to the delivery address. If an order only referred to an address the customer can later edit, the record of where a parcel was actually sent would change retrospectively. In practice, mature designs copy the address on to the order for exactly this reason.

2. The cart is not an order

A cart is a working set: it may sit for weeks, its prices are current, and it holds no history. An order is a fixed record of a completed decision. Merging them means either orders that change or carts that cannot be edited.

Step 5 — Stock and concurrency

Two customers buy the last item at the same moment. Reading the stock, deciding it is enough, and writing the decremented value is exactly the lost update from Phase 1.

  customer A            customer B
  read stock -> 1
                        read stock -> 1
  both see one available
  A writes 0
                        B writes 0

  Two items sold. One existed.

  The fix is not in the schema. It is a transaction that
  locks the product row, checks and decrements in one
  atomic step. Phases 9 and 10 cover exactly this.

Verification questions

1. What did one order contain, at the prices actually charged?
2. Is an order fully paid, given several partial payments?
3. Which products are out of stock but present in carts?
4. What is the average rating of a product, and how many reviews?
5. What are all the subcategories of a top level category?

Common mistakes

• Omitting unit_price from the order line and rewriting history with every price change.
• Deleting cart rows on checkout without copying them into order lines.
• Assuming one payment per order, which breaks partial payments and retries.
• Allowing several reviews per customer per product by using a surrogate key with no uniqueness constraint.
• Storing a product category name on the product rather than a foreign key.
• Storing a password rather than a password hash. Store only a hash, always.

Practice

1. Add discount coupons applied at order level, and decide whether the discount belongs on the order or the order line.
2. Add a shipment entity where one order may ship in several parcels.
3. Write the condition that finds orders whose payments do not add up to the total.

Conclusion

Nine tables, one weak entity, one recursive hierarchy and two decisions worth remembering: an order line stores the price it charged, and a cart is not an order. Both are cases where copying a value is correct because the value is a historical fact.