Database Design Project (E-Commerce)

# CHAPTER 29

Database Design Project (E-Commerce) #

1. Introduction #

2. Learning Objectives #

• Identify the core entities required for an E-Commerce system.

• Apply Database Normalization rules to prevent redundancy.

• Map One-to-Many and Many-to-Many relationships.

• Write the final CREATE TABLE scripts with full constraints.

3. Step 1: Identifying the Entities #

1. 1. Users: The customers making purchases.

1. 2. Products: The items being sold.

1. 3. Categories: The groupings of products (e.g., Electronics, Clothing).

1. 4. Orders: The master record of a purchase.

1. 5. Order_Items: The specific products attached to a specific order.

4. Step 2: Mapping the Relationships #

• A Category has Many Products. A Product belongs to One Category. (1:N)

• A User has Many Orders. An Order belongs to One User. (1:N)

• An Order has Many Products. A Product can be in Many Orders. (N:M) -> *We must use the order_items Pivot Table!*

5. Step 3: Writing the DDL (Data Definition Language) #

#### The Parents (No Foreign Keys)

CREATE DATABASE storefront_db;
USE storefront_db;

-- 1. USERS TABLE
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. CATEGORIES TABLE
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

#### The Children (1:N Relationships)

-- 3. PRODUCTS TABLE (Belongs to Categories)
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    
    -- Constraint: Price cannot be negative!
    CONSTRAINT chk_price CHECK (price >= 0),
    
    -- Relationship
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 4. ORDERS TABLE (Belongs to Users)
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled') DEFAULT 'Pending',
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    -- Relationship
    FOREIGN KEY (user_id) REFERENCES users(id)
);

#### The Pivot Table (N:M Relationship)

-- 5. ORDER_ITEMS TABLE (Bridges Orders and Products)
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    price_at_purchase DECIMAL(10,2) NOT NULL,
    
    -- Composite Primary Key (An order can't have two identical product lines)
    PRIMARY KEY (order_id, product_id),
    
    -- Relationships
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

6. Architectural Brilliance: price_at_purchase #

7. Step 4: The Ultimate Reporting Query #

SELECT 
    o.id AS OrderNumber, 
    u.email AS Customer, 
    p.name AS Product, 
    oi.quantity, 
    o.total_amount AS OrderTotal
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'Shipped' AND o.total_amount > 500;

*Because our Foreign Keys are perfectly mapped, this 4-table join executes flawlessly.*

8. Common Mistakes #

• Storing calculated totals blindly: While we store total_amount in the orders table for caching/speed, you must ensure that your backend PHP code strictly calculates that total by summing up the order_items. Never trust a total passed directly from the frontend HTML cart!

9. Best Practices #

• Indexes for Speed: In production, you would immediately add an index to user_id in the orders table, because the most common query on the website will be: "Show me My Orders."

10. Exercises #

1. 1. Analyze the schema: If a database admin attempts to DELETE a category that currently has 50 products assigned to it, what will MySQL do?

1. 2. Write the SQL query to find the total sum of revenue generated by all orders that are currently marked as 'Delivered'.

11. MCQ Quiz with Answers #

12. Interview Questions #

• Q: Walk me through your database design for a basic E-Commerce cart system. Detail the relationships between Users, Orders, and Products, and specifically explain how you track historical pricing.

• Q: Look at the ON DELETE CASCADE rule applied to the order_id in the order_items table. Explain what this does and why it was not applied to the product_id constraint.

13. FAQs #

14. Summary #

15. Next Chapter Recommendation #