Course: MySQL Training
Topic: Developing an E-Commerce Database with Products, Orders, and Customers
Course Link: MySQL Training Course
Demo Link: Register for a Free Course Demo
An e-commerce database is a structured collection of data used to manage the operations of an online shopping platform. It stores information about customers, products, orders, order items, payments, inventory, shipping addresses, and other business activities.
When customers visit an online store, they browse products, add items to a cart, provide delivery details, place orders, and make payments. The database stores and connects this information so that the application can retrieve accurate details and process transactions reliably.
MySQL is commonly used for e-commerce applications because it supports relational tables, primary keys, foreign keys, constraints, indexes, transactions, and SQL queries.
In this project, we will develop a sample e-commerce database using MySQL. The database will manage customers, products, orders, and the individual products included in each order.
Suppose an online store sells laptops, mobile phones, headphones, books, and accessories. A customer named Rahul registers on the website and purchases one laptop and two headphones. The database must store Rahul's details, the product information, the order date, quantities, prices at the time of purchase, and the order total.
By building this project, learners will understand how relational database concepts are applied to a practical business application.
After completing these notes, learners will be able to:
An e-commerce database consists of several related entities. Each entity represents a specific type of information and is generally stored in a separate table.
| Entity | Purpose | Example Information |
|---|---|---|
| Customers | Stores registered customer details. | Name, email, phone number |
| Products | Stores products available for sale. | Name, price, stock quantity |
| Orders | Stores the overall details of customer orders. | Customer, order date, status |
| Order Items | Stores individual products included in each order. | Product, quantity, purchase price |
| Payments | Records payment attempts and payment outcomes. | Amount, method, payment status |
| Categories | Organizes products into groups. | Electronics, Books, Clothing |
| Addresses | Stores customer delivery and billing addresses. | Street, city, postal code |
For the initial project, we will implement the four core tables: customers, products, orders, and order_items. Payments, categories, and addresses can be added as extensions.
Relationships describe how records in different tables are connected. Designing these relationships correctly reduces duplicate data and helps maintain consistency.
One customer can place multiple orders, but each order belongs to one customer in this basic design. The customer identifier is stored in the orders table as a foreign key.
Example: Customer ID 1 can have Order ID 101, Order ID 102, and Order ID 103.
One order can contain multiple order items. Each order item represents one product line within that order.
Example: Order 101 may contain one laptop and two headphones as separate order-item records.
A product can appear in many different orders. The order_items table references the product using product_id.
An order can contain multiple products, and a product can be included in many orders. This is a many-to-many relationship. The order_items table resolves this relationship by connecting orders and products.
| Relationship | Implementation |
|---|---|
| Customers to Orders | orders.customer_id references customers.customer_id |
| Orders to Order Items | order_items.order_id references orders.order_id |
| Products to Order Items | order_items.product_id references products.product_id |
Before starting the project, learners should have a basic understanding of SQL statements, tables, rows, columns, and data types.
You can use the MySQL command-line client or another compatible SQL editor instead of MySQL Workbench.
The first step is to create a dedicated database to hold the project's tables.
CREATE DATABASE ecommerce_db;
USE ecommerce_db;Explanation:
CREATE DATABASE creates a new database.ecommerce_db is the database name.USE selects the database for subsequent commands.To check whether the database exists, execute:
SHOW DATABASES;To list the tables after creating them, execute:
SHOW TABLES;The customers table stores the details of people who register on the online store.
| Column | Data Type | Purpose |
|---|---|---|
| customer_id | INT | Unique identifier for each customer |
| first_name | VARCHAR(50) | Customer's first name |
| last_name | VARCHAR(50) | Customer's last name |
| VARCHAR(150) | Customer's email address | |
| phone | VARCHAR(20) | Customer's contact number |
| created_at | TIMESTAMP | Registration timestamp |
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);INT AUTO_INCREMENT automatically generates an identifier for each new customer.PRIMARY KEY uniquely identifies every customer record.NOT NULL ensures that required columns contain values.UNIQUE prevents duplicate email addresses in this design.DEFAULT CURRENT_TIMESTAMP records the current timestamp when the row is inserted.Customer email addresses should be validated by the application. A unique database constraint prevents duplicate stored values but does not verify that an address is real or belongs to the customer.
The products table stores information about items that customers can purchase.
| Column | Data Type | Purpose |
|---|---|---|
| product_id | INT | Unique product identifier |
| product_name | VARCHAR(150) | Name of the product |
| description | TEXT | Product details |
| price | DECIMAL(10,2) | Current selling price |
| stock_quantity | INT | Available inventory quantity |
| created_at | TIMESTAMP | Product creation timestamp |
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(150) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock_quantity INT NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT chk_product_price CHECK (price >= 0),
CONSTRAINT chk_product_stock CHECK (stock_quantity >= 0)
);DECIMAL(10,2) stores prices with two decimal places, which is suitable for many currency calculations.DEFAULT 0 initializes stock quantity to zero when it is omitted.In a production application, currency should be explicitly defined for the store or recorded per transaction where multiple currencies are supported.
The orders table stores the overall details of each purchase. It links the order to a customer and records when it was placed and its current status.
| Column | Data Type | Purpose |
|---|---|---|
| order_id | INT | Unique order identifier |
| customer_id | INT | Customer who placed the order |
| order_date | TIMESTAMP | Order creation time |
| status | VARCHAR(30) | Current order status |
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(30) NOT NULL DEFAULT 'Pending',
CONSTRAINT chk_order_status CHECK (
status IN (
'Pending',
'Confirmed',
'Processing',
'Shipped',
'Delivered',
'Cancelled'
)
),
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);customer_id connects each order to a customer.ON DELETE RESTRICT prevents deletion of a customer who has related orders.ON UPDATE CASCADE propagates a customer identifier change to related orders, although primary identifiers are generally kept stable.For an actual online store, an order's status should be updated according to a defined workflow. Cancelled orders and refunds should also be handled according to the store's business rules.
The order_items table records the products and quantities included in each order. It is essential because one order may contain several products.
Imagine an order containing three different products. Storing all three products in separate columns in the orders table would make the design difficult to extend and query. Instead, each product line is stored as a separate row in order_items.
| Column | Data Type | Purpose |
|---|---|---|
| order_item_id | INT | Unique order-item identifier |
| order_id | INT | Associated order |
| product_id | INT | Purchased product |
| quantity | INT | Number of units purchased |
| unit_price | DECIMAL(10,2) | Price per unit at purchase time |
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,
CONSTRAINT chk_order_item_quantity CHECK (quantity > 0),
CONSTRAINT chk_order_item_price CHECK (unit_price >= 0),
CONSTRAINT fk_order_items_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_order_items_product
FOREIGN KEY (product_id)
REFERENCES products(product_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);The products table stores the current product price. However, a product's price may change after a customer places an order. The order_items table therefore stores the unit price agreed upon at purchase time.
Example: A headphone costs ₹1,500 when an order is placed. The price later changes to ₹1,800. The existing order should still show ₹1,500 as the original unit price.
This preserves historical order information and helps produce accurate invoices and sales reports.
The core database consists of four connected tables.
customers
---------
customer_id (PK)
first_name
last_name
email
phone
created_at
orders
------
order_id (PK)
customer_id (FK)
order_date
status
products
--------
product_id (PK)
product_name
description
price
stock_quantity
created_at
order_items
-----------
order_item_id (PK)
order_id (FK)
product_id (FK)
quantity
unit_pricePK means Primary Key. FK means Foreign Key.
Customers (1) -------- (Many) Orders
|
| (1)
|
| (Many)
Order_Items
|
| (Many)
|
| (1)
ProductsThe diagram shows that one customer can have many orders, one order can have many order items, and one product can appear in many order items.
After creating the tables, insert sample customers for testing.
INSERT INTO customers
(first_name, last_name, email, phone)
VALUES
('Rahul', 'Sharma', '[email protected]', '9876543210'),
('Priya', 'Patel', '[email protected]', '9876501234'),
('Amit', 'Verma', '[email protected]', '9812345670'),
('Neha', 'Singh', '[email protected]', '9823456781');The customer_id values are generated automatically. The generated values depend on the current database state, so use the IDs returned by your own queries instead of assuming a particular ID.
SELECT * FROM customers;To display only selected fields:
SELECT customer_id, first_name, last_name, email
FROM customers;Insert several products into the products table.
INSERT INTO products
(product_name, description, price, stock_quantity)
VALUES
('Laptop', 'Business laptop with 16GB RAM', 55000.00, 10),
('Smartphone', 'Android smartphone with 128GB storage', 25000.00, 20),
('Headphones', 'Wireless over-ear headphones', 1500.00, 50),
('Keyboard', 'USB mechanical keyboard', 2500.00, 30),
('Mouse', 'Wireless optical mouse', 800.00, 40);SELECT * FROM products;To display products ordered by price:
SELECT product_id, product_name, price, stock_quantity
FROM products
ORDER BY price DESC;ORDER BY price DESC sorts the most expensive products first.
Before inserting orders, retrieve the actual customer identifiers.
SELECT customer_id, first_name, last_name
FROM customers;Use the returned customer_id values to create orders. The following example assumes Rahul has customer_id 1 and Priya has customer_id 2 in a fresh database.
INSERT INTO orders (customer_id, status)
VALUES
(1, 'Confirmed'),
(2, 'Pending'),
(1, 'Processing');If your customer IDs are different, replace 1 and 2 with the correct values. A foreign-key error will occur if the specified customer does not exist.
SELECT * FROM orders;Order items connect orders with the products purchased in each order.
First, retrieve the actual order and product identifiers:
SELECT order_id, customer_id, status
FROM orders;
SELECT product_id, product_name, price
FROM products;For a fresh database where order IDs 1 and 2 and product IDs 1, 2, and 3 correspond to the sample records above, you can use the following sample data:
INSERT INTO order_items
(order_id, product_id, quantity, unit_price)
VALUES
(1, 1, 1, 55000.00),
(1, 3, 2, 1500.00),
(2, 2, 1, 25000.00),
(2, 5, 1, 800.00);Here, Order 1 contains one laptop and two headphones. Order 2 contains one smartphone and one mouse.
Important: This step records order details but does not automatically reduce product stock. Inventory changes should be handled as part of a controlled order-placement transaction, as explained later.
The SELECT statement retrieves records from a database table.
SELECT * FROM customers;The asterisk (*) returns all columns. In application code, explicitly listing the required columns is usually preferable because it improves clarity and avoids retrieving unnecessary information.
SELECT
customer_id,
first_name,
last_name,
email
FROM customers;Use SELECT to display the products available in the store.
SELECT
product_id,
product_name,
price,
stock_quantity
FROM products;SELECT product_name, price
FROM products
WHERE price < 5000;This query returns products with a price below ₹5,000 when the example prices are interpreted in Indian rupees.
SELECT product_name, stock_quantity
FROM products
WHERE stock_quantity > 0;SELECT product_id, product_name, price
FROM products
WHERE product_name LIKE '%phone%';The LIKE operator supports pattern matching. The percent sign represents zero or more characters.
A JOIN combines related rows from multiple tables. It is one of the most important SQL operations in an e-commerce database.
SELECT
o.order_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
o.order_date,
o.status
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;Explanation:
orders AS o assigns the alias o to the orders table.customers AS c assigns the alias c to the customers table.CONCAT() combines the customer's first and last names.| JOIN Type | Purpose | Typical Use |
|---|---|---|
| INNER JOIN | Returns rows with matching records in both tables. | Orders that have a valid customer |
| LEFT JOIN | Returns every row from the left table and matching rows from the right table. | All customers, including those without orders |
SELECT
c.customer_id,
c.first_name,
c.last_name,
c.email
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;This query is useful for identifying registered customers who have not yet made a purchase.
To display order details, join the customers, orders, order_items, and products tables.
SELECT
o.order_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
p.product_name,
oi.quantity,
oi.unit_price,
oi.quantity * oi.unit_price AS line_total,
o.status
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
INNER JOIN products AS p
ON oi.product_id = p.product_id
ORDER BY o.order_id, p.product_name;The calculated field line_total multiplies the ordered quantity by the stored unit price. For example, two headphones at ₹1,500 each produce a line total of ₹3,000.
Order totals are calculated by adding the line totals for all items in an order.
SELECT
o.order_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
GROUP BY
o.order_id,
c.customer_id,
c.first_name,
c.last_name
ORDER BY o.order_id;Suppose an order contains:
| Product | Quantity | Unit Price | Line Total |
|---|---|---|---|
| Laptop | 1 | ₹55,000 | ₹55,000 |
| Headphones | 2 | ₹1,500 | ₹3,000 |
| Order Total | ₹58,000 | ||
The query multiplies each item's quantity by its unit price and then adds the line totals. This example excludes shipping charges, taxes, discounts, and refunds.
For production systems, store the relevant tax, discount, shipping, and final amount information according to a clearly defined financial model.
Businesses often need to understand how much each customer has spent. The following query calculates the sum of order-item totals for each customer.
SELECT
c.customer_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_order_value
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
LEFT JOIN order_items AS oi
ON o.order_id = oi.order_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name
ORDER BY total_order_value DESC;COALESCE() replaces NULL with zero when a customer has no matching order items.
This query represents the value of recorded order items, not necessarily finalized revenue. A production sales report should account for order status, cancellations, returns, refunds, discounts, and payment outcomes.
Best-selling product reports help store owners identify popular items and plan inventory.
SELECT
p.product_id,
p.product_name,
SUM(oi.quantity) AS total_units_sold
FROM products AS p
INNER JOIN order_items AS oi
ON p.product_id = oi.product_id
INNER JOIN orders AS o
ON oi.order_id = o.order_id
WHERE o.status IN ('Confirmed', 'Processing', 'Shipped', 'Delivered')
GROUP BY p.product_id, p.product_name
ORDER BY total_units_sold DESC;This example includes orders in selected active or completed states. Whether a status should count as a sale depends on the business definition; cancelled orders should normally be excluded, and returns may require additional records or adjustments.
Order-status reports help managers understand how many orders are pending, processing, shipped, delivered, or cancelled.
SELECT
status,
COUNT(*) AS total_orders
FROM orders
GROUP BY status
ORDER BY total_orders DESC;The COUNT() function counts records, while GROUP BY groups records with the same status.
The UPDATE statement modifies existing records.
UPDATE products
SET price = 52000.00
WHERE product_id = 1;This query changes the current product price for the selected product. It does not change the unit_price stored in previously created order_items records.
UPDATE products
SET stock_quantity = 25
WHERE product_id = 3;Always include an appropriate WHERE condition when you intend to modify specific records. Without WHERE, an UPDATE statement may affect every row in the table.
The DELETE statement removes rows from a table.
DELETE FROM customers
WHERE customer_id = 4;In this schema, the foreign-key restriction prevents deletion of a customer who has related orders. A safer production approach is often to deactivate a customer account rather than remove customer records needed for order history or legal obligations.
| Command | Purpose | Important Difference |
|---|---|---|
| DELETE | Removes selected rows or all rows. | Supports WHERE; triggers may execute. |
| TRUNCATE TABLE | Removes all rows from a table. | DDL operation; generally cannot be rolled back like ordinary transactional DELETE. |
| DROP TABLE | Removes the table definition and its data. | Deletes the table object itself. |
Use these commands carefully, especially in production databases. Foreign-key dependencies may prevent truncating or dropping referenced tables.
A subquery is a query nested inside another SQL statement.
SELECT product_name, price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);The inner query calculates the average product price. The outer query returns products priced above that average.
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);The IN operator checks whether a value appears in the result of the subquery.
A view is a stored SQL query that can be used like a virtual table. It helps simplify frequently used reporting queries.
CREATE VIEW order_details_view AS
SELECT
o.order_id,
o.order_date,
o.status,
c.customer_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
p.product_name,
oi.quantity,
oi.unit_price,
oi.quantity * oi.unit_price AS line_total
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
INNER JOIN products AS p
ON oi.product_id = p.product_id;To retrieve records from the view, run:
SELECT *
FROM order_details_view
ORDER BY order_id;Views can simplify reporting and restrict which columns are exposed to particular users. A view does not automatically provide every security guarantee, so database privileges must also be configured correctly.
An index helps MySQL find matching rows without necessarily scanning an entire table. Indexes are particularly useful for frequently filtered, joined, and sorted columns.
Primary keys and unique constraints create indexes automatically. Foreign-key columns may also require indexes, and MySQL creates a suitable index when necessary.
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
CREATE INDEX idx_orders_status_date
ON orders(status, order_date);
CREATE INDEX idx_order_items_product_id
ON order_items(product_id);Before adding an index, inspect the existing indexes to avoid unnecessary duplication.
SHOW INDEX FROM orders;
SHOW INDEX FROM order_items;Indexes improve some read operations but consume storage and can slow inserts, updates, and deletes because index entries must be maintained.
EXPLAIN
SELECT order_id, order_date, status
FROM orders
WHERE customer_id = 1
ORDER BY order_date DESC;EXPLAIN shows the query execution plan. Use it to understand index selection, access methods, and estimated row counts.
A transaction groups multiple database operations into a single unit of work. Transactions are important when placing an order because creating an order, inserting order items, and updating inventory should remain consistent.
| Property | Meaning | E-Commerce Example |
|---|---|---|
| Atomicity | All operations succeed or the transaction is rolled back. | An order should not be partially saved. |
| Consistency | Rules and constraints remain satisfied. | Stock should not become negative. |
| Isolation | Concurrent transactions are controlled. | Two customers should not oversell the last item. |
| Durability | Committed changes persist according to the database's durability guarantees. | A committed order remains recorded after a restart. |
START TRANSACTION;
INSERT INTO orders (customer_id, status)
VALUES (1, 'Pending');
SET @new_order_id = LAST_INSERT_ID();
INSERT INTO order_items
(order_id, product_id, quantity, unit_price)
VALUES
(@new_order_id, 3, 2, 1500.00);
COMMIT;This example demonstrates grouping an order and its order item in a transaction. If any required operation fails, application code should issue ROLLBACK instead of COMMIT.
ROLLBACK;Important: The sample transaction above does not deduct stock or validate payment. A production order-placement process must validate available inventory, use safe concurrency controls, and apply a clearly defined payment workflow before the order is finalized.
Inventory must be checked before confirming a purchase. A conditional UPDATE can reduce stock only when enough units are available.
START TRANSACTION;
UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 3
AND stock_quantity >= 2;
SELECT ROW_COUNT() AS rows_updated;
COMMIT;If rows_updated is zero, the product may not exist or there may be insufficient stock. Application logic should detect this condition and roll back the transaction instead of confirming the purchase.
A safe implementation checks the update result before committing. For example, in application code:
For orders containing several products, process each product within the same transaction and ensure all inventory updates succeed. A payment provider's external API call cannot be rolled back by a MySQL transaction; production systems often use a payment authorization/capture workflow and explicit order states to coordinate these operations.
Normalization organizes data to reduce unnecessary duplication and prevent data anomalies.
Each column should contain a single value rather than a list of values. For example, store individual order products in separate order_items rows instead of storing several product names in one orders column.
Tables should be in 1NF, and non-key attributes should depend on the whole candidate key rather than only part of a composite key. The surrogate order_item_id simplifies the primary key in this project, while the order and product identifiers represent its relationships.
Non-key attributes should depend on the key, not on other non-key attributes. Customer contact information belongs in customers rather than being repeated in every order record.
A larger e-commerce platform usually groups products into categories. A separate categories table avoids repeatedly storing category names in every product record.
CREATE TABLE categories (
category_id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(100) NOT NULL UNIQUE,
description TEXT
);Add a category reference to products:
ALTER TABLE products
ADD COLUMN category_id INT NULL;
ALTER TABLE products
ADD CONSTRAINT fk_products_category
FOREIGN KEY (category_id)
REFERENCES categories(category_id)
ON DELETE SET NULL
ON UPDATE CASCADE;Insert sample categories:
INSERT INTO categories (category_name, description)
VALUES
('Electronics', 'Electronic devices and accessories'),
('Books', 'Printed and digital reading materials'),
('Accessories', 'Additional products and accessories');The category_id in products is nullable in this example, so existing products can remain uncategorized until they are assigned a category.
Payments should be recorded separately from orders because a single order may have payment attempts, partial payments, refunds, or multiple payment events.
CREATE TABLE payments (
payment_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_method VARCHAR(40) NOT NULL,
payment_status VARCHAR(30) NOT NULL DEFAULT 'Pending',
transaction_reference VARCHAR(150) UNIQUE,
paid_at TIMESTAMP NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT chk_payment_amount CHECK (amount >= 0),
CONSTRAINT chk_payment_status CHECK (
payment_status IN (
'Pending',
'Authorized',
'Completed',
'Failed',
'Refunded'
)
),
CONSTRAINT fk_payments_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);This is a simplified payment model. A production system may need separate payment-attempt and refund tables to preserve a complete financial history.
Never store raw card numbers, card verification values (CVV/CVC), or other sensitive payment credentials in a general-purpose database table. Use a reputable payment processor and store only the permitted transaction references and payment metadata.
Customers may have different billing and delivery addresses. A separate table allows one customer to save multiple addresses.
CREATE TABLE customer_addresses (
address_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
address_type VARCHAR(20) NOT NULL DEFAULT 'Shipping',
address_line1 VARCHAR(200) NOT NULL,
address_line2 VARCHAR(200),
city VARCHAR(100) NOT NULL,
state VARCHAR(100),
postal_code VARCHAR(20),
country VARCHAR(100) NOT NULL,
is_default BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT chk_address_type CHECK (
address_type IN ('Shipping', 'Billing')
),
CONSTRAINT fk_addresses_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);For real orders, consider storing a snapshot of the selected shipping and billing addresses with the order. Otherwise, editing a saved customer address could make an old order appear to have been shipped to a different address.
SELECT COUNT(*) AS total_customers
FROM customers;SELECT COUNT(*) AS total_products
FROM products;SELECT product_id, product_name, stock_quantity
FROM products
WHERE stock_quantity <= 5
ORDER BY stock_quantity ASC;SELECT
c.customer_id,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
COUNT(o.order_id) AS total_orders
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_orders DESC;SELECT
p.product_name,
oi.quantity,
oi.unit_price,
oi.quantity * oi.unit_price AS line_total
FROM order_items AS oi
INNER JOIN products AS p
ON oi.product_id = p.product_id
WHERE oi.order_id = 1;Replace order ID 1 with the identifier of the order you want to inspect.
Data integrity means keeping database records accurate, valid, and consistent. Constraints allow the database to reject many invalid operations.
| Constraint | Purpose | Project Example |
|---|---|---|
| PRIMARY KEY | Uniquely identifies each row. | customer_id |
| FOREIGN KEY | Maintains relationships between tables. | orders.customer_id |
| NOT NULL | Requires a value in a column. | product_name |
| UNIQUE | Prevents duplicate values. | customers.email |
| CHECK | Enforces a condition on values. | quantity must be positive |
| DEFAULT | Provides a value when none is supplied. | Order status defaults to Pending |
Constraints should be designed according to business requirements. For example, an email address may be unique in a simple store, while a marketplace may require separate customer accounts, business accounts, and verified email identities.
Security is essential because an e-commerce database may contain personal information, order histories, delivery addresses, and financial transaction references.
The following example is illustrative. Run account-management statements using an appropriately privileged administrator account, and replace the example host and credentials according to your deployment.
CREATE USER 'ecommerce_app'@'localhost'
IDENTIFIED BY 'ReplaceWithAStrongUniquePassword';
GRANT SELECT, INSERT, UPDATE, DELETE
ON ecommerce_db.*
TO 'ecommerce_app'@'localhost';Do not use the sample password literally. Production privileges should be restricted further where possible, and application users should not receive unnecessary administrative permissions.
Backups protect data from accidental deletion, hardware failures, software issues, and other incidents. A backup plan should include regular testing of restoration procedures.
mysqldump -u your_username -p ecommerce_db > ecommerce_backup.sqlThis command exports the database to a SQL file. Run it in a system terminal where MySQL client utilities are installed, not inside the SQL editor.
mysql -u your_username -p ecommerce_db < ecommerce_backup.sqlConfirm that the target database exists and that the backup is suitable for restoration. Protect backup files because they may contain personal and business-sensitive information.
Testing helps ensure that the schema, relationships, and queries behave as expected.
| Error or Problem | Possible Cause | Solution |
|---|---|---|
| Unknown database | The database does not exist or the wrong name is used. | Run SHOW DATABASES and select the correct database. |
| Table already exists | The table was previously created. | Inspect the table or use an appropriate migration strategy. |
| Duplicate entry | A UNIQUE constraint was violated. | Check for an existing email or other duplicate value. |
| Cannot add or update a child row | A foreign key references a missing parent record. | Verify that the customer, order, or product exists. |
| Cannot delete or update a parent row | Related records depend on the row. | Follow the intended relationship and retention policy. |
| Incorrect decimal value | The value does not match the numeric column type. | Use valid numeric values and appropriate precision. |
| Column count doesn't match value count | The INSERT column list and values differ. | Ensure each listed column has a corresponding value. |
| Insufficient stock | The requested quantity exceeds available inventory. | Validate stock before confirming the order. |
| Unexpected report totals | JOINs may duplicate rows or the wrong order statuses may be included. | Review join relationships, grouping, and business rules. |
The four-table design is suitable for learning and a small demonstration project. A real e-commerce platform usually requires additional structures and operational safeguards.
For high-traffic systems, the design should be reviewed for workload, indexing, transaction isolation, data retention, recovery objectives, monitoring, and scalability.
Complete the following exercises to strengthen your MySQL skills.
Project Goal: Create a report that shows each order, its customer, the number of units purchased, and the total value of its order items.
SELECT
o.order_id,
o.order_date,
CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
COUNT(oi.order_item_id) AS distinct_order_lines,
COALESCE(SUM(oi.quantity), 0) AS total_units,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS item_total,
o.status
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id
LEFT JOIN order_items AS oi
ON o.order_id = oi.order_id
GROUP BY
o.order_id,
o.order_date,
c.customer_id,
c.first_name,
c.last_name,
o.status
ORDER BY o.order_date DESC;Note: distinct_order_lines counts the number of order-item rows, not the number of unique product types if the schema permits multiple rows for the same product in an order. The item_total is the sum of stored line values and excludes shipping, taxes, and discounts.
An e-commerce database stores and organizes information used by an online store, including customers, products, orders, inventory, and payments.
MySQL provides relational tables, SQL queries, constraints, indexes, transactions, and foreign keys that support consistent storage and retrieval of structured business data.
A primary key uniquely identifies each row in a table. For example, customer_id identifies a customer.
A foreign key connects a child table to a referenced parent table and helps enforce referential integrity. For example, orders.customer_id references customers.customer_id.
It connects orders with products and stores each purchased product's quantity and purchase-time price. It supports orders containing multiple products.
The current product price may change. Storing the purchase-time price preserves the historical value associated with the order.
WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY, often using aggregate results.
JOIN combines related data from multiple tables, such as displaying customer names alongside their orders.
Normalization organizes tables to reduce unnecessary duplication and prevent insertion, update, and deletion anomalies.
An index helps MySQL locate records efficiently. It can improve read performance but requires storage and adds work to data modifications.
Transactions allow related database operations to succeed together or be rolled back, helping prevent incomplete orders and inconsistent inventory.
Use suitable constraints, conditional stock updates, and transactions that validate inventory before committing an order.
DELETE removes rows from a table, while DROP TABLE removes the table object and its data.
A view is a named SQL query that can be queried like a virtual table. It can simplify frequently used reports.
Join products with order_items, aggregate purchased quantities using SUM(), group by product, and sort the totals in descending order. Define which order statuses count as sales.
Use least-privilege database accounts, parameterized queries, secure connections, protected backups, and appropriate access controls and retention policies.
An order may have multiple payment attempts, partial payments, or refunds. A separate payment structure can preserve these events independently of the order's overall status.
EXPLAIN displays the query execution plan and helps developers investigate how MySQL accesses tables and uses indexes.
Developing an e-commerce database with products, orders, and customers is a practical way to learn relational database design and MySQL. The project demonstrates how to organize information into related tables, enforce relationships through primary and foreign keys, retrieve information using SQL, and calculate useful sales reports.
The customers table stores customer information, the products table stores current product details, the orders table records purchases, and the order_items table connects orders with products while preserving purchase-time prices. Additional tables for categories, addresses, payments, shipping, and returns can extend the design.
By completing this project, learners gain practical experience with database creation, data manipulation, joins, aggregate functions, normalization, indexing, transactions, inventory management, and security. These skills form a foundation for developing database-backed e-commerce applications.
Learn More: Explore the MySQL Training Course to strengthen your database development skills.
Course Demo: Visit Register for a Free Course Demo to learn more about the training program.
