Case Study: Designing an Order System

The requirements#

A product manager hands you a one-paragraph brief for a small e-commerce platform's order system:

• Customers create accounts with an email and password, and can save multiple shipping addresses.
• Customers browse a catalog of products. Every product belongs to exactly one category and has a stock level that goes up and down.
• A customer places an order containing one or more products, at whatever quantity and price applied at the moment of purchase.
• Every order ships to exactly one address — a copy of one of the customer's saved addresses, as it looked when the order was placed.
• Orders move through a fixed set of statuses: pending, paid, shipped, delivered, cancelled.
• Admins need to run monthly revenue reports broken down by product category.

This page walks through turning that brief into a real schema, using the same three-step process from the introduction page in this section — entities, relationships, constraints — and applying the normalization rules from the normalization page along the way.

Step 1: identify the entities#

Reading the brief for nouns that represent a distinct "thing" the system needs to remember: customers, addresses, products, categories, orders, and — because "an order contains one or more products, at whatever quantity and price applied at the time" is exactly the many-to-many-with-its-own-attributes pattern from the introduction page — order line items.

Step 2: identify the relationships#

• Customers → Addresses: one customer can save many addresses (one-to-many). Modeled with a foreign key on addresses.customer_id.
• Categories → Products: one category contains many products, but each product belongs to exactly one category (one-to-many). Modeled with a foreign key on products.category_id.
• Customers → Orders: one customer places many orders (one-to-many). Modeled with a foreign key on orders.customer_id.
• Orders ↔ Products: an order can contain many products, and a product can appear on many different orders (many-to-many). This needs a join table — order_items — carrying quantity and the price actually charged, exactly the "the pairing has its own attributes" signal from the introduction page.
• Orders → Addresses: an order needs shipping address data, but — as the next section explains — this isn't a plain foreign key.

Step 3: the normalization decisions, and where to deliberately break the rules#

Splitting out addresses and categories#

Following straight 3NF reasoning: a customer's name and email shouldn't be repeated on every address row, and a product's category name shouldn't be repeated on every product row. Both get their own tables, referenced by id — this part is unremarkable normalization, identical in spirit to the customers/products split on the normalization page.

CREATE TABLE customers (
    id            INT PRIMARY KEY AUTO_INCREMENT,
    email         VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE addresses (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    line1       VARCHAR(200) NOT NULL,
    city        VARCHAR(100) NOT NULL,
    postal_code VARCHAR(20)  NOT NULL,
    country     VARCHAR(2)   NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE categories (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE products (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    name        VARCHAR(150) NOT NULL,
    price       DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock       INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

The judgment call: shipping address as a snapshot, not a foreign key#

A naive reading of "an order ships to one of the customer's addresses" suggests orders.address_id REFERENCES addresses(id). That's the wrong call here, and it's worth understanding exactly why: a customer can edit or delete a saved address at any time, but an order that already shipped must keep showing the address it actually shipped to, unchanged, forever — a shipping label from March shouldn't silently update itself because the customer moved apartments in June, and it definitely shouldn't break because the customer deleted that old address afterward.

This is precisely the deliberate-denormalization case the normalization page calls out under "historical snapshots" — the same reasoning that justifies capturing a product's price on the order line rather than pointing back at the live, changeable products.price. The fix is copying the address fields directly onto the order at the moment it's placed, rather than referencing the addresses table at all:

CREATE TABLE orders (
    id                    INT PRIMARY KEY AUTO_INCREMENT,
    customer_id           INT NOT NULL,
    status                VARCHAR(20) NOT NULL DEFAULT 'pending',
    shipping_line1        VARCHAR(200) NOT NULL,
    shipping_city         VARCHAR(100) NOT NULL,
    shipping_postal_code  VARCHAR(20)  NOT NULL,
    shipping_country      VARCHAR(2)   NOT NULL,
    created_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled'))
);

(CHECK (status IN (...)) is a portable stand-in here for MySQL's ENUM or a small lookup table — any of the three works; the point is the database, not just application code, rejects an invalid status.)

order_items: the many-to-many join table, with its own snapshot#

order_items resolves the many-to-many between orders and products, and — for exactly the same reason as the shipping address — captures unit_price at the moment of purchase rather than joining back to the live products.price on every read:

CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Step 4: indexing and transaction boundaries#

Applying the indexing-strategy page's guidance directly: every foreign key here (addresses.customer_id, products.category_id, orders.customer_id, both columns of order_items) is a strong index candidate, since every one of them will be joined on constantly (a customer's order history, a category's product listing, an order's line items):

CREATE INDEX idx_addresses_customer   ON addresses (customer_id);
CREATE INDEX idx_products_category    ON products (category_id);
CREATE INDEX idx_orders_customer      ON orders (customer_id);
-- order_items' composite primary key (order_id, product_id) already covers
-- "this order's items"; a second index on product_id alone supports
-- "which orders contain this product" for the reporting side.
CREATE INDEX idx_order_items_product  ON order_items (product_id);

Placing an order is a multi-step write — insert the order, insert one or more order_items rows, and decrement each product's stock — that only makes sense as one atomic unit, exactly the transaction-boundary reasoning from the transactions-and-acid page: a crash after the order row commits but before stock is decremented would oversell inventory that was never actually reserved.

START TRANSACTION;

INSERT INTO orders (customer_id, status, shipping_line1, shipping_city, shipping_postal_code, shipping_country)
VALUES (1, 'pending', '12 Rue de Rivoli', 'Paris', '75001', 'FR');

-- capture the new order's id, e.g. via LAST_INSERT_ID() in application code

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 1, 2, 24.99);

UPDATE products SET stock = stock - 2 WHERE id = 1 AND stock >= 2;

-- if the UPDATE above affected 0 rows, stock ran out concurrently:
-- ROLLBACK instead of COMMIT

COMMIT;

The WHERE ... AND stock >= 2 guard, checked against the row count the UPDATE actually affects, is the standard defense against two customers simultaneously buying the last two units of a low-stock item — covered in more depth as a general pattern on the transactions-and-acid page's common mistakes.

The monthly revenue-by-category report#

The report the brief asked for is a straightforward join across the schema now that it exists:

SELECT c.name AS category, DATE_FORMAT(o.created_at, '%Y-%m') AS month,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o     ON oi.order_id = o.id
JOIN products p   ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE o.status IN ('paid', 'shipped', 'delivered')
GROUP BY c.name, DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY month, revenue DESC;

Notice this query deliberately reads oi.unit_price, not p.price — the snapshot decision from Step 3 pays off directly here, since a report run today about last month's revenue must reflect what customers actually paid last month, not whatever the product happens to cost right now. If this report starts running often enough on a large order_items table to matter, it's also the textbook candidate for the denormalized-reporting-table pattern covered on the next page.

Common mistakes#

• Modeling the shipping address as a plain foreign key to addresses, then discovering that editing or deleting a saved address silently rewrites (or breaks) the shipping details on every past order that used it.
• Joining order_items back to products.price for revenue reporting instead of storing unit_price on the line item itself — this makes historical reports silently wrong the moment any product's price ever changes.
• Skipping the stock >= quantity guard on the inventory decrement, letting two concurrent orders both succeed against the last unit of stock and pushing it negative.
• Treating this kind of schema as "finished" after the first pass — the shipping-address and price-snapshot decisions only become obvious once you trace through what happens later (an edited address, a price change), which is exactly why walking through concrete scenarios during design catches problems a pure entity/relationship diagram won't.