MySQL & Database Design

E-Commerce Database Design

13 min Lesson 35 of 40

E-Commerce Database Design

Designing an e-commerce database requires careful consideration of products, inventory, orders, payments, and customer data. In this lesson, we'll build a complete e-commerce database schema that handles real-world scenarios including product variations, shopping carts, and order processing.

Core Requirements Analysis

A typical e-commerce system needs to handle:

Product Management:
- Products with multiple categories
- Product variations (size, color, etc.)
- Inventory tracking
- Pricing and discounts

Order Processing:
- Shopping cart functionality
- Order placement and tracking
- Order status management
- Payment processing

Customer Management:
- User accounts and profiles
- Shipping addresses
- Order history
- Wishlist functionality

Complete E-Commerce Schema

Let's create the complete database structure:

-- Users table
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    email_verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- Categories table (hierarchical)
CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id INT UNSIGNED NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    image_url VARCHAR(500),
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_parent (parent_id),
    INDEX idx_slug (slug),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- Products table
CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    short_description VARCHAR(500),
    base_price DECIMAL(10, 2) NOT NULL,
    sku VARCHAR(100) UNIQUE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    weight DECIMAL(8, 2) COMMENT 'Weight in kg',
    meta_title VARCHAR(255),
    meta_description VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT,
    INDEX idx_category (category_id),
    INDEX idx_slug (slug),
    INDEX idx_sku (sku),
    INDEX idx_active_featured (is_active, is_featured),
    FULLTEXT idx_search (name, description)
) ENGINE=InnoDB;

-- Product images
CREATE TABLE product_images (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    image_url VARCHAR(500) NOT NULL,
    display_order INT DEFAULT 0,
    is_primary BOOLEAN DEFAULT FALSE,
    alt_text VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_primary (product_id, is_primary)
) ENGINE=InnoDB;

-- Product variations (size, color, etc.)
CREATE TABLE product_variations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    sku VARCHAR(100) UNIQUE NOT NULL,
    variation_name VARCHAR(100) NOT NULL COMMENT 'e.g., "Large / Red"',
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT UNSIGNED DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_sku (sku),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- Inventory tracking
CREATE TABLE inventory_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_variation_id BIGINT UNSIGNED NOT NULL,
    transaction_type ENUM('purchase', 'sale', 'adjustment', 'return') NOT NULL,
    quantity INT NOT NULL COMMENT 'Positive for increase, negative for decrease',
    reference_id BIGINT UNSIGNED COMMENT 'Order ID or purchase ID',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_variation_id) REFERENCES product_variations(id) ON DELETE RESTRICT,
    INDEX idx_variation (product_variation_id),
    INDEX idx_type (transaction_type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Addresses
CREATE TABLE addresses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    address_type ENUM('shipping', 'billing') NOT NULL,
    full_name VARCHAR(200) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20) NOT NULL,
    country VARCHAR(100) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_type (address_type),
    INDEX idx_default (user_id, is_default)
) ENGINE=InnoDB;

