Online Shopping Database Project in MySQL

The project

An online shopping (e-commerce) database is a rich DBMS project. It models customers, products, orders, and the items in each order. It shows both a one-to-many relationship (customer to orders) and a many-to-many one (orders to products), resolved by an order items table.

Designing the tables

• customers — id, name, city.
• products — id, name, price.
• orders — id, customer_id (FK), order_date.
• order_items — id, order_id (FK), product_id (FK), quantity, price.

Each order belongs to one customer. Each order can have many products through order_items.

Creating the schema

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL, city VARCHAR(40)
);
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(80) NOT NULL, price DECIMAL(10,2)
);
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT, order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT, product_id INT,
    quantity INT, price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

The order items link

order_items is the many-to-many link between orders and products. Each row is one product line in an order, with its quantity and the price at purchase time. Storing the price here keeps old order totals accurate even if the product price changes later.

Sample data

INSERT INTO customers (name, city) VALUES ('Aman', 'Delhi'), ('Diya', 'Mumbai');
INSERT INTO products (name, price) VALUES ('Pen', 10.00), ('Notebook', 50.00);
INSERT INTO orders (customer_id, order_date) VALUES (1, '2026-09-20');
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
    (1, 1, 3, 10.00), (1, 2, 2, 50.00);

Sales queries

-- total for one order
SELECT SUM(quantity * price) AS order_total
FROM order_items WHERE order_id = 1;

-- best-selling products
SELECT p.name, SUM(oi.quantity) AS sold
FROM order_items oi JOIN products p ON oi.product_id = p.id
GROUP BY p.id ORDER BY sold DESC;

-- total spending per customer
SELECT c.name, SUM(oi.quantity * oi.price) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id;

Common mistakes

• Putting products directly in orders. Use order_items for many products per order.
• Not storing price in order_items. Then old totals change when a product's price does.
• Forgetting quantity. An order line needs how many were bought.
• No foreign keys. Link items to real orders and products.

Summary

• An e-commerce database has customers, products, orders and order_items.
• customer → orders is one-to-many; orders ↔ products is many-to-many.
• order_items stores each product line with quantity and price.
• Order total = SUM(quantity × price) over its items.
• Query best-sellers and spending with JOIN and GROUP BY.

Next, revise many-to-many in ER diagram examples, or build the library database project.