9.5 实践：电商系统表关系设计

      电商系统是数据库设计的经典应用场景，它包含了丰富的表间关系：用户与订单的一对多、订单与商品的多对多、商品与分类的一对多、用户与收货地址的一对多等。本节我们将设计一个简化的电商系统数据库，通过实战巩固对表关系的理解。

一、需求概述

一个基本的电商系统应支持以下功能：

• 用户管理：用户注册、登录、维护个人信息。

• 商品管理：商品属于某个分类，商品有库存、价格、描述等。

• 购物车：用户可将商品加入购物车，支持修改数量、删除。

• 订单管理：用户从购物车生成订单，订单包含多个商品（订单项），并记录收货地址、支付状态等。

• 分类管理：商品分类，支持层级（为简化，我们只设计一级分类）。

• 支付与物流：简化为记录支付方式、订单状态。

二、表结构设计

根据需求，我们设计以下核心表：

1. users（用户表）

2. addresses（收货地址表）

3. categories（商品分类表）

4. products（商品表）

5. carts（购物车表）

6. cart_items（购物车项表）

7. orders（订单表）

8. order_items（订单项表）

9. payments（支付记录表）

1. 用户表（users）

2. 收货地址表（addresses）

每个用户可以拥有多个收货地址（一对多）。订单生成时会关联一个地址。

外键：user_id 引用 users(id)，级联删除（用户删除时地址也删除）。

3. 商品分类表（categories）

4. 商品表（products）

外键：category_id 引用 categories(id)，使用 ON DELETE RESTRICT（防止删除有商品的分类）。

5. 购物车表（carts）

购物车是一个会话级别的概念，通常每个用户只有一个购物车（也可能有游客购物车，这里简化）。

外键：user_id 引用 users(id)，ON DELETE CASCADE。

注意：这里 user_id 加了唯一约束，确保一个用户只有一个购物车（一对一关系）。

6. 购物车项表（cart_items）

购物车与商品是多对多关系，通过此中间表记录每个商品的数量。

7. 订单表（orders）

外键：

• user_id 引用 users(id)，ON DELETE RESTRICT（用户不可随意删除）。

• address_id 引用 addresses(id)，ON DELETE RESTRICT（地址不能随便删除，或可置为NULL）。

8. 订单项表（order_items）

订单与商品也是多对多关系，通过此中间表记录每个商品的购买数量、当时单价。

9. 支付记录表（payments）

记录每笔订单的支付流水，一个订单可以有多次支付尝试，但通常只有一次成功。

外键：order_id 引用 orders(id)，ON DELETE CASCADE。

表关系总览

• users 与 addresses：一对多，一个用户可以有多个收货地址。

• users 与 carts：一对一，一个用户只有一个购物车。

• users 与 orders：一对多，一个用户可以有多个订单。

• categories 与 products：一对多，一个分类下有多个商品。

• products 与 carts：多对多，通过 cart_items 关联，购物车与商品多对多。

• products 与 orders：多对多，通过 order_items 关联，订单与商品多对多。

• orders 与 addresses：多对一（从订单角度，一个订单使用一个地址，一个地址可以被多个订单使用？地址如果被多个订单使用，逻辑上可能发生用户修改地址后订单地址变化，所以通常订单会复制地址快照，但我们这里直接关联地址表，意味着地址变更会影响历史订单，实际系统往往在订单中保存地址快照。但为简化，我们直接关联地址，并限制删除）。

• orders 与 payments：一对多，一个订单可以有多次支付尝试。

创建表的 SQL 语句

（仅展示关键表，省略部分细节）

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    phone VARCHAR(20),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 地址表
CREATE TABLE addresses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    receiver VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    province VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    district VARCHAR(50) NOT NULL,
    address VARCHAR(255) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 分类表
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    category_id INT NOT NULL,
    image_url VARCHAR(255),
    status ENUM('on', 'off') NOT NULL DEFAULT 'on',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 购物车表
CREATE TABLE carts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 购物车项表
CREATE TABLE cart_items (
    cart_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    PRIMARY KEY (cart_id, product_id),
    FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    CHECK (quantity > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(30) NOT NULL UNIQUE,
    user_id INT NOT NULL,
    address_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
    payment_method VARCHAR(50),
    paid_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (address_id) REFERENCES addresses(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单项表
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 支付记录表
CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    payment_no VARCHAR(50) NOT NULL UNIQUE,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('success', 'failed', 'pending') NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    paid_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. 查询某用户购物车中的商品详情

SELECT p.id, p.name, p.price, ci.quantity, (p.price * ci.quantity) AS subtotal
FROM cart_items ci
JOIN products p ON ci.product_id = p.id
JOIN carts c ON ci.cart_id = c.id
WHERE c.user_id = 1;

2. 查询某用户的所有订单及其总金额

SELECT order_no, total_amount, status, created_at
FROM orders
WHERE user_id = 1
ORDER BY created_at DESC;

3. 查询某订单的详细信息（包括商品明细）

SELECT o.order_no, o.status, o.total_amount,
       p.name AS product_name, oi.quantity, oi.unit_price,
       (oi.quantity * oi.unit_price) AS item_total
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.id = 1001;

4. 统计每个分类下的商品数量

SELECT c.name, COUNT(p.id) AS product_count
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.id;

5. 查询热门商品（按在订单中出现的次数排序）

SELECT p.id, p.name, COUNT(oi.product_id) AS order_count
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id
ORDER BY order_count DESC
LIMIT 10;

6. 查询待支付订单（超过30分钟未支付）

SELECT id, order_no, user_id, created_at
FROM orders
WHERE status = 'pending' AND created_at < NOW() - INTERVAL 30 MINUTE;

设计思考

1. 外键约束策略：我们为不同的关系选择了合适的级联行为。例如，用户删除时，其地址和购物车应级联删除；但订单和商品记录因涉及交易，应限制删除或归档处理。

2. 索引优化：对于经常查询的字段（如 orders.user_id、orders.order_no、products.category_id）应建立索引。外键列自动创建索引，可满足大部分查询。

3. 数据类型：价格使用 DECIMAL 保证精度；状态使用 ENUM 限制取值。

4. 订单号的生成：order_no 应具有唯一性，可在应用层生成（如时间戳+随机数），数据库层加唯一约束。

5. 购物车与订单分离：购物车是临时存储，订单是持久化交易记录，分开设计清晰。

6. 地址快照问题：本设计直接将订单关联到地址表，如果用户修改了地址，历史订单的地址也会变化，这通常不符合业务。更合理的做法是在订单中冗余一份地址快照（如复制地址字段），但为保持简洁我们略过。

7. 性能考虑：订单表和订单项表会快速增长，应考虑分区或归档策略。

通过这个电商系统案例，我们实践了各种表间关系的设计，包括一对一（用户-购物车）、一对多（用户-订单、分类-商品）、多对多（商品-订单、商品-购物车）。这些模式可以扩展到许多其他业务系统。