数据库设计基础

建议学习 90-120 分钟更新于 2026/4/18

数据库设计不是简单地“建几张表”。

真正的数据库设计，需要先理解业务中的数据、关系和约束，再把它们转换成可以被数据库稳定存储、查询和维护的结构。

这篇笔记按数据库设计的学习顺序整理：

数据库系统的基本概念。
数据库设计流程。
E-R 图与关系模型。
关系代数与 SQL。
规范化与反规范化。
事务、并发控制和完整性约束。

数据库基本概念

数据库是长期存储在计算机中的、有组织的、可共享的数据集合。

数据库管理系统，也就是 DBMS，负责对数据库进行统一管理。常见 DBMS 包括 MySQL、PostgreSQL、SQL Server、Oracle、SQLite 等。

从使用角度看：

数据库负责保存数据。
DBMS 负责管理数据。
应用程序通过 SQL 或驱动程序访问 DBMS。

可以简单理解为：

用户 / 应用程序
      ↓
数据库管理系统 DBMS
      ↓
数据库
      ↓
磁盘文件

数据库系统的特点

数据库系统相比普通文件存储，有几个明显优势。

特点	说明
数据结构化	数据按照表、字段、关系等结构组织
数据共享性高	多个应用可以访问同一份数据
冗余度较低	通过合理设计减少重复数据
数据独立性高	数据结构变化尽量不影响应用程序
统一管理	由 DBMS 负责安全、并发、恢复和完整性

数据库设计的目标，就是在业务表达、查询效率、数据一致性和后期维护之间取得平衡。

三级模式结构

数据库系统通常采用三级模式结构：

外模式。
概念模式。
内模式。

这三个层次从用户视角逐渐靠近底层存储。

层次	英文	说明
外模式	External Schema	用户或应用看到的数据视图
概念模式	Conceptual Schema	数据库整体逻辑结构
内模式	Internal Schema	数据在物理层面的存储方式

可以这样理解：

外模式：不同用户看到的局部视图
概念模式：整个数据库的全局逻辑结构
内模式：数据实际如何存储

例如：

普通用户只能看到自己的订单，这是外模式。
系统中有用户表、商品表、订单表，这是概念模式。
表数据如何存放在磁盘、索引如何组织，这是内模式。

两级映像

三级模式之间通过两级映像联系起来。

映像	作用
外模式 / 概念模式映像	保证逻辑数据独立性
概念模式 / 内模式映像	保证物理数据独立性

逻辑数据独立性指：概念模式发生变化时，尽量不影响外部用户视图。

物理数据独立性指：底层存储方式变化时，尽量不影响上层逻辑结构。

这也是数据库比普通文件系统更容易维护的重要原因。

数据库设计流程

数据库设计一般可以分为几个阶段：

需求分析
  -> 概念结构设计
  -> 逻辑结构设计
  -> 物理结构设计
  -> 数据库实施
  -> 运行与维护

不同教材叫法可能略有差异，但核心思路一致：先理解业务，再抽象模型，再落到具体数据库。

需求分析

需求分析是数据库设计的起点。

这一阶段要弄清楚：

系统需要管理哪些数据。
数据之间有什么关系。
用户会进行哪些查询和操作。
哪些数据必须保持一致。
哪些数据有权限、安全或性能要求。

例如电商系统中，需要分析：

用户如何注册和登录。
商品如何分类和上架。
订单如何创建、支付、取消。
库存如何扣减和恢复。
售后、退款、优惠券是否参与流程。

需求分析做得不清楚，后面的表设计就很容易返工。

概念结构设计

概念结构设计主要使用 E-R 模型描述业务。

E-R 是 Entity-Relationship 的缩写，也就是实体-联系模型。

这一阶段不急着建表，而是先抽象业务中的：

实体。
属性。
联系。

例如：

概念	示例
实体	用户、商品、订单
属性	用户名、手机号、商品价格
联系	用户下单、订单包含商品

概念结构设计的产物通常是 E-R 图。

E-R 图

E-R 图用来描述实体、属性和实体之间的联系。

