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.