Skip to content
DiaGO

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
Open in Studio (class diagramwith this project's brief)Class diagram · the steps stay beside your canvas

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:

ObjectPersistent?Why
CustomerYesOrders reference this customer for years; identity must survive restarts
PaymentYesFinancial 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:

orderIdorderDatecustomerNamecustomerEmailproductNamequantityunitPrice
10012026-03-02Ana Reyesana@example.comBurr Grinder189.00
10012026-03-02Ana Reyesana@example.comGooseneck Kettle245.00
10022026-03-05Ana Reyesana@example.comBurr Grinder195.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 elementRelational mappingNotes
Customer classcustomer tableObject identity becomes the primary key
Order -> Customer associationorders.customer_id FKMany-to-one maps to an FK on the many side
Order-OrderLine compositionorder_line, PK (order_id, line_no), ON DELETE CASCADEComposition 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_purchase is not denormalization?
  • Does your mapping table account for every class, attribute, and association in the Studio diagram?

Going further

  • Add a product_price history table with valid_from and valid_to columns so the shop can answer what any product cost on any date - then decide whether order_line still 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.