E-commerce ERD: preserve history and avoid multiplied totals
Build a small order-history lab, change a catalog price, and reproduce a double-counting bug when joining order items and payments.
An online store is a useful model because it separates several kinds of facts that happen at different times. A customer has a current account profile. A product has a current name and list price. An order records a historical agreement: what was bought, at what price, by whom, and where it was to be delivered.
Start with customers, products, orders, and order_items. One customer can place zero or many orders; each order belongs to one customer. One order has one or more items, and each item refers to one product. Put order_id on order_items, not repeated order columns on every item row.
Keep product_id on an order item, but also snapshot product_name and unit_price. A later price change must not rewrite an old receipt. The duplicated values are intentional because a catalog fact and a transaction fact answer different questions.
Payments and shipments deserve their own tables when they can have an independent lifecycle. A payment may be approved, partly refunded, or retried. A shipment may be created after payment and may cover only some items. A simple first version can use payments.order_id and shipments.order_id; add shipment items when partial fulfillment becomes real.
Define what changes and what must stay fixed
Order 1001 contains two keyboards at 30,000 KRW each and one mouse at 10,000 KRW. Its agreed total is 70,000 KRW. After checkout, increase the keyboard's current catalog price to 35,000 KRW. The receipt must still show 70,000 KRW even though buying the same quantities today would cost 80,000 KRW.
Download the complete order-history SQL lab. Use an empty practice database and run the file once. It targets MySQL 8.0.16+ with InnoDB, or SQLite 3.8.3+ with foreign keys enabled. Amounts are integer KRW. The data is fictional and intentionally excludes taxes, discounts, refunds, and concurrent writes.
Reproduce the fan-out bug before fixing it
Record two payments of 40,000 and 30,000 KRW. Joining the two order-item rows directly to both payment rows creates four joined rows. Summing item prices in that result returns 140,000 KRW. The relationships are valid, but the query has multiplied two independent child collections.
| Calculation | Expected result |
|---|---|
| Historical item prices | 70,000 KRW |
| Current catalog prices | 80,000 KRW |
| Items joined directly to two payments | 140,000 KRW, incorrect |
| Each collection aggregated before the join | Order 70,000; paid 70,000 KRW |
WITH item_totals AS (
SELECT order_id, SUM(unit_price * quantity) AS order_total
FROM lab_order_items GROUP BY order_id
), payment_totals AS (
SELECT order_id, SUM(amount) AS paid_total
FROM lab_payments GROUP BY order_id
)
SELECT o.order_id, i.order_total, COALESCE(p.paid_total, 0) AS paid_total
FROM lab_orders o
JOIN item_totals i ON i.order_id = o.order_id
LEFT JOIN payment_totals p ON p.order_id = o.order_id;Add lifecycle rules before expanding the model
The LEFT JOIN keeps orders without payments and reports zero paid. The inner join to item totals assumes every confirmed order has an item. If the screen also lists empty drafts, make that join optional too and decide how a draft total is displayed.
Partial refunds require more facts than this lab contains. Preserve successful charges and completed refunds separately, decide how failed or pending attempts affect the balance, and group each collection before comparing totals. Keep payment-provider transaction references unique at the appropriate scope to avoid counting retries twice. A historical snapshot solves price changes; it does not solve payment idempotency or inventory concurrency.
Open the ecommerce sample in YourERD to inspect the larger customer, product, and order structure. The downloadable lab is a smaller teaching schema, so its lab_ table names and omitted entities are intentional. Extend it only after writing the new invariant and a query that can check it.
Reference and next step
Examples use simplified business rules for teaching. Check the database documentation for the exact enforcement behavior of your chosen engine.
Open the ecommerce sample in YourERD · Explore the sample models