MySQL: Integrated Project

Q: How do you ensure order numbers are unique?

By concatenating a timestamp and a random number (e.g., ORD202401151430520387 ), using a UUID, or relying on a Redis auto-increment sequence. Generate the order number as CONCAT + DATE_FORMAT + RAND during the order storage process, and use a UNIQUE constraint as a fallback.

Q: What should I do if inventory is oversold?

There are two approaches—for pessimistic locking, use SELECT ... FOR UPDATE to lock the inventory row and deduct the quantity within the transaction; for optimistic locking, use version UPDATE products SET stock = stock - N, version = version + 1 WHERE id = X AND version = V . In high-concurrency scenarios, we recommend using optimistic locking combined with Redis for pre-deduction.

Q: Is the order table too large?

Three strategies—1. Split the table by month into orders_202401 and orders_202402 ; 2. Use MySQL partitioning PARTITION BY RANGE (TO_DAYS(created_at)) ; 3. Separate active and inactive data; archive orders older than one year to a historical table. Add a time condition to queries to target the correct partition.

Q: How do you design an infinite-level classification structure?

Use parent_id as a self-referencing foreign key, with the top-level category parent_id = 0 . Query the subtree using a recursive CTE: WITH RECURSIVE cat_tree AS (SELECT * FROM categories WHERE id = 1 UNION ALL SELECT c.* FROM categories c JOIN cat_tree ct ON c.parent_id = ct.id) SELECT * FROM cat_tree;

Last updated: 2026-08-26

ShopEasy, the e-commerce platform founded by Alice, has just secured funding and needs to upgrade from Excel-based management to MySQL. She designed a complete e-commerce database from scratch, with eight modules covering the entire process from user registration to data analysis. Since going live, the platform has been handling over 5,000 orders per day, and the database has remained rock-solid.

1. Project Overview

Covers 8 major modules: Users, Products, Orders, Shopping Cart, Payments, Statistics, Permissions, and Backups
Each module includes complete SQL statements for creating tables and business logic
Stored procedures encapsulate the order placement logic, and transactions ensure data consistency.
Views provide data statistics and access control for security
Backup scripts ensure data recoverability

(1) List of Modules and Tables

Module	Core Table	Main Functions
User System	users / addresses / user_logs	Registration and Login, Address Management, Operation Auditing
Product System	categories / products / product_images	Unlimited-level categorization, product CRUD, multiple images
Order System	orders / order_items	Order Placement, Inventory Deduction, Status Updates
Shopping Cart	cart	Add, Delete, Edit, View, Merge Shopping Carts
Payment History	payments	Payment Status, Refunds
Statistics	Views	Best-Selling Products, Sales Rankings, Monthly Reports
Permission Management	MySQL Users	Read-Only/Application/Administrator
Backup Strategy	mysqldump	Daily Backup and Retention Policy

(2) Complete ER Diagram