常见元素：

元素	说明
实体	现实世界中可以独立存在的对象
属性	实体具有的特征
联系	实体之间的关系

例如：

用户 ---- 下单 ---- 订单 ---- 包含 ---- 商品

其中：

用户是实体。
订单是实体。
商品是实体。
下单是用户和订单之间的联系。
包含是订单和商品之间的联系。

实体、属性和联系

实体是业务中需要被记录的对象。

属性是实体的具体信息。

联系是实体之间的业务关系。

以用户为例：

实体：用户
属性：用户ID、用户名、手机号、注册时间

以订单为例：

实体：订单
属性：订单ID、订单编号、订单状态、下单时间、支付时间

用户和订单之间的联系：

一个用户可以创建多个订单
一个订单只能属于一个用户

这就是一对多关系。

联系类型

实体之间常见联系有三种。

联系类型	说明	示例
一对一	一个实体最多对应另一个实体中的一个	用户和身份证信息
一对多	一个实体可以对应多个另一个实体	用户和订单
多对多	两边都可以对应多个	学生和课程

多对多关系通常不能直接落成两个表，需要引入中间表。

例如学生和课程：

学生表
课程表
选课表

选课表保存学生和课程之间的对应关系。

E-R 模型转关系模型

概念结构设计完成后，需要把 E-R 模型转换成关系模型，也就是转换成表结构。

常见转换规则：

E-R 模型	关系模型
实体	转换成一张表
属性	转换成表中的字段
主键	用来唯一标识一条记录
一对多联系	在“多”的一方加入外键
多对多联系	新增中间表

例如用户和订单是一对多关系：

用户表 user
  id
  name

订单表 order
  id
  user_id
  status

order.user_id 就是外键，用来表示订单属于哪个用户。

逻辑结构设计

逻辑结构设计是把概念模型转换成具体的关系模式。

这一阶段需要确定：

表名。
字段名。
字段类型。
主键。
外键。
唯一约束。
非空约束。
表之间的关系。

例如：

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  phone VARCHAR(20) UNIQUE,
  created_at TIMESTAMP NOT NULL
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

逻辑结构设计强调数据结构是否正确表达业务。

物理结构设计

物理结构设计更关注数据库运行效率。

这一阶段通常要考虑：

是否需要索引。
索引建在哪些字段上。
表是否需要分区。
是否需要冗余字段。
数据量增长后如何查询。
读写比例如何。
是否需要缓存配合。

例如订单表可能会根据这些字段建立索引：

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_orders_status ON orders(status);

索引不是越多越好。

索引可以提高查询速度，但会增加写入和维护成本。物理设计要结合真实查询场景，而不是看到字段就加索引。

数据模型

数据模型是对现实世界数据特征的抽象。

常见数据模型包括：

概念模型。
逻辑模型。
物理模型。

其中概念模型常用 E-R 图表示，逻辑模型常用关系模型表示，物理模型关注数据在数据库中的实际存储和访问。

关系模型

关系模型是关系型数据库的基础。

在关系模型中，数据以二维表形式组织。

概念	说明
关系	一张表
元组	表中的一行
属性	表中的一列
域	属性可取值的范围

例如用户表：

id	name	phone
1	张三	13800000000
2	李四	13900000000

这张表就是一个关系。

每一行是一个元组。

每一列是一个属性。

phone 的取值范围就是手机号对应的域。

键

关系模型中常见的键包括：

名称	说明
超键	能唯一标识元组的属性集合
候选键	不含多余属性的最小超键
主键	从候选键中选出的主要标识
外键	引用其他表主键的字段

例如用户表：

users(id, phone, name)

如果 id 和 phone 都能唯一标识用户，那么它们都可以是候选键。

实际设计时通常选择 id 作为主键，phone 加唯一约束。

关系完整性

关系模型通常包含三类完整性约束。

完整性	说明
实体完整性	主键不能为空且不能重复
参照完整性	外键必须引用已经存在的主键，或为空
用户自定义完整性	根据业务定义的约束

例如：

