第 10 章：数据库设计与范式
良好的数据库设计是构建高性能、可维护应用的基础。在本章中，我们将学习数据库设计的基本原则、实体关系模型以及规范化理论，帮助你设计出结构合理、性能优良的数据库。
本章内容概要
- 数据库设计的基本原则
- 实体关系模型（ER 模型）
- 数据库设计步骤
- 第一范式（1NF）
- 第二范式（2NF）
- 第三范式（3NF）
- 反范式化设计
- 实战练习：电商系统数据库设计
数据库设计的基本原则
优秀的数据库设计应该遵循以下基本原则：
1. 完整性原则
确保数据的准确性和一致性：
- 实体完整性 - 主键约束
- 参照完整性 - 外键约束
- 域完整性 - 数据类型和检查约束
2. 一致性原则
数据库设计应该与业务逻辑保持一致：
- 表结构反映现实世界的实体和关系
- 字段命名清晰、有意义
- 数据类型选择合适
3. 可扩展性原则
设计应该考虑未来的扩展需求：
- 预留扩展字段
- 合理的表结构设计
- 避免过度复杂的设计
4. 性能原则
在满足功能需求的前提下优化性能：
- 合理使用索引
- 避免冗余数据
- 适当的数据分区
实体关系模型（ER 模型）
实体关系模型是数据库设计的重要工具，用于描述现实世界中实体及其相互关系。
基本概念
实体（Entity）
现实世界中可以区分的对象或概念：
- 学生、课程、教师
- 订单、商品、客户
- 部门、员工、项目
属性（Attribute）
实体的特征或性质：
- 学生：学号、姓名、年龄、专业
- 订单：订单号、下单时间、总金额
- 商品：商品编号、名称、价格、库存
关系（Relationship）
实体之间的联系：
- 一个学生可以选修多门课程（一对多）
- 一个订单包含多个商品（一对多）
- 一个学生可以有多个联系电话（一对多）
ER 图表示法
实体表示
[学生]
+--------+
| 学号 |
| 姓名 |
| 年龄 |
| 专业 |
+--------+关系表示
[学生] ----<选修>---- [课程]基数表示
- 一对一：1:1
- 一对多：1:N
- 多对多：M:N
数据库设计步骤
1. 需求分析
明确系统需要存储和管理的数据：
- 业务流程分析
- 数据流图绘制
- 数据字典建立
2. 概念设计
使用 ER 模型描述数据结构：
- 确定实体和属性
- 确定实体间的关系
- 绘制 ER 图
3. 逻辑设计
将概念模型转换为数据库模式：
- ER 图转换为关系模式
- 确定主键和外键
- 定义完整性约束
4. 物理设计
确定数据的物理存储结构：
- 选择存储引擎
- 设计索引策略
- 考虑分区和分表
5. 实施与维护
创建数据库并持续优化：
- 创建表结构
- 导入初始数据
- 性能调优和维护
范式理论
范式是数据库设计的规范化标准，用于消除数据冗余和异常。
第一范式（1NF）
第一范式要求表中的每个字段都是不可分割的原子值。
不符合 1NF 的设计
sql
-- 错误示例：联系方式字段包含多个值
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
contact VARCHAR(100) -- 包含"电话:138****,邮箱:****@***.com"
);
-- 查询困难，无法单独查询电话或邮箱符合 1NF 的设计
sql
-- 正确示例：每个字段都是原子值
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
phone VARCHAR(20),
email VARCHAR(100)
);
-- 每个字段都可以独立查询和更新第二范式（2NF）
第二范式要求表满足 1NF，并且所有非主键字段都完全依赖于主键。
不符合 2NF 的设计
sql
-- 错误示例：存在部分依赖
CREATE TABLE order_items (
order_id INT,
product_id INT,
order_date DATE,
customer_name VARCHAR(50),
product_name VARCHAR(100),
quantity INT,
price DECIMAL(10,2),
PRIMARY KEY (order_id, product_id)
);
-- 问题：
-- order_date, customer_name 只依赖于 order_id（部分依赖）
-- product_name 只依赖于 product_id（部分依赖）符合 2NF 的设计
sql
-- 正确示例：分解为多个表
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE,
customer_name VARCHAR(50)
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10,2)
);
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);第三范式（3NF）
第三范式要求表满足 2NF，并且所有非主键字段都不传递依赖于主键。
不符合 3NF 的设计
sql
-- 错误示例：存在传递依赖
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
major_id INT,
major_name VARCHAR(50), -- 依赖于 major_id，而非 student_id
department VARCHAR(50) -- 依赖于 major_id，而非 student_id
);
-- 传递依赖：student_id → major_id → (major_name, department)符合 3NF 的设计
sql
-- 正确示例：消除传递依赖
CREATE TABLE majors (
major_id INT PRIMARY KEY,
major_name VARCHAR(50),
department VARCHAR(50)
);
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
major_id INT,
FOREIGN KEY (major_id) REFERENCES majors(major_id)
);高级范式
巴科斯-科德范式（BCNF）
BCNF 是 3NF 的加强版，要求所有函数依赖的决定因素都是候选键。
第四范式（4NF）
4NF 处理多值依赖问题，消除非平凡的多值依赖。
第五范式（5NF）
5NF 处理连接依赖问题，确保表不能进一步无损分解。
反范式化设计
在某些情况下，为了提高查询性能，可以适当违反范式原则，进行反范式化设计。
1. 增加冗余字段
sql
-- 订单表中冗余存储客户姓名，避免连接查询
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
customer_name VARCHAR(50), -- 冗余字段
order_date DATE,
total_amount DECIMAL(10,2)
);2. 预计算字段
sql
-- 订单表中预计算总金额，避免实时计算
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
item_count INT, -- 预计算字段
total_amount DECIMAL(10,2) -- 预计算字段
);3. 历史数据快照
sql
-- 订单表中保存下单时的商品信息快照
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
product_id INT,
product_name VARCHAR(100), -- 快照字段
product_price DECIMAL(10,2) -- 快照字段
);实战练习：电商系统数据库设计
让我们通过设计一个电商系统的数据库来实践所学知识。
1. 需求分析
电商系统需要管理：
- 用户信息
- 商品信息
- 订单信息
- 购物车
- 支付信息
- 库存管理
2. 概念设计（ER 图）
[用户] ----<下单>---- [订单] ----<包含>---- [订单项]
| |
| |
[购物车] [支付]
|
|
[购物车项] ----<关联>---- [商品] ----<属于>---- [商品分类]3. 逻辑设计
sql
-- 创建电商数据库
CREATE DATABASE ecommerce_system
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE ecommerce_system;
-- 用户表
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
phone VARCHAR(20),
real_name VARCHAR(50),
gender ENUM('男', '女', '未知') DEFAULT '未知',
birthday DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
status ENUM('active', 'inactive', 'banned') DEFAULT 'active'
);
-- 用户地址表
CREATE TABLE user_addresses (
address_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
receiver_name VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL,
province VARCHAR(50) NOT NULL,
city VARCHAR(50) NOT NULL,
district VARCHAR(50),
detail_address VARCHAR(200) NOT NULL,
is_default BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
-- 商品分类表
CREATE TABLE categories (
category_id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(50) NOT NULL,
parent_id INT DEFAULT NULL,
sort_order INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES categories(category_id)
);
-- 品牌表
CREATE TABLE brands (
brand_id INT AUTO_INCREMENT PRIMARY KEY,
brand_name VARCHAR(50) NOT NULL,
logo_url VARCHAR(200),
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 商品表
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
brand_id INT,
category_id INT NOT NULL,
product_code VARCHAR(50) UNIQUE,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock_quantity INT NOT NULL DEFAULT 0,
sales_count INT NOT NULL DEFAULT 0,
status ENUM('on_sale', 'off_sale', 'discontinued') DEFAULT 'on_sale',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (brand_id) REFERENCES brands(brand_id),
FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
-- 商品规格表
CREATE TABLE product_specs (
spec_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
spec_name VARCHAR(50) NOT NULL,
spec_value VARCHAR(100) NOT NULL,
price_adjustment DECIMAL(10,2) DEFAULT 0,
stock_quantity INT NOT NULL DEFAULT 0,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- 购物车表
CREATE TABLE shopping_carts (
cart_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
-- 购物车项表
CREATE TABLE cart_items (
cart_item_id INT AUTO_INCREMENT PRIMARY KEY,
cart_id INT NOT NULL,
product_id INT NOT NULL,
spec_id INT,
quantity INT NOT NULL DEFAULT 1,
added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (cart_id) REFERENCES shopping_carts(cart_id),
FOREIGN KEY (product_id) REFERENCES products(product_id),
FOREIGN KEY (spec_id) REFERENCES product_specs(spec_id)
);
-- 订单表
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
order_number VARCHAR(50) NOT NULL UNIQUE,
user_id INT NOT NULL,
address_id INT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
discount_amount DECIMAL(10,2) DEFAULT 0,
shipping_fee DECIMAL(10,2) DEFAULT 0,
final_amount DECIMAL(10,2) NOT NULL,
order_status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
payment_status ENUM('unpaid', 'paid', 'refunded') DEFAULT 'unpaid',
payment_method ENUM('alipay', 'wechat', 'bank_card') DEFAULT 'alipay',
shipping_company VARCHAR(50),
tracking_number VARCHAR(100),
remark TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (address_id) REFERENCES user_addresses(address_id)
);
-- 订单项表
CREATE TABLE order_items (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
spec_id INT,
product_name VARCHAR(100) NOT NULL, -- 快照字段
product_price DECIMAL(10,2) NOT NULL, -- 快照字段
quantity INT NOT NULL,
subtotal DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id),
FOREIGN KEY (spec_id) REFERENCES product_specs(spec_id)
);
-- 支付记录表
CREATE TABLE payments (
payment_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
payment_number VARCHAR(50) NOT NULL UNIQUE,
payment_method ENUM('alipay', 'wechat', 'bank_card') NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_status ENUM('pending', 'success', 'failed', 'refunded') DEFAULT 'pending',
transaction_id VARCHAR(100),
paid_at TIMESTAMP NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES orders(order_id)
);4. 创建索引
sql
-- 为常用查询字段创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_brand ON products(brand_id);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(order_status);
CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_order_items_order ON order_items(order_id);5. 插入测试数据
sql
-- 插入测试数据
INSERT INTO users (username, email, password_hash, phone, real_name) VALUES
('zhangsan', 'zhangsan@example.com', 'hashed_password_1', '13800138001', '张三'),
('lisi', 'lisi@example.com', 'hashed_password_2', '13800138002', '李四');
INSERT INTO categories (category_name, parent_id) VALUES
('电子产品', NULL),
('手机', 1),
('电脑', 1),
('服装', NULL),
('男装', 4),
('女装', 4);
INSERT INTO brands (brand_name, description) VALUES
('苹果', 'Apple品牌'),
('华为', '华为品牌'),
('小米', '小米品牌');
INSERT INTO products (product_name, brand_id, category_id, product_code, price, stock_quantity) VALUES
('iPhone 15', 1, 2, 'IP15-001', 5999.00, 100),
('MacBook Pro', 1, 3, 'MBP-001', 12999.00, 50),
('华为P50', 2, 2, 'HP50-001', 4999.00, 80);
INSERT INTO user_addresses (user_id, receiver_name, phone, province, city, detail_address, is_default) VALUES
(1, '张三', '13800138001', '北京市', '北京市', '朝阳区某某街道123号', TRUE);6. 常用查询示例
sql
-- 查询用户订单及订单项
SELECT
o.order_number,
o.final_amount,
o.order_status,
oi.product_name,
oi.product_price,
oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.user_id = 1
ORDER BY o.created_at DESC;
-- 查询商品及其分类和品牌信息
SELECT
p.product_name,
p.price,
b.brand_name,
c.category_name
FROM products p
JOIN brands b ON p.brand_id = b.brand_id
JOIN categories c ON p.category_id = c.category_id
WHERE p.status = 'on_sale'
ORDER BY p.sales_count DESC;
-- 查询用户购物车信息
SELECT
p.product_name,
ps.spec_value,
ci.quantity,
p.price + IFNULL(ps.price_adjustment, 0) AS final_price
FROM shopping_carts sc
JOIN cart_items ci ON sc.cart_id = ci.cart_id
JOIN products p ON ci.product_id = p.product_id
LEFT JOIN product_specs ps ON ci.spec_id = ps.spec_id
WHERE sc.user_id = 1;数据库设计最佳实践
1. 命名规范
sql
-- 表名使用复数形式
CREATE TABLE users (...);
CREATE TABLE orders (...);
-- 字段名使用下划线分隔
user_id, first_name, created_at
-- 主键统一命名为 id 或 table_name_id
PRIMARY KEY (user_id)2. 数据类型选择
sql
-- 使用合适的数据类型
TINYINT -- 0-255
SMALLINT -- 0-65535
INT -- 大整数
DECIMAL -- 精确小数
VARCHAR -- 可变长度字符串
TEXT -- 长文本
DATE -- 日期
TIMESTAMP -- 时间戳3. 约束和索引
sql
-- 合理使用约束
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK
-- 为常用查询字段创建索引
CREATE INDEX idx_users_email ON users(email);小结
在本章中，我们学习了数据库设计与范式的重要内容：
- 设计原则 - 完整性、一致性、可扩展性、性能原则
- ER 模型 - 实体、属性、关系的概念和表示
- 设计步骤 - 需求分析、概念设计、逻辑设计、物理设计
- 范式理论 - 1NF、2NF、3NF 及其应用
- 反范式化 - 为了性能适当违反范式原则
- 实战练习 - 电商系统数据库设计
良好的数据库设计是构建高质量应用的基础。在下一章中，我们将学习 MySQL 的高级特性，包括分区表、JSON 数据类型等现代数据库功能。