-- Shopping cart
CREATE TABLE cart_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    product_variation_id BIGINT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_variation_id) REFERENCES product_variations(id) ON DELETE CASCADE,
    UNIQUE KEY unique_cart_item (user_id, product_variation_id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB;

-- Orders
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded') DEFAULT 'pending',
    subtotal DECIMAL(10, 2) NOT NULL,
    tax_amount DECIMAL(10, 2) DEFAULT 0,
    shipping_amount DECIMAL(10, 2) DEFAULT 0,
    discount_amount DECIMAL(10, 2) DEFAULT 0,
    total_amount DECIMAL(10, 2) NOT NULL,
    shipping_address_id BIGINT UNSIGNED NOT NULL,
    billing_address_id BIGINT UNSIGNED NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (shipping_address_id) REFERENCES addresses(id) ON DELETE RESTRICT,
    FOREIGN KEY (billing_address_id) REFERENCES addresses(id) ON DELETE RESTRICT,
    INDEX idx_user (user_id),
    INDEX idx_order_number (order_number),
    INDEX idx_status (status),
    INDEX idx_payment_status (payment_status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Order items
CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    product_variation_id BIGINT UNSIGNED NOT NULL,
    product_name VARCHAR(255) NOT NULL COMMENT 'Snapshot at time of order',
    variation_name VARCHAR(100),
    sku VARCHAR(100) NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    FOREIGN KEY (product_variation_id) REFERENCES product_variations(id) ON DELETE RESTRICT,
    INDEX idx_order (order_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB;

-- Payment transactions
CREATE TABLE payment_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    transaction_id VARCHAR(255) UNIQUE NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    status ENUM('pending', 'completed', 'failed', 'refunded') NOT NULL,
    gateway_response TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT,
    INDEX idx_order (order_id),
    INDEX idx_transaction (transaction_id),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- Coupons/Discounts
CREATE TABLE coupons (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    discount_type ENUM('percentage', 'fixed') NOT NULL,
    discount_value DECIMAL(10, 2) NOT NULL,
    min_purchase_amount DECIMAL(10, 2) DEFAULT 0,
    max_discount_amount DECIMAL(10, 2),
    usage_limit INT UNSIGNED,
    times_used INT UNSIGNED DEFAULT 0,
    valid_from TIMESTAMP NOT NULL,
    valid_until TIMESTAMP NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_code (code),
    INDEX idx_active (is_active),
    INDEX idx_valid (valid_from, valid_until)
) ENGINE=InnoDB;

-- Reviews
CREATE TABLE product_reviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NOT NULL COMMENT 'Must have purchased',
    rating TINYINT UNSIGNED NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(255),
    comment TEXT,
    is_verified BOOLEAN DEFAULT TRUE,
    is_approved BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_review (product_id, user_id, order_id),
    INDEX idx_product (product_id),
    INDEX idx_user (user_id),
    INDEX idx_approved (is_approved)
) ENGINE=InnoDB;

Key Design Decision: We store product and variation details in order_items as a snapshot to preserve historical accuracy, even if the product is later modified or deleted.

Entity Relationships

Understanding the relationships in this schema:

One-to-Many Relationships:
- categories → categories (hierarchical)
- categories → products
- products → product_images
- products → product_variations
- product_variations → inventory_transactions
- users → addresses
- users → cart_items
- users → orders
- orders → order_items
- orders → payment_transactions

Many-to-Many Relationships:
- products ↔ orders (through order_items)
- users ↔ products (through cart_items)
- users ↔ products (through product_reviews)

Common E-Commerce Queries

Here are essential queries for e-commerce operations:

-- Get products with inventory and images
SELECT
    p.id,
    p.name,
    p.base_price,
    c.name AS category,
    pi.image_url,
    COALESCE(SUM(pv.stock_quantity), 0) AS total_stock
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN product_images pi ON p.id = pi.product_id AND pi.is_primary = TRUE
LEFT JOIN product_variations pv ON p.id = pv.product_id
WHERE p.is_active = TRUE
GROUP BY p.id, p.name, p.base_price, c.name, pi.image_url
HAVING total_stock > 0
ORDER BY p.created_at DESC;

-- Get user's cart with product details
SELECT
    ci.id,
    ci.quantity,
    p.name AS product_name,
    pv.variation_name,
    pv.price,
    (ci.quantity * pv.price) AS subtotal,
    pi.image_url,
    pv.stock_quantity
FROM cart_items ci
JOIN product_variations pv ON ci.product_variation_id = pv.id
JOIN products p ON pv.product_id = p.id
LEFT JOIN product_images pi ON p.id = pi.product_id AND pi.is_primary = TRUE
WHERE ci.user_id = 1
ORDER BY ci.added_at DESC;

-- Get order details with items
SELECT
    o.order_number,
    o.status,
    o.total_amount,
    o.created_at,
    oi.product_name,
    oi.variation_name,
    oi.quantity,
    oi.unit_price,
    oi.total_price
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 1
ORDER BY o.created_at DESC, oi.id;

-- Calculate product average rating
SELECT
    p.id,
    p.name,
    COUNT(pr.id) AS review_count,
    ROUND(AVG(pr.rating), 2) AS avg_rating
FROM products p
LEFT JOIN product_reviews pr ON p.id = pr.product_id AND pr.is_approved = TRUE
GROUP BY p.id, p.name
HAVING review_count > 0
ORDER BY avg_rating DESC;

-- Best selling products
SELECT
    p.id,
    p.name,
    SUM(oi.quantity) AS total_sold,
    SUM(oi.total_price) AS total_revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status IN ('delivered', 'shipped')
    AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY p.id, p.name
ORDER BY total_sold DESC
LIMIT 10;

Handling Product Variations

Product variations require special consideration:

-- Get all variations for a product
SELECT
    p.name AS product_name,
    pv.variation_name,
    pv.sku,
    pv.price,
    pv.stock_quantity,
    pv.is_active
FROM product_variations pv
JOIN products p ON pv.product_id = p.id
WHERE p.id = 100
ORDER BY pv.price;

-- Check stock before adding to cart
SELECT
    pv.id,
    pv.stock_quantity,
    COALESCE(ci.quantity, 0) AS cart_quantity,
    (pv.stock_quantity - COALESCE(ci.quantity, 0)) AS available
FROM product_variations pv
LEFT JOIN cart_items ci ON pv.id = ci.product_variation_id AND ci.user_id = 1
WHERE pv.id = 500;

Performance Tip: Use composite indexes on frequently queried combinations like (is_active, is_featured) and (user_id, is_default) to speed up common queries.

Inventory Management

Tracking inventory accurately is critical:

-- Record inventory transaction (sale)
INSERT INTO inventory_transactions
(product_variation_id, transaction_type, quantity, reference_id, notes)
VALUES (500, 'sale', -2, 12345, 'Order #ORD-2024-001');

-- Update stock quantity
UPDATE product_variations
SET stock_quantity = stock_quantity - 2
WHERE id = 500;

-- Get inventory history
SELECT
    it.id,
    pv.sku,
    it.transaction_type,
    it.quantity,
    it.reference_id,
    it.created_at
FROM inventory_transactions it
JOIN product_variations pv ON it.product_variation_id = pv.id
WHERE pv.product_id = 100
ORDER BY it.created_at DESC
LIMIT 50;

Important: Always use database transactions when processing orders to ensure inventory, cart, and order tables remain consistent. Never update stock without recording the transaction.

Order Processing Workflow

Complete order creation process:

-- Start transaction
START TRANSACTION;

-- Create order
INSERT INTO orders
(user_id, order_number, subtotal, tax_amount, shipping_amount,
 total_amount, shipping_address_id, billing_address_id, payment_method)
VALUES
(1, 'ORD-2024-001', 150.00, 15.00, 10.00, 175.00, 5, 5, 'credit_card');

SET @order_id = LAST_INSERT_ID();

-- Insert order items from cart
INSERT INTO order_items
(order_id, product_id, product_variation_id, product_name,
 variation_name, sku, quantity, unit_price, total_price)
SELECT
    @order_id,
    p.id,
    ci.product_variation_id,
    p.name,
    pv.variation_name,
    pv.sku,
    ci.quantity,
    pv.price,
    (ci.quantity * pv.price)
FROM cart_items ci
JOIN product_variations pv ON ci.product_variation_id = pv.id
JOIN products p ON pv.product_id = p.id
WHERE ci.user_id = 1;

-- Update inventory
UPDATE product_variations pv
JOIN cart_items ci ON pv.id = ci.product_variation_id
SET pv.stock_quantity = pv.stock_quantity - ci.quantity
WHERE ci.user_id = 1;

-- Record inventory transactions
INSERT INTO inventory_transactions
(product_variation_id, transaction_type, quantity, reference_id)
SELECT
    ci.product_variation_id,
    'sale',
    -ci.quantity,
    @order_id
FROM cart_items ci
WHERE ci.user_id = 1;

-- Clear cart
DELETE FROM cart_items WHERE user_id = 1;

-- Commit transaction
COMMIT;

Scalability Considerations

Performance Optimizations:
- Partition orders table by date (monthly/yearly)
- Archive old orders to separate tables
- Use read replicas for product browsing
- Cache popular product queries
- Implement pagination for all listings
- Use covering indexes for common queries

Data Growth Management:
- Archive completed orders after 2 years
- Move old inventory_transactions to archive
- Implement soft deletes for audit trail
- Regular cleanup of abandoned carts
- Optimize image storage with CDN

Practice Exercise:

Task: Add a wishlist feature to the e-commerce database.

Requirements:

Create a wishlist_items table linking users to products
Track when items were added
Allow users to add notes to wishlist items
Write a query to show wishlist with product details and current prices

Solution:

CREATE TABLE wishlist_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    notes VARCHAR(500),
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_wishlist (user_id, product_id),
    INDEX idx_user (user_id)
) ENGINE=InnoDB;

-- Query wishlist with details
SELECT
    wi.id,
    p.name,
    p.base_price,
    wi.notes,
    wi.added_at,
    pi.image_url
FROM wishlist_items wi
JOIN products p ON wi.product_id = p.id
LEFT JOIN product_images pi ON p.id = pi.product_id AND pi.is_primary = TRUE
WHERE wi.user_id = 1
ORDER BY wi.added_at DESC;

Summary

In this lesson, you learned:

How to design a complete e-commerce database schema
Managing products, variations, and inventory
Implementing shopping cart functionality
Processing orders with database transactions
Handling payment tracking and order status
Optimizing queries for e-commerce operations
Scalability considerations for growing stores

Next Up: In the next lesson, we'll design a social media database with posts, comments, likes, and follow relationships!