用户 ID 不能为空，这是实体完整性。
订单的 user_id 必须对应真实用户，这是参照完整性。
商品价格不能小于 0，这是用户自定义完整性。

关系代数

关系代数是关系数据库查询语言的理论基础。

常见操作包括：

操作	含义
选择	从表中筛选满足条件的行
投影	从表中选出指定列
并	合并两个关系中的元组
差	找出属于一个关系但不属于另一个关系的元组
笛卡尔积	两个关系的所有元组组合
连接	按条件把两个关系关联起来

对应到 SQL 中：

-- 选择
SELECT * FROM users WHERE city = '北京';

-- 投影
SELECT name, phone FROM users;

-- 连接
SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id;

关系代数不一定在业务开发中直接使用，但它能帮助理解 SQL 的本质。

SQL

SQL 是关系型数据库最常用的操作语言。

常见分类：

分类	作用	示例
DDL	定义数据库结构	`CREATE`、`ALTER`、`DROP`
DML	操作数据	`INSERT`、`UPDATE`、`DELETE`
DQL	查询数据	`SELECT`
DCL	控制权限	`GRANT`、`REVOKE`
TCL	控制事务	`COMMIT`、`ROLLBACK`

基础建表：

CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock INT NOT NULL
);

基础查询：

SELECT id, name, price
FROM products
WHERE price > 100
ORDER BY price DESC
LIMIT 10;

基础更新：

UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock > 0;

规范化

规范化是为了减少数据冗余、避免更新异常、插入异常和删除异常。

如果表设计不合理，可能出现这些问题：

同一份数据在多处重复保存。
修改一处数据时，其他地方忘记同步。
删除某条记录时，连带丢失不该丢失的信息。
插入新数据时，因为缺少其他字段导致无法插入。

规范化的核心是根据函数依赖拆分关系模式。

函数依赖

函数依赖描述属性之间的决定关系。

如果属性 X 的值能够唯一确定属性 Y 的值，就说 Y 函数依赖于 X。

记作：

X -> Y

例如：

用户ID -> 用户名
用户ID -> 手机号

因为只要知道用户 ID，就能确定这个用户的姓名和手机号。

部分依赖和传递依赖

部分依赖通常出现在联合主键中。

如果一个非主属性只依赖联合主键的一部分，而不是依赖整个联合主键，就叫部分依赖。

例如：

(学生ID, 课程ID) -> 学生姓名

学生姓名只依赖学生 ID，不依赖课程 ID，因此这是部分依赖。

传递依赖指一个属性通过另一个非主属性间接依赖主键。

例如：

学生ID -> 学院ID
学院ID -> 学院名称

那么：

学生ID -> 学院名称

就是传递依赖。

第一范式

第一范式要求属性值不可再分。

也就是说，表中的每个字段都应该是原子值。

不符合第一范式的例子：

id	name	phones
1	张三	13800000000,13900000000

phones 中存了多个手机号，不够原子。

更合理的设计是拆成用户表和手机号表：

users(id, name)
user_phones(id, user_id, phone)

第二范式

第二范式要求在满足第一范式的基础上，非主属性完全依赖候选键。

它主要用于消除部分依赖。

例如选课表：

student_course(student_id, course_id, student_name, course_name, score)

主键是：

(student_id, course_id)

但：

student_id -> student_name
course_id -> course_name

这说明 student_name 和 course_name 只依赖主键的一部分，需要拆表。

拆分后：

students(student_id, student_name)
courses(course_id, course_name)
student_course(student_id, course_id, score)

第三范式

第三范式要求在满足第二范式的基础上，消除传递依赖。

例如：

students(student_id, student_name, college_id, college_name)

如果：

student_id -> college_id
college_id -> college_name

那么 college_name 通过 college_id 传递依赖于 student_id。

更合理的设计：

students(student_id, student_name, college_id)
colleges(college_id, college_name)

BCNF

BCNF 是比第三范式更严格的范式。

它要求每一个决定因素都必须是候选键。

可以简单理解为：如果一个属性集合能决定其他属性，那么它本身就应该具备唯一标识能力。

