MySQL数据库设计实战之如何从需求到建表(完整流程)

目录

• 为什么写这篇博客
• 第一步：需求分析（找主体）
• 我一开始列出来的主体
• 最终确定的主体
• 第二步：画E-R图（找关系）
• 主体之间的关系
• E-R图
• 第三步：确定字段（每个主体存什么）
• 用户表（users）
• 订单表（orders）
• 订单明细表（order_items）
• 第四步：检查三范式
• 1NF：字段不可再分 ✅
• 2NF：不要部分依赖 ✅
• 3NF：不要传递依赖 ✅
• 第五步：建表SQL
• 创建数据库
• 创建用户表
• 创建订单表
• 创建订单明细表
• 第六步：验证设计（写查询测试）
• 查询1：找深圳用户的订单
• 查询2：找买了iPhone的用户
• 查询3：统计每个用户的订单总额
• 第七步：插入测试数据
• 数据规模
• 数据生成方法
• 我的收获
• 1. 数据库设计的核心流程
• 2. 三范式不是教条
• 3. 索引要根据查询场景设计
• 4. 外键约束要慎用
• 最终的表结构
• 总结

2025年10月9日

工具：HeidiSQL + mysql 8.0

目标：设计一个电商订单系统数据库

为什么写这篇博客

在做SQL优化实战时，我用到了users、orders、order_items三张表。当时觉得这个设计挺合理的，现在系统性的设计出这个数据库。

今天想弄明白：如果让我从需求开始，怎么一步步设计出这三张表？

这篇博客记录我的设计过程。

第一步编程客栈：需求分析（找主体）

假设需求是：做一个电商订单系统。

我的第一个问题：这个系统里有哪些"东西"？

我一开始列出来的主体

1. 用户（买东西的人）
2. 商品（要卖的东西）
3. 订单（用户下的单）

然后我想了想，订单里要包含多个商品，比如：

• 订单1：买了iPhone、AirPods、充电器
• 订单2：买了iPad、Magic Keyboard

我发现：一个订单可以包含多个商品，每个商品的数量、价格可能不同。

所以我又加了一个主体：

4. 订单明细（订单里的每个商品）

最终确定的主体

✅ 用户（User）
✅ 订单（Order）
✅ 订单明细（Order Item）

我没加"商品"表，因为这次练习重点是订单查询，所以把商品名直接存在订单明细里了。（实际项目中应该有独立的商品表）

第二步：画E-R图（找关系）

主体之间的关系

1. 用户 和 订单

• 一个用户可以下多个订单
• 一个订单只属于一个用户
• 关系：一对多（1:N）

2. 订单 和 订单明细

• 一个订单可以包含多个商品
• 一个订单明细只属于一个订单
• 关系：一对多（1:N）

E-R图

符号说明：

• ||--o{：一对多关系
• PK：主键（Primary Key）
• FK：外键（Foreign Key）

第三步：确定字段（每个主体存什么）

用户表（users）

需要存什么信息？

我想了想，一个用户需要：

• id：唯一标识（主键）
• name：姓名
• email：邮箱（可以用来登录、发邮件）
• city：城市（可以按地区统计）

我一开始想加：

• ❌ password（密码）→ 这次练习不涉及登录，不加了
• ❌ phone（手机号）→ 暂时不需要

最终字段：

id, name, email, city

订单表（orders）

需要存什么信息？

• id：订单号（主键）
• user_id：哪个用户下的单（外键，指向users.id）
• total_amount：订单总金额
• status：订单状态（pending/completed/cancelled）
• created_at：下单时间

我一开始犯了个错误：

我想在orders表里存用户名、城市，方便查询。

但我想起来：这违反第三范式（传递依赖）！

订单ID → 用户ID → 用户名、城市
       直接依赖   传递依赖（不应该存）

正确做法：

• orders表只存user_id
• 需要用户名？JOIN users表

最终字段：

id, user_id, total_amount, status, created_at

订单明细表（order_items）

需要存什么信息？

• id：明细ID（主键）
• order_id：属于哪个订单（外键，指向orders.id）
• product_name：商品名称
• quantity：购买数量
• price：单价

我一开始又想犯错：

我想存订单总金额、用户名，方便查询。

但我又想起来：这又是传递依赖！

订单明细ID → 订单ID → 订单总金额、用户ID → 用户名
           直接依赖   传递依赖（不应该存）

正确做法：

• order_items表只存order_id
• 需要订单金额、用户名？一层层JOIN

最终字段：

id, order_id, product_name, quantity, price

第四步：检查三范式

1NF：字段不可再分 ✅

每个字段都是原子的：

• ✅ name是name，email是email，没有"用户信息"字段塞一堆东西
• ✅ product_name是商品名，quantity是数量，分开存

2NF：不要部分依赖 ✅

每张表只存自己的事：

• ✅ users表：只存用户信息
• ✅ orders表：只存订单信息（不存用户名、邮箱）
• ✅ order_items表：只存商品信息（不存订单金额、用户名）

3NF：不要传递依赖 ✅

不存"可以通过别人找到"的数据：

• ✅ orders表：不存用户名（可以通过user_id找到）
• ✅ order_items表：不存订单金额、用户名（可以通过order_id找到）

关系链：

graph LR
    A[order_items表] -->|order_id| B[orders表]
    B -->|user_id| C[users表]
    A -->|只存：商品信息| A
    B -->|只存：订单信息| B
    C -->|只存：用户信息| C

第五步：建表SQL

确认设计没问题后，开始写建表语句。

创建数据库

CREATE DATABASE IF NOT EXISTS sql_optimization_test;
USE sql_optimization_test;

创建用户表

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    city VARCHAR(50),
    INDEX idx_city (city)  -- 按城市查询的索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

为什么加idx_city索引？

• 我预计会经常按城市查询（比如：深圳的用户）
• 提前建好索引

创建订单表

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),       -- JOIN时用
    INDEX idx_status (status),         -- 按状态查询
    INDEX idx_created_at (created_at), -- 按时间查询
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

