Online Store Database – SQL Project Example for E-commerce

Overview

An Online Store Database is a core component of any e-commerce platform. It handles everything from products, customers, and orders to inventory, payments, and shipping. Designing such a system helps learners understand how relational databases are used in real-world business applications.

Core Features of an Online Store Database

Manage products and categories
Handle customer information
Process orders and order details
Track inventory
Record payments and shipping details

Database Design – Tables and Relationships

1. Customers Table

Stores customer personal and contact information.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(50),
    postal_code VARCHAR(20),
    country VARCHAR(50)
);

2. Categories Table

Categorizes products (e.g., electronics, clothing).

CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100)
);

3. Products Table

Stores product details.

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10, 2),
    stock_quantity INT,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

4. Orders Table

Captures order-level data.

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20),
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

5. Order_Details Table

Stores line items (products in each order).

CREATE TABLE order_details (
    order_detail_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    price_each DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

6. Payments Table

Stores payment records.

CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    payment_date DATETIME,
    payment_method VARCHAR(50),
    amount DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

7. Shipping Table

Stores shipping and delivery information.

CREATE TABLE shipping (
    shipping_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    shipping_method VARCHAR(50),
    shipping_address TEXT,
    shipped_date DATETIME,
    delivery_date DATETIME,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Sample SQL Queries

Retrieve All Products in a Category

SELECT name, price
FROM products
WHERE category_id = 2;

Get All Orders by a Customer

SELECT * FROM orders
WHERE customer_id = 5;

Update Product Stock After an Order

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 10;

Total Sales by Product

SELECT p.name, SUM(od.quantity * od.price_each) AS total_sales
FROM products p
JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id;

Best Practices

Use foreign keys to maintain referential integrity
Add indexes on frequently queried fields like product_id, order_id
Ensure price fields use DECIMAL instead of FLOAT for accuracy
Normalize the database to reduce redundancy
Use views for sales reports and customer summaries