BCNF 通常用于处理第三范式仍然无法完全解决的异常情况。

学习阶段可以先掌握 1NF、2NF、3NF，再理解 BCNF。

模式分解

规范化往往会带来模式分解，也就是把一张大表拆成多张小表。

模式分解需要关注两个性质：

性质	说明
无损连接	拆分后的表能够通过连接还原原始信息
保持函数依赖	拆分后仍能维护原有的函数依赖约束

如果分解后无法还原原始数据，就会造成信息丢失。

如果分解后无法检查原有约束，后续维护会变复杂。

反规范化

规范化能减少冗余，但不一定总是性能最优。

在高频查询场景中，有时会有意保留冗余字段，这就是反规范化。

例如订单表中保存商品名称：

orders
order_items(product_id, product_name_snapshot, price_snapshot)

即使商品表中的名称后续发生变化，订单明细仍然保留下单时的商品快照。

反规范化常见场景：

查询非常频繁。
多表关联成本较高。
需要保存历史快照。
读多写少。

反规范化不是随意冗余，而是为了明确的查询性能或业务一致性目标。

数据库控制

数据库控制主要包括：

事务管理。
并发控制。
完整性控制。
安全控制。
数据恢复。

这些能力保证数据库在多人访问、系统异常和业务约束下仍然可靠。

事务

事务是一组数据库操作，要么全部成功，要么全部失败。

典型例子是转账：

A 账户扣款
B 账户加款

这两个操作必须作为一个整体执行。如果 A 扣款成功，但 B 加款失败，数据就会不一致。

事务具有 ACID 特性。

特性	含义
原子性 Atomicity	事务中的操作要么全部成功，要么全部失败
一致性 Consistency	事务执行前后，数据必须满足约束
隔离性 Isolation	并发事务之间不能互相干扰
持久性 Durability	事务提交后，结果应永久保存

并发控制

多个事务同时执行时，可能出现并发问题。

常见问题：

问题	说明
脏读	一个事务读到了另一个事务尚未提交的数据
不可重复读	同一事务中两次读取同一行，结果不同
幻读	同一事务中两次查询范围数据，结果行数不同

数据库通过锁、MVCC、事务隔离级别等机制处理并发问题。

封锁

封锁是并发控制的重要手段。

常见锁类型：

锁	说明
共享锁	允许多个事务读取，同一时间不允许写
排他锁	当前事务独占资源，其他事务不能读写

常见封锁协议包括：

一级封锁协议。
二级封锁协议。
三级封锁协议。
两段锁协议。

两段锁协议要求事务加锁和解锁分为两个阶段：

扩展阶段：只加锁，不解锁
收缩阶段：只解锁，不加锁

它可以保证冲突可串行化，但不能完全避免死锁。

完整性约束

完整性约束用于保证数据库中的数据符合规则。

常见约束：

约束	作用
主键约束	唯一标识记录，不能重复或为空
外键约束	保证引用关系有效
唯一约束	保证字段值不重复
非空约束	保证字段不能为空
检查约束	保证字段满足指定条件

例如：

CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) CHECK (price >= 0)
);

这里 price >= 0 就是检查约束。

数据库设计建议

实际设计数据库时，可以按下面的顺序思考：

先理解业务对象和流程。
抽象实体、属性和联系。
画出 E-R 图。
把 E-R 图转换成表结构。
确定主键、外键和约束。
根据查询场景设计索引。
使用规范化减少冗余。
必要时通过反规范化提升查询效率。

不要一开始就急着写 SQL。

好的数据库设计，往往来自对业务关系的清晰理解。

小结

数据库设计可以抓住三条主线：

结构设计：从业务实体到 E-R 图，再到表结构。
数据约束：通过主键、外键、范式和完整性约束保证数据正确。
运行控制：通过事务、并发控制和恢复机制保证数据可靠。

如果把数据库只看成“存数据的地方”，很容易设计出难维护的表。

如果把数据库看成“业务关系和数据规则的长期表达”，设计思路就会清晰很多。