Online Shopping System
An order line is an entity, not an attribute of a many-to-many: it has its own attributes, its own key and its own price frozen at the moment of the order.
Intermediate
The diagram
Opening an example creates a copy in your own projects. The original example is never modified.
The problem
An online shop sells products from a catalogue and ships orders to registered customers. A customer browses the catalogue, places an order, and the order contains one or more lines: three units of one product, one unit of another. Each line records how many units were bought and the price of a single unit at the moment the order was placed. Prices in the catalogue change — promotions end, suppliers raise their costs — but the total of an order placed last March must remain the total it had in March. Customers may register and never order anything, and products may sit in the catalogue for months before their first sale. The shop also needs to answer, for any product, which orders mention it, and, for any order, what it contained and what it cost.
Requirements
- The system must record customers with a unique customer number, an e-mail address and a full name.
- The system must record the products of the catalogue, each with a unique SKU, a name and the current unit price.
- The system must record orders, each with a unique order number, the date on which it was placed and its status.
- The system must record, for every order, the lines it contains, each with the product, the quantity and the unit price applied.
- A customer may place zero, one or many orders, and every order is placed by exactly one customer.
- Every order contains at least one line, and every line belongs to exactly one order.
- A product may appear in zero, one or many order lines, and every line refers to exactly one product.
- The unit price stored on a line is the price applied when the order was placed and must not change when the catalogue price changes.
Entities and attributes
| Entity | Attributes | Identifier |
|---|---|---|
| Customer | customer_no, email, full_name | customer_no |
| Order | order_no, order_date, status | order_no |
| Product | sku, name, unit_price | sku |
| OrderItem | quantity, unit_price | (order_no, sku) |
Relations and cardinalities
| Relation | From | To | Cardinality (min, max) | Note |
|---|---|---|---|---|
| Places | Customer | Order | (0,N) – (1,1) |
A customer may place zero, one or many orders: registering is not ordering, and a customer who has not bought anything yet is a normal record. An order is placed by exactly one customer, so the minimum on the Order side is 1 and the foreign key customer_no sits on Order, never the other way round. |
| Contains | Order | OrderItem | (1,N) – (1,1) |
Every order contains at least one line: the minimum is 1 because an order with no lines is not a document the shop would keep. Every line belongs to exactly one order, so OrderItem carries order_no as part of its key. |
| RefersTo | Product | OrderItem | (0,N) – (1,1) |
A product may appear in zero, one or many lines over time: a new product has never been sold. Every line refers to exactly one product, which is why Product can be asked which orders mention it — a question a many-to-many association attribute could not answer as cleanly. |
Reading the diagram
- The diagram has four entities: Customer, Order, Product and OrderItem. Order is a document with a date and a status, Product is a catalogue record with a current price, and OrderItem is one line of the document. Keeping the document and its lines apart is what makes the example work.
- Places is one-to-many: the (0,N) on the Customer branch says that a customer places zero, one or many orders, and the (1,1) on the Order branch says that every order is placed by exactly one customer. The foreign key goes on the many side, so Order holds customer_no.
- Order and Product are not joined directly. A direct many-to-many would have to carry quantity and price on the association, and that association would immediately need an identity, a name and its own queries: which lines mention this product, which lines were discounted, which were shipped. The moment you say that, the association has become the entity OrderItem.
- Contains is one-to-many from Order to OrderItem: (1,N) on the Order branch because an order has at least one line, (1,1) on the OrderItem branch because a line belongs to exactly one order. The order total is computed over these lines and is never stored as a column of Order.
- RefersTo is one-to-many from Product to OrderItem: (0,N) on the Product branch because a product may never have been ordered, (1,1) on the OrderItem branch because a line always points at exactly one product. Notice that this relation, and not the key, is what tells us which products are inside an order.
- There are two attributes called unit_price, and they are not the same fact. Product.unit_price is today's catalogue price and it changes; OrderItem.unit_price is the price applied when the order was placed and it must never change. The line total is quantity times OrderItem.unit_price, and an old order is never recomputed by joining back to Product.
- The diagram gives OrderItem no key attribute of its own: quantity and unit_price are not identifiers. Its identity comes from the two relations that reach it, so the relational schema uses (order_no, sku) as the primary key. That choice also states a business rule — one line per product per order — which is exactly the kind of rule a designer should make explicit rather than leave implicit.
From the conceptual model to the logical schema
Customer(customer_no, email, full_name) PRIMARY KEY (customer_no) Order(order_no, customer_no, order_date, status) PRIMARY KEY (order_no) FOREIGN KEY (customer_no) REFERENCES Customer(customer_no) Product(sku, name, unit_price) PRIMARY KEY (sku) OrderItem(order_no, sku, quantity, unit_price) PRIMARY KEY (order_no, sku) FOREIGN KEY (order_no) REFERENCES Order(order_no) FOREIGN KEY (sku) REFERENCES Product(sku)