erDiagram
    USERS ||--o{ ADDRESSES : has
    USERS ||--o{ ORDERS : places
    USERS ||--o{ CART : adds
    USERS ||--o{ USER_LOGS : generates
    CATEGORIES ||--o{ CATEGORIES : parent
    CATEGORIES ||--o{ PRODUCTS : contains
    PRODUCTS ||--o{ PRODUCT_IMAGES : has
    PRODUCTS ||--o{ ORDER_ITEMS : included_in
    PRODUCTS ||--o{ CART : added_to
    ORDERS ||--|{ ORDER_ITEMS : contains
    ORDERS ||--o| PAYMENTS : paid_by

    USERS {
        bigint id PK
        varchar username
        varchar email
        varchar password_hash
        varchar phone
        enum status
    }
    ADDRESSES {
        bigint id PK
        bigint user_id FK
        varchar receiver
        varchar phone
        varchar province
        varchar city
        varchar detail
        tinyint is_default
    }
    USER_LOGS {
        bigint id PK
        bigint user_id FK
        varchar action
        varchar ip_address
        datetime created_at
    }
    CATEGORIES {
        bigint id PK
        varchar name
        bigint parent_id FK
        int sort_order
    }
    PRODUCTS {
        bigint id PK
        varchar name
        decimal price
        int stock
        bigint category_id FK
        enum status
    }
    PRODUCT_IMAGES {
        bigint id PK
        bigint product_id FK
        varchar image_url
        tinyint is_main
        int sort_order
    }
    ORDERS {
        bigint id PK
        varchar order_no
        bigint user_id FK
        decimal total_amount
        enum status
    }
    ORDER_ITEMS {
        bigint id PK
        bigint order_id FK
        bigint product_id FK
        int quantity
        decimal price
    }
    CART {
        bigint id PK
        bigint user_id FK
        bigint product_id FK
        int quantity
    }
    PAYMENTS {
        bigint id PK
        bigint order_id FK
        varchar payment_no
        decimal amount
        enum method
        enum status
    }

MERMAID

erDiagram
    USERS ||--o{ ADDRESSES : has
    USERS ||--o{ ORDERS : places
    USERS ||--o{ CART : adds
    USERS ||--o{ USER_LOGS : generates
    CATEGORIES ||--o{ CATEGORIES : parent
    CATEGORIES ||--o{ PRODUCTS : contains
    PRODUCTS ||--o{ PRODUCT_IMAGES : has
    PRODUCTS ||--o{ ORDER_ITEMS : included_in
    PRODUCTS ||--o{ CART : added_to
    ORDERS ||--|{ ORDER_ITEMS : contains
    ORDERS ||--o| PAYMENTS : paid_by

    USERS {
        bigint id PK
        varchar username
        varchar email
        varchar password_hash
        varchar phone
        enum status
    }
    ADDRESSES {
        bigint id PK
        bigint user_id FK
        varchar receiver
        varchar phone
        varchar province
        varchar city
        varchar detail
        tinyint is_default
    }
    USER_LOGS {
        bigint id PK
        bigint user_id FK
        varchar action
        varchar ip_address
        datetime created_at
    }
    CATEGORIES {
        bigint id PK
        varchar name
        bigint parent_id FK
        int sort_order
    }
    PRODUCTS {
        bigint id PK
        varchar name
        decimal price
        int stock
        bigint category_id FK
        enum status
    }
    PRODUCT_IMAGES {
        bigint id PK
        bigint product_id FK
        varchar image_url
        tinyint is_main
        int sort_order
    }
    ORDERS {
        bigint id PK
        varchar order_no
        bigint user_id FK
        decimal total_amount
        enum status
    }
    ORDER_ITEMS {
        bigint id PK
        bigint order_id FK
        bigint product_id FK
        int quantity
        decimal price
    }
    CART {
        bigint id PK
        bigint user_id FK
        bigint product_id FK
        int quantity
    }
    PAYMENTS {
        bigint id PK
        bigint order_id FK
        varchar payment_no
        decimal amount
        enum method
        enum status
    }

2. Requirements Analysis and ER Design

(1) Requirements Analysis

Core business workflow of an e-commerce system: User registration → Browse products → Add to cart → Place order → Payment → Shipping → Completion. Each step requires corresponding tables and constraints.

(2) Relationship Description

Relationship	Type	Description
User → Address	One-to-many	A user can have multiple shipping addresses
User → Order	One-to-many	A single user can place multiple orders
Order → Order Line Item	One-to-many	An order contains multiple items
Product → Order Line Item	One-to-many	A single product can appear in multiple orders
Category → Category	Self-referencing	parent_id for implementing infinite-level categorization
Category → Product	One-to-many	Multiple products under one category
Product → Image	One-to-many	Multiple images per product
Order → Payment	One-to-one	One payment record per order

▶ Example: Creating a Database

SQL

CREATE DATABASE IF NOT EXISTS ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE ecommerce;

▶ Try it Yourself

Output:

TEXT 📖 Display only

Output displayed

3. User System

(1) Table Structure Design

SQL

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    avatar VARCHAR(255) DEFAULT '/images/default_avatar.png',
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_phone (phone),
    INDEX idx_users_status (status)
) ENGINE=InnoDB COMMENT='User Table';

CREATE TABLE addresses (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    receiver VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    province VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    district VARCHAR(30) NOT NULL,
    detail VARCHAR(200) NOT NULL,
    is_default TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_addresses_user (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='List of Shipping Addresses';

CREATE TABLE user_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    action VARCHAR(50) NOT NULL,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    detail JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_logs_user (user_id),
    INDEX idx_logs_action (action),
    INDEX idx_logs_created (created_at)
) ENGINE=InnoDB COMMENT='User Activity Log Table';

▶ Example: User Registration

Output:

TEXT 📖 Display only

Query OK, 1 row affected

Query OK, 1 row affected

SQL

INSERT INTO users (username, email, password_hash, phone)
VALUES (
    'alice',
    'alice@example.com',
    SHA2('MySecret123!', 256),
    '13800138000'
);

INSERT INTO users (username, email, password_hash, phone)
VALUES (
    'bob_dev',
    'bob@example.com',
    SHA2('BobPass456!', 256),
    '13900139000'
);

Output:

TEXT 📖 Display only

---+----------+--------
id | username | status 
---+----------+--------
1  | Alice    | active 
2  | Bob      | pending
---+----------+--------
2 rows in set

SQL

SELECT id, username, status FROM users
WHERE email = 'alice@example.com'
  AND password_hash = SHA2('MySecret123!', 256)
  AND status = 'active';

▶ Try it Yourself

Output:

TEXT 📖 Display only

Query OK, 1 row affected

Query OK, 1 row affected

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

▶ Example: Address Management

SQL

INSERT INTO addresses (user_id, receiver, phone, province, city, district, detail, is_default)
VALUES (1, 'Alice', '13800138000', 'Guangdong', 'Shenzhen', 'Nanshan', 'Tech Park Bldg 5 Room 301', 1);

INSERT INTO addresses (user_id, receiver, phone, province, city, district, detail, is_default)
VALUES (1, 'Alice', '13800138000', 'Beijing', 'Beijing', 'Haidian', 'Zhongguancun St 88', 0);

SELECT * FROM addresses WHERE user_id = 1 ORDER BY is_default DESC;

▶ Try it Yourself

Output:

TEXT 📖 Display only

Query OK, 1 row affected

Query OK, 1 row affected

+--------+-----+
| action | cnt |
+--------+-----+
| 5      | 5   |
+--------+-----+
1 row in set

▶ Example: User Activity Log

SQL

INSERT INTO user_logs (user_id, action, ip_address, user_agent, detail)
VALUES (1, 'LOGIN', '192.168.1.100', 'Chrome/120', '{"method": "email"}');

INSERT INTO user_logs (user_id, action, ip_address, detail)
VALUES (1, 'UPDATE_ADDRESS', '192.168.1.100', '{"address_id": 1}');

SELECT action, COUNT(*) AS cnt
FROM user_logs
WHERE user_id = 1
GROUP BY action
ORDER BY cnt DESC;

▶ Try it Yourself

Output:

TEXT 📖 Display only

Output displayed

4. Product System

(1) Infinite-level classification

SQL

CREATE TABLE categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    parent_id BIGINT DEFAULT 0,
    sort_order INT DEFAULT 0,
    icon VARCHAR(255),
    is_visible TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_categories_parent (parent_id),
    INDEX idx_categories_sort (sort_order)
) ENGINE=InnoDB COMMENT='Product Category Table';

▶ Example: Categorical Data

Output:

TEXT 📖 Display only

Query OK, 1 row affected

---------+-------------
category | sub_category
---------+-------------
A        | A           
B        | B           
---------+-------------
2 rows in set

SQL

INSERT INTO categories (id, name, parent_id, sort_order) VALUES
(1, 'Electronics', 0, 1),
(2, 'Clothing', 0, 2),
(3, 'Home & Living', 0, 3),
(4, 'Smartphones', 1, 1),
(5, 'Laptops', 1, 2),
(6, 'Men', 2, 1),
(7, 'Women', 2, 2),
(8, 'Kitchen', 3, 1);

SELECT c1.name AS category, c2.name AS sub_category
FROM categories c1
LEFT JOIN categories c2 ON c1.id = c2.parent_id
WHERE c1.parent_id = 0
ORDER BY c1.sort_order, c2.sort_order;

Output:

TEXT 📖 Display only

Query OK, 0 rows affected

Query OK, 0 rows affected

(2) Products and Images

SQL

CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    original_price DECIMAL(10,2),
    stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    sales INT NOT NULL DEFAULT 0,
    category_id BIGINT NOT NULL,
    status ENUM('active', 'inactive', 'sold_out') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_products_category (category_id),
    INDEX idx_products_status (status),
    INDEX idx_products_sales (sales DESC),
    INDEX idx_products_created (created_at DESC),
    FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB COMMENT='Product List';

CREATE TABLE product_images (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    is_main TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_images_product (product_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Product Image Table';

5. Shopping Cart

(1) Table Structure

SQL

CREATE TABLE cart (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
    selected TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_cart_user_product (user_id, product_id),
    INDEX idx_cart_user (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Shopping Cart Table';

6. Order System

(1) Orders and Order Items

SQL

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    pay_amount DECIMAL(12,2) DEFAULT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled', 'refunded')
        DEFAULT 'pending',
    receiver VARCHAR(50) NOT NULL,
    receiver_phone VARCHAR(20) NOT NULL,
    shipping_address TEXT NOT NULL,
    remark VARCHAR(500),
    paid_at DATETIME DEFAULT NULL,
    shipped_at DATETIME DEFAULT NULL,
    completed_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_orders_user (user_id),
    INDEX idx_orders_status (status),
    INDEX idx_orders_created (created_at DESC),
    INDEX idx_orders_order_no (order_no),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB COMMENT='Orders Table';

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(12,2) GENERATED ALWAYS AS (quantity * price) STORED,
    INDEX idx_items_order (order_id),
    INDEX idx_items_product (product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB COMMENT='Order Details';

(2) Order Status Changes

Current State	Possible States	Trigger Conditions
pending	paid	payment successful
pending	canceled	canceled by user/unpaid due to timeout
paid	shipped	shipped by seller
paid	refunded	request a refund
shipped	completed	buyer confirmed receipt
completed	refunded	After-sales refund

7. Payment History

(1) Table Structure

SQL

CREATE TABLE payments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    payment_no VARCHAR(64) NOT NULL UNIQUE,
    amount DECIMAL(12,2) NOT NULL,
    method ENUM('credit_card', 'debit_card', 'paypal', 'bank_transfer') NOT NULL,
    status ENUM('pending', 'success', 'failed', 'refunded') DEFAULT 'pending',
    paid_at DATETIME DEFAULT NULL,
    refund_no VARCHAR(64) DEFAULT NULL,
    refund_amount DECIMAL(12,2) DEFAULT NULL,
    refund_reason VARCHAR(500) DEFAULT NULL,
    refunded_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_payments_order (order_id),
    INDEX idx_payments_no (payment_no),
    INDEX idx_payments_status (status),
    FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB COMMENT='Payment History Report';

(2) Payment Status Progression

Current State	Possible States	Trigger Conditions
pending	success	Payment gateway callback successful
pending	failed	Payment gateway callback failed
success	refunded	Refund initiated

8. Data Statistics View

9. Permission Management and Backup Policies

(1) Permissions Matrix

User	SELECT	INSERT	UPDATE	DELETE	EXECUTE	ALTER	DROP	CREATE
ecom_readonly	Y	-	-	-	-	-	-	-
ecom_app	Y	Y	Y	-	Y	-	-	-
ecom_admin	Y	Y	Y	Y	Y	Y	Y	Y

10. Index Design Checklist

Table	Index Name	Column	Type	Purpose
users	idx_users_phone	phone	BTREE	Log in with phone number
users	idx_users_status	status	BTREE	Status filtering
addresses	idx_addresses_user	user_id	BTREE	User address list
categories	idx_categories_parent	parent_id	BTREE	Find subcategories
products	idx_products_category	category_id	BTREE	Category product list
products	idx_products_status	status	BTREE	Filter by Listing Status
products	idx_products_sales	sales DESC	BTREE	Best-seller sorting
products	idx_products_created	created_at DESC	BTREE	New arrivals sorting
orders	idx_orders_user	user_id	BTREE	User Order List
orders	idx_orders_status	status	BTREE	Status filtering
orders	idx_orders_created	created_at DESC	BTREE	Time sorting
orders	idx_orders_order_no	order_no	UNIQUE	Order Number Lookup
order_items	idx_items_order	order_id	BTREE	Order Details
order_items	idx_items_product	product_id	BTREE	Product Sales Statistics
cart	uk_cart_user_product	user_id,product_id	UNIQUE	Remove duplicates from shopping cart
payments	idx_payments_order	order_id	BTREE	Order payment query
payments	idx_payments_status	status	BTREE	Payment status filtering
user_logs	idx_logs_user	user_id	BTREE	User Logs
user_logs	idx_logs_created	created_at	BTREE	Time-Range Query

❓ FAQ

Q How do you ensure order numbers are unique?

A By concatenating a timestamp and a random number (e.g., ORD202401151430520387), using a UUID, or relying on a Redis auto-increment sequence. Generate the order number as CONCAT + DATE_FORMAT + RAND during the order storage process, and use a UNIQUE constraint as a fallback.

Q What should I do if inventory is oversold?

A There are two approaches—for pessimistic locking, use SELECT ... FOR UPDATE to lock the inventory row and deduct the quantity within the transaction; for optimistic locking, use version UPDATE products SET stock = stock - N, version = version + 1 WHERE id = X AND version = V. In high-concurrency scenarios, we recommend using optimistic locking combined with Redis for pre-deduction.

Q Is the order table too large?

A Three strategies—1. Split the table by month into orders_202401 and orders_202402; 2. Use MySQL partitioning PARTITION BY RANGE (TO_DAYS(created_at)); 3. Separate active and inactive data; archive orders older than one year to a historical table. Add a time condition to queries to target the correct partition.

Q How do you design an infinite-level classification structure?

A Use parent_id as a self-referencing foreign key, with the top-level category parent_id = 0. Query the subtree using a recursive CTE:

WITH RECURSIVE cat_tree AS (SELECT * FROM categories WHERE id = 1 UNION ALL SELECT c.* FROM categories c JOIN cat_tree ct ON c.parent_id = ct.id) SELECT * FROM cat_tree;

Q Are shopping carts stored in the database or in Redis?

A For logged-in users, shopping carts are stored in the database (for persistence) and cached in Redis (for fast read/write operations). For users who are not logged in, shopping carts are stored in Redis or LocalStorage and merged into the database upon login. Since shopping cart operations are frequent, Redis helps reduce the load on the database.

Q Why use SHA-2 instead of MD5 for passwords?

A MD5 has been proven to be vulnerable to collisions and can be easily cracked using rainbow tables. SHA-2-256 is more secure; in production environments, we recommend using bcrypt or Argon2, which include salt values and allow for adjustable computational cost.

📖 Summary

Requirements Analysis is the starting point; the ER diagram maps out entities and relationships; the eight modules cover the entire e-commerce process.
User System: Three-table collaboration—the users table manages identities, the addresses table manages shipping addresses, and the user_logs table manages audit logs
Product System: Uses parent_id self-referencing to implement infinite-level categorization; product_images supports multiple images
The order system is at the core: stored procedures encapsulate the order placement logic, FOR UPDATE prevents overselling, and transactions ensure atomicity.
Shopping Cart: Use ON DUPLICATE KEY UPDATE to implement adding items to the cart and merging them; stored procedures handle cross-user merging
Payment Records are separate from orders, with clear status updates and complete fields for tracking refunds
Data Analytics: Encapsulate complex aggregate queries using views to generate best-sellers, rankings, and monthly reports with a single click
Permissions and Backups are the foundation of security: the principle of least privilege + daily full backups + incremental binlog recovery

📝 Exercises

Basic Question (Difficulty: ⭐): Create an e-commerce database, write the SQL statements to create the four tables (users, addresses, categories, and products), and insert test data.
Advanced Exercise (Difficulty ⭐⭐): Implement a complete shopping cart feature—create tables, write SQL queries for CRUD operations, write the sp_merge_cart stored procedure, and use ON DUPLICATE KEY UPDATE to handle duplicate additions to the cart.
Practical Exercise (Difficulty: ⭐⭐⭐): Implement the order placement stored procedure sp_create_order. It must include iterating through the shopping cart, checking inventory (FOR UPDATE), inserting the order and its details, deducting inventory, and clearing the shopping cart. The entire process must be wrapped in a transaction; if inventory is insufficient, roll back the transaction and throw an error.
Analysis Question (Difficulty: ⭐⭐⭐): Create three statistical views (Best-Selling Products / User Spending Rankings / Monthly Sales Report), write queries to verify the view results, and analyze performance issues with the views when the orders table exceeds 10 million rows, along with proposed optimization solutions.
Challenge (Difficulty: ⭐⭐⭐⭐): Fully deploy the ShopEasy e-commerce database—including database and table creation, indexes, stored procedures, views, and user permissions (readonly/app/admin)—along with a daily backup script. Additionally, write a 200-character deployment document outlining the initial setup steps and key points for daily operations and maintenance.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

MySQL: Integrated Project

1. Project Overview

(1) List of Modules and Tables

(2) Complete ER Diagram

2. Requirements Analysis and ER Design

(1) Requirements Analysis

(2) Relationship Description

▶ Example: Creating a Database

3. User System

(1) Table Structure Design

(2) Registration and Login Verification

▶ Example: User Registration

▶ Example: Login Authentication

▶ Example: Address Management

▶ Example: User Activity Log

4. Product System

(1) Infinite-level classification

▶ Example: Categorical Data

(2) Products and Images

5. Shopping Cart

(1) Table Structure

6. Order System

(1) Orders and Order Items

(2) Order Status Changes

7. Payment History

(1) Table Structure

(2) Payment Status Progression

8. Data Statistics View

9. Permission Management and Backup Policies

(1) Permissions Matrix

10. Index Design Checklist

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好