E-Commerce Order Processing
From an object model to a normalized relational schema - ER modeling, normalization, and class-to-table mapping.
intermediate3-4 hours
What you will produce
- Object model
- ER model
- Normalized relational schema
- Class-to-table mapping
Your client sells specialty coffee equipment online. Customers browse a catalog, place orders containing several products, pay, and then track the shipment until it arrives. The catalog team changes prices weekly - daily during promotions. The requirement the client repeats in every meeting: an order placed last March must show exactly what the customer paid last March, no matter what the catalog says today.
That one sentence will shape your entire data model. In this project you take the shop from an object model to a working relational schema: you draw the class diagram, decide which objects need durable identity, translate the model into an ER diagram, normalize it to third normal form, write the SQL, and document how classes map to tables. The class and ER diagrams are built in DiaGO Studio; normalization and SQL are plain text on paper or in your editor.
Before you start
You should be comfortable with objects and classes and reading multiplicities on class diagrams. Skim ER modeling and normalization now - you will apply both directly.
Open DiaGO Studio's class editor with a blank class diagram for Steps 1 and 2. Keep a text editor (or paper) ready for Steps 3 through 7.
Step 1: Create the object model
In Studio, model six classes: Customer, Order, OrderLine, Product, Payment, and Shipment. Draw the associations with multiplicities before adding any attributes - the relationships carry more design information than the attribute lists.
The key decision is the Order-OrderLine relationship. An order line has no meaning outside its order: it is created with the order, and if the order is deleted, its lines go with it. That lifecycle dependency is exactly what composition expresses, so use a filled diamond on the Order end. By contrast, OrderLine to Product is a plain association - the product exists independently and outlives any order that references it.
Hint: give Order a 1..* multiplicity toward OrderLine (an order with zero lines is not a valid order in this shop), and decide deliberately whether Payment and Shipment are 0..1 or 1 from the order's point of view. An order that is placed but not yet paid argues for 0..1.
Step 2: Identify persistent entities
Not every object in a running system deserves a database row. Ask of each class: does this object have an identity that must survive a restart? If two objects with identical attribute values are still meaningfully different things, and the business needs to find this one again next week, it is a persistent entity. Objects that exist only to do work - a price calculator, a checkout session in progress - are not.
Fill in this table for all six classes; two rows are done for you:
| Object | Persistent? | Why |
|---|---|---|
| Customer | Yes | Orders reference this customer for years; identity must survive restarts |
| Payment | Yes | Financial record; auditors will ask for it long after the process exits |
| Order | ? | ? |
| OrderLine | ? | ? |
| Product | ? | ? |
| Shipment | ? | ? |
This is paper work, but annotate your Studio diagram (a note or a naming convention) to mark the persistent classes.
Step 3: Create the ER model
Translate the class diagram into an ER diagram in DiaGO's ER editor - see ER modeling for the notation, or open the Smart Library data model to see one built with primary keys, foreign keys, and crow's-foot cardinalities. Give each entity a primary key, add a foreign key on the many side of every relationship, and set the crow's-foot cardinality on each end. Most classes become entities directly. Two spots need real thought.
First, Order and Product have a many-to-many relationship: an order contains many products, and a product appears in many orders. Relational databases cannot store many-to-many directly, so you resolve it through an associative entity - and you already have one. OrderLine is that entity. It carries the relationship plus its own attributes, starting with quantity.
Second, the price-history requirement. Suppose OrderLine held only a foreign key to Product and you computed line totals from Product.currentPrice. The moment the catalog team raises a price, every historical order silently changes its total - the March order now reports today's prices. The fix is to capture the price at the moment of purchase:
class OrderLine {
productId: string;
quantity: number;
unitPriceAtPurchase: number; // written once at checkout, never updated
}Step 4: Normalize the schema
Before writing your real schema, practice on a deliberately bad one. The client's previous developer stored everything in one flat OrderReport table:
| orderId | orderDate | customerName | customerEmail | productName | quantity | unitPrice |
|---|---|---|---|---|---|---|
| 1001 | 2026-03-02 | Ana Reyes | ana@example.com | Burr Grinder | 1 | 89.00 |
| 1001 | 2026-03-02 | Ana Reyes | ana@example.com | Gooseneck Kettle | 2 | 45.00 |
| 1002 | 2026-03-05 | Ana Reyes | ana@example.com | Burr Grinder | 1 | 95.00 |
Work it to third normal form using the process in normalization. Identify the functional dependencies first: customerName and customerEmail depend on the customer, not on any order line; orderDate depends on orderId alone, not on the full order-plus-product key. Each violation you find tells you which table to split out. Notice what is not a violation: unitPrice differs between rows 1 and 3 for the same product because it is a per-line fact, not a product fact.
Step 5: Write the relational schema
Now write the DDL for your normalized design, with primary and foreign keys explicit. Here are four of the six tables; write payment and shipment yourself:
CREATE TABLE customer (
customer_id BIGINT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
email VARCHAR(320) NOT NULL UNIQUE
);
CREATE TABLE product (
product_id BIGINT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
current_price NUMERIC(10,2) NOT NULL
);
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customer(customer_id),
placed_at TIMESTAMP NOT NULL
);
CREATE TABLE order_line (
order_id BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
line_no INT NOT NULL,
product_id BIGINT NOT NULL REFERENCES product(product_id),
quantity INT NOT NULL CHECK (quantity > 0),
unit_price_at_purchase NUMERIC(10,2) NOT NULL,
PRIMARY KEY (order_id, line_no)
);Note how the model's composition survives translation: ON DELETE CASCADE on order_line is the relational expression of the shared lifecycle you drew in Step 1.
Step 6: Map classes to tables
Document the mapping so anyone can trace a class-diagram element to its relational home. Complete this table for the whole model; three rows are done:
| Model element | Relational mapping | Notes |
|---|---|---|
| Customer class | customer table | Object identity becomes the primary key |
| Order -> Customer association | orders.customer_id FK | Many-to-one maps to an FK on the many side |
| Order-OrderLine composition | order_line, PK (order_id, line_no), ON DELETE CASCADE | Composition maps to a dependent table with cascading delete |
| OrderLine -> Product association | ? | ? |
| Payment class and its association | ? | ? |
| Shipment class and its association | ? | ? |
Step 7: Explain normalization decisions
Write a short design note (three or four paragraphs) justifying your schema. The question it must answer: is copying the unit price into order_line denormalization?
It is not, and being able to argue why is the point of this project. Denormalization means storing the same fact redundantly - copying customer.name into orders would be denormalization, because there is one fact (the customer's name) held in two places that can drift apart. But product.current_price and order_line.unit_price_at_purchase are two different facts: what the product costs now versus what this customer paid then. There is no redundancy, no single source of truth being violated, and no update anomaly - a change to the current price is supposed to leave historical lines untouched. A schema that could not represent both facts would be wrong, not lean.
Reach for it
- The duplicated value is a historical snapshot that is immutable once written
- A measured read-path cost justifies a deliberate copy of a live fact
- The team documents which table owns the truth and how copies are refreshed
Avoid it
- Copying live facts that change, such as customer names or current prices
- Denormalizing early, before any real performance problem is observed
- Duplicating any value that must stay consistent for correctness
Check your work
- Does your class diagram use composition (filled diamond) for Order-OrderLine and plain associations elsewhere, with a reason for each?
- Can you state, for each of the six classes, why it does or does not need identity that survives a restart?
- Is the Order-Product many-to-many resolved through OrderLine in the ER model, with quantity and unit price on the associative entity?
- Does every table in your DDL have an explicit primary key, and every association a foreign key or junction table?
- If the catalog team doubles a price today, does a query for last March's order total return the March amount?
- Can you explain in two sentences why
unit_price_at_purchaseis not denormalization? - Does your mapping table account for every class, attribute, and association in the Studio diagram?
Going further
- Add a
product_pricehistory table withvalid_fromandvalid_tocolumns so the shop can answer what any product cost on any date - then decide whetherorder_linestill needs its snapshot column (it does; explain why). - Support partial shipments: one order fulfilled by several shipments, each covering some lines. This turns Shipment-OrderLine into its own many-to-many to resolve.
- Design the notification flow for shipment status changes using the Observer pattern, and decide which state changes are worth persisting as events.
Practice
Add returns to the model: a customer may return some lines of a delivered order for a refund. Introduce the classes you need, decide what is persistent, extend the ER model and DDL, and add the new rows to your mapping table. Pay attention to the same historical-fact question: a refund must record the amount actually refunded, even if the original payment is later disputed.