关键点：

• user_id设置了外键约束（保证数据一致性）
• 加了3个索引（根据常用查询场景）

创建订单明细表

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    price DECIMAL(10,2) NOT NULL,
    INDEX idx_order_id (order_id),          -- JOIN时用
    INDEX idx_product_name (product_name),  -- 按商品查询
    FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

第六步：验证设计（写查询测试）

建表后，我写了几个常见查询，测试设计是否合理。

查询1：找深圳用户的订单

SELECT u.name, o.id, o.total_amount, o.status
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = '深圳';

测试通过！ ✅

查询2：找买了iPhone的用户

SELECT DISTINCT u.id, u.name, u.ehttp://www.devze.commail
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
WHERE oi.product_name LIKE '%iPhone%';

测试通过！ ✅

查询3：统计每个用户的订单总额

SELECT u.id, u.name, SUM(o.total_amount) AS total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total DESC;

测试通过！ ✅

第七步：插入测试数据

为了后续做SQL优化练习，我准备了测试数据。

数据规模

• users：1000条
• orders：5000条
• order_items：10000条

数据生成方法

我用存储过程批量生成：

-- 生成1000个用户
DELIMITER $$
CREATE PROCEDURE generate_users()
BEGIN
    DECLARE i INT DEandroidFAULT 1;
    WHILE i <= 1000 DO
        INSERT INTO users (name, email, city) VALUES (
            CONCAT('用户', i),
            CONCAT('user', i, '@example.com'),
            ELT(FLOOR(1 + RAND() * 5), '深圳', '北京', '上海', '广州', '杭州')
        );
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;
CALL generate_users();

类似的方法生成订单和订单明细。

我的收获

1. 数据库设计的核心流程

需求分析 → 找主体 → 画E-R图 → 确定字段 → 检查范式 → 建表 → 验证

2. 三范式不是教条

我一开始以为"符合三范式就是好设计"。

但后来发现：

• 有时候需要反范式化（为了性能，适当冗余）
• 关键是理解为什么要这样设计

比如：

• 电商系统可能会在订单表冗余"收货地址"（虽然违反3NF）
• 因为用户可能改地址，但历史订单的地址不能变

3. 索引要根据查询场景设计

我在建表时就考虑了编程客栈：

• 哪些字段会用来JOIN？→ 加索引
• 哪些字段会用来WHERE？→ 加索引
• 哪些字段会用来ORDER BY？→ 加索引

这样后续做SQL优化时，心里有底。

4. 外键约束要慎用

我在建表时加了外键约束：

FOREIGN KEY (user_id) REFERENCES users(id)

好处：

• 保证数据一致性（不能插入不存在的user_id）

坏处：

• 插入/删除性能下降（要检查约束）
• 实际项目中很多公司不用外键，在应用层保证一致性

最终的表结构

总结

这次从零设计数据库，最大的感受是：

数据库设计不是背范式规则，而是理解"为什么要这样存"。

三个核心问题：

1. 这个数据属于谁？（决定放哪个表）
2. 这个数据能通过别人找到吗？（决定要不要冗余存）
3. 这个字段会怎么查？（决定要不要加索引）

把这三个问题想清楚，数据库设计就不难了。

到此这篇关于MySQL数据库设计实战之如何从需求到建表(完整流程)的文章就介绍到这了,更多相关mysql数据库设计内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！python