A Simple Database Design Example

Now combine tables, keys, foreign keys, and constraints in a small online store. The system must store users, products, orders, and the products inside each order. It uses users, products, orders, and order_items.

This differs from the earlier simplified model: product_id no longer sits directly in orders. A real order may contain several products, so order_items records which product belongs to which order and in what quantity.

users: account details

id identifies the user; name and email are required; email is also unique so two accounts cannot share it.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(200) NOT NULL UNIQUE
);

products: official product data

A product requires a name and exact price; category may remain unknown for now.

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  category VARCHAR(100)
);

orders: who ordered and when

orders records the user, date, and status. user_id references users.id, so each order belongs to a real user; status defaults to pending.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT NOT NULL,
  order_date DATE NOT NULL,
  status VARCHAR(50) DEFAULT 'pending',
  FOREIGN KEY (user_id) REFERENCES users(id)
);

order_items: each product inside an order

Each row represents one product in an order. order_id points to orders, product_id points to products, and quantity records how many. The same order_id can appear in many rows, allowing any number of products.

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

The three line items in order 1001

How the four tables connect

One user can have many orders; one order can have many order items; one product can appear in many order items. Each kind of official information stays in its own table.

Why not add product_1 and product_2 to orders?

The next order may contain four products, another ten. Repeatedly adding columns creates an awkward structure that is hard to query. order_items represents changing quantities with rows: whether an order has one product or one hundred, the schema remains unchanged. Do not add columns merely because the number of records changes.

JOIN reconstructs the full order

Separate storage does not make the data unreadable. Joining all four tables returns the order ID, user, product, quantity, and price.

SELECT o.id AS order_id, u.name AS user_name,
       p.name AS product_name, oi.quantity, p.price
FROM orders AS o
INNER JOIN users AS u ON o.user_id = u.id
INNER JOIN order_items AS oi ON o.id = oi.order_id
INNER JOIN products AS p ON oi.product_id = p.id;