E-commerce Schema

Model products, customers, orders, and order items relationally.

NormalizationJoinsIndexes

Overview

An order is not one flat record — it is a header (who ordered, when, what status) plus a variable-length list of line items (which products, how many, at what price). Cramming both into a single `orders` table would mean either a fixed number of "product1, product2, product3" columns (which caps every order at 3 items and wastes space on smaller orders) or a comma-separated list in one column (which breaks every SQL feature that expects one atomic value per column, from `WHERE product_id = 5` to `SUM(quantity)`). This project builds the normalized alternative: a separate `order_items` table, where each row is exactly one product on exactly one order.

By the end of this tutorial you will have a four-table schema — `customers`, `products`, `orders`, and `order_items` — with foreign keys tying orders back to customers and order items back to both their order and their product, an index on a column that a real storefront would query constantly, and the join queries that reconstruct a full order (and its total) from the normalized pieces.

What You'll Build

A `customers` table with a unique email constraint.
A `products` table with `CHECK` constraints guarding price and stock against negative values.
An `orders` table holding one row per order, with a `status` column and a foreign key to `customers`.
An `order_items` table normalizing the one-to-many relationship between an order and its products.
An index on `order_items.product_id` for fast "how many times has this product sold" lookups.
Join queries computing an order's total and reporting best-selling products.

Prerequisites

CREATE TABLE basics — columns, data types, `NOT NULL`, and `DEFAULT` values.
Primary keys and foreign keys — how `FOREIGN KEY ... REFERENCES` links two tables.
Basic SELECT — `WHERE`, `ORDER BY`, and simple `JOIN`.
Aggregate functions at a glance — `SUM()` and `COUNT()` with `GROUP BY`.
The general idea of normalization — splitting data into tables to avoid repeating values.

Project Structure

`customers` and `products` are independent entities. `orders` depends on `customers` (every order belongs to exactly one customer). `order_items` depends on both `orders` and `products` — it is the normalized bridge that lets one order contain any number of products, and lets one product appear on any number of orders, without repeating data anywhere.

Table	Purpose	Key Columns
customers	One row per customer account	customer_id (PK), email (UNIQUE)
products	One row per product in the catalog	product_id (PK), sku (UNIQUE), price, stock_quantity
orders	One row per order placed	order_id (PK), customer_id (FK), status
order_items	One row per product on an order	order_item_id (PK), order_id (FK), product_id (FK), quantity, unit_price

Step 1: Design the Customers Table

Nothing unusual here yet — a surrogate `customer_id` primary key, and a `UNIQUE` constraint on `email` so two accounts can never share a login.

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,   -- Surrogate key referenced by orders.customer_id
    full_name VARCHAR(100) NOT NULL,               -- Display name for this customer
    email VARCHAR(100) NOT NULL UNIQUE,             -- UNIQUE so no two accounts share a login
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP   -- Automatically stamped when the row is inserted
);

Step 2: Design the Products Table

Two `CHECK` constraints protect the columns that money and inventory math depend on: `price >= 0` and `stock_quantity >= 0`. Without them, a buggy discount calculation or an unchecked decrement could quietly write a negative price or a negative stock count, and every downstream query that assumes non-negative values would silently produce nonsense.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,      -- Surrogate key referenced by order_items.product_id
    sku VARCHAR(20) NOT NULL UNIQUE,                -- Stock keeping unit — the human-facing catalog identifier
    name VARCHAR(150) NOT NULL,                     -- Product display name
    price DECIMAL(10,2) NOT NULL,                   -- DECIMAL, not FLOAT, so money never suffers rounding error
    stock_quantity INT NOT NULL DEFAULT 0,          -- Units currently available
    CHECK (price >= 0),                             -- A product can never have a negative price
    CHECK (stock_quantity >= 0)                     -- Stock can never go below zero, no matter what decremented it
);

Step 3: Design the Orders Table

`orders` is deliberately just the header information: who placed it, when, and its current status. It says nothing about *what* was ordered — that is what `order_items` is for, in the next step. The `status` column uses `ENUM`, which restricts the value to a fixed, named set at the database level, catching a typo like `'shiped'` before it is ever written.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,        -- Surrogate key referenced by order_items.order_id
    customer_id INT NOT NULL,                       -- FK: which customer placed this order
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending','shipped','delivered','cancelled') NOT NULL DEFAULT 'pending',  -- Fixed set, catches typos
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT   -- Can't delete a customer with existing orders
);

Step 4: Design the Order Items Table

This is the table that makes the whole schema normalized: one row per product on one order, with its own `quantity` and `unit_price`. `unit_price` deliberately duplicates the price already stored on `products` — but that duplication is intentional, not a normalization mistake. It is a snapshot of what the product cost *at the moment this order was placed*; if `products.price` changes next week, every past order must still show the price the customer actually paid, and only a stored snapshot on `order_items` can guarantee that.

CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,   -- Own surrogate key for this specific line item
    order_id INT NOT NULL,                          -- FK: which order this line item belongs to
    product_id INT NOT NULL,                        -- FK: which product this line item is for
    quantity INT NOT NULL,                          -- How many units of the product were ordered
    unit_price DECIMAL(10,2) NOT NULL,              -- Snapshot of products.price at the moment of purchase, not a live lookup
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,       -- Deleting an order removes its line items
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT, -- Can't delete a product still referenced by an order
    CHECK (quantity > 0)                            -- A line item with zero or negative quantity makes no sense
);

Why not just store products in orders?

A single order can contain any number of products, and a single product can appear on any number of orders — that is a many-to-many relationship. `order_items` normalizes it the same way `enrollments` normalized students-and-courses: one row per (order, product) pair, each carrying the details specific to that pairing (quantity, price paid).

Step 5: Add an Index for Frequent Lookups

`order_items.product_id` is exactly the kind of column a real storefront queries constantly: "show me every order that included this product" for a best-sellers report, or "how many units of this SKU have sold total." Without an index, MySQL has to scan every row in `order_items` to answer that; with one, it can jump straight to the matching rows. The foreign key on `product_id` does **not** automatically create this index in every case, so it is added explicitly here.

CREATE INDEX idx_order_items_product_id ON order_items(product_id);
-- Speeds up any query filtering or joining on product_id, e.g. "which orders contained product X"
-- or the best-sellers aggregate query in the Sample Queries section below.

Step 6: Insert Sample Data

As with the student database, insert order matters: `customers` and `products` first (since `orders` and `order_items` reference them), then `orders`, and finally `order_items` last of all.

INSERT INTO customers (full_name, email) VALUES
('Neha Kapoor', 'neha.kapoor@example.com'),
('Arjun Rao', 'arjun.rao@example.com');

INSERT INTO products (sku, name, price, stock_quantity) VALUES
('SKU-KEYB-01', 'Mechanical Keyboard', 79.99, 40),
('SKU-MOUSE-01', 'Wireless Mouse', 29.99, 120),
('SKU-MONITOR-01', '27-inch Monitor', 219.99, 15);

INSERT INTO orders (customer_id, status) VALUES
(1, 'shipped'),
(2, 'pending');

-- unit_price is copied from products.price at insert time — a deliberate snapshot, not a formula
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 79.99),   -- Neha's order: 1 keyboard
(1, 2, 2, 29.99),   -- Neha's order: 2 mice
(2, 3, 1, 219.99);  -- Arjun's order: 1 monitor

Step 7: Join Orders, Items, and Products

This query reconstructs a human-readable order from the normalized pieces: `order_items` supplies the quantity, `products` supplies the name, and multiplying `quantity * unit_price` per row gives a line total.

SELECT
    o.order_id,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_id = 1
ORDER BY p.name;

order_id	product_name	quantity	unit_price	line_total
1	Mechanical Keyboard	1	79.99	79.99
1	Wireless Mouse	2	29.99	59.98

Complete Schema

The full schema, in the order it must be created (each table only references tables that already exist above it).

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    CHECK (price >= 0),
    CHECK (stock_quantity >= 0)
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending','shipped','delivered','cancelled') NOT NULL DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT
);

CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT,
    CHECK (quantity > 0)
);

CREATE INDEX idx_order_items_product_id ON order_items(product_id);

Sample Queries

Two more queries a real storefront runs constantly: an order's grand total, and a best-sellers report powered directly by the index from Step 5.

-- Grand total per order, computed from its line items
SELECT o.order_id, o.status, SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.status
ORDER BY o.order_id;

order_id	status	order_total
1	shipped	139.97
2	pending	219.99

-- Best-selling products by total units sold — filters/aggregates on product_id,
-- exactly the access pattern idx_order_items_product_id was added to speed up
SELECT p.name, SUM(oi.quantity) AS units_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY units_sold DESC;

name	units_sold
Wireless Mouse	2
Mechanical Keyboard	1
27-inch Monitor	1

Extend This Project

Add a `categories` table and a `category_id` foreign key on `products` to support catalog browsing.
Add a `shipping_addresses` table referenced by `orders`, instead of assuming one address per customer.
Add a composite index on `order_items(order_id, product_id)` to speed up per-order lookups further.
Add a `discounts` table and a nullable `discount_id` on `order_items` to model promotional pricing.
Create a `VIEW` called `order_summaries` that pre-joins orders with their computed totals.

Summary

You split what could have been one bloated `orders` table into four normalized tables, with `order_items` doing the real work of representing "many products per order, many orders per product" without duplicating or losing data. Snapshotting `unit_price` onto each line item — rather than joining live to `products.price` every time — is a pattern you will see in almost every real order-history schema, and the index you added on `order_items.product_id` is the kind of targeted optimization that matters once a catalog and order history grow past a handful of rows.