数据库设计¶

概述¶

数据库设计属于系统设计的范畴，通常把使用数据库的系统称为数据库应用系统，把对数据库应用系统的设计成为数据库设计。

按照软件工程对系统生命周期的定义，软件生命周期分为6个阶段：

制定计划
需求分析
系统设计
程序编制
测试以及运行维护

在数据库设计中，也参照这种划分，划分数据库应用系统的生命周期：

数据库规划
需求描述与分析
数据库与应用程序设计
数据库设计与实现
测试
运行维护

数据库设计¶

数据库设计是对数据进行组织和结构化的过程，关键问题是数据模型的设计。一个良好的设计对于数据库系统至关重要，它可以减少系统中的数据冗余、确保数据的一致性和完整性，同时易于维护和扩展。

数据库设计是指对于一个给定的应用环境，构造（设计）优化的数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，使之能够有效地存储和管理数据，满足各种用户的应用需求，包括信息管理要求和数据操作要求。

数据库设计的目标 ：是为用户和各种应用系统提供一个信息基础设施和高效率的运行环境。

数据库设计的基本任务 ：是根据用户的信息需求、处理需求和数据库的支持环境(包括硬件、操作系统和DBMS)，设计出数据库模式(包括外模式、逻辑模式和内模式)及其典型的应用程序。

我们在设计数据表的时候，要考虑很多问题。比如：

用户都需要什么数据?需要在数据表中保存哪些数据？
如何保证数据表中数据的正确性，当插入、删除、更新的时候该进行怎样的约束检查?
如何降低数据表的数据冗余度，保证数据表不会因为用户量的增长而迅速扩张?
如何让负责数据库维护的人员更方便地使用数据库?
使用数据库的应用场景也各不相同，可以说针对不同的情况，设计出来的数据表可能千差万别。

现实情况中，面临的场景:

当数据库运行了一段时间之后，我们才发现数据表设计的有问题。重新调整数据表的结构，就需要做数据迁移，还有可能影响程序的业务逻辑，以及网站正常的访问。

如果是糟糕的数据库设计可能会造成以下问题:

数据冗余、信息重复，存储空间浪费
数据更新、插入、删除的异常
无法正确表示信息
丢失有效信息
程序性能差

良好的数据库设计则有以下优点:

节省数据的存储空间
能够保证数据的完整性
方便进行数据库应用系统的开发

总之，开始设置数据库的时候，我们就需要重视数据表的设计。为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。

用户需求分析
- 产出：需求说明文档，数据字典和数据流图
概念结构设计
逻辑结构设计
物理结构设计
数据库实施阶段
数据库运行和维护阶段

ER图¶

实体关系图（（Entity-Relationship Diagram））也被称为 ERD、ER 图、实体联系模型、实体联系模式图或 ER 模型，是一种用于数据库设计的结构图。它描述了数据库中的实体以及这些实体之间的关系。ERD 包括**实体**、**属性**以及**关系**三个部分。

一幅 ERD 包含不同的符号和连接符，用于显示两个重要的信息： 系统范围内的主要实体，以及这些实体之间的相互关系

E-R图叫实体联系图，用图形化表示数据库的全局逻辑结构。

在 ER 模型中，实体显示为 圆角矩形 ，其名称位于上方，其属性列在实体形状的主体中。实体代表了一种对象或者概念。例如，员工、部门和职位都可以称为实体。
分成两部分的矩形 代表实体集， 阴影部分 是实体集名字，第二部分包括实体集属性，主码属性带下划线。属性表示实体的某种特性，例如员工拥有姓名、性别、工资、所在部门等属性。
菱形代表联系集。
未分割的矩形 代表联系集的属性。
联系集与其属性以虚线连接。
线段将实体集与联系集连接。

详细可以参考visual-paradigm

进一步来说，ERD 可以按照抽象层次分为三种：

概念 ERD，即概念数据模型。描述系统中存在的业务对象以及它们之间的联系，一般给业务分析人员使用。上图就是一个概念 ERD。
逻辑 ERD，即逻辑数据模型。对概念数据模型进一步的分解和细化，转换为关系模型。同时，还需要引入规范化过程，对关系模式进行优化。
物理 ERD，即物理数据模型。物理 ERD 是针对具体数据库的设计描述，需要为每列指定类型、长度、可否为空等属性，为表增加主键、外键以及索引等

数据流图¶

从原理上讲，只要纸足够大，一个软件系统的分析模型就可以画在一张纸上。然而，一个复杂的软件系统可能涉及上百个加工和上百个数据流，甚至更多。

如果将它们画在一张图上，则会十分复杂，不易阅读，也不易理解。因此，根据自顶向下逐层分解的思想，可以将数据流图按照层次结构来绘制。

DFD 中使用级别或层来表示有关系统或过程的渐进式详细程度。这些级别包括：

级别 0：也称为"上下文图"，这是最高级别，它仅包含一个流程节点（"流程0"），它概括了与外部实体相关的整个系统的功能。

所有外部实体以及进出它们的主要数据流都显示在上下文图上。
该图不包含任何数据存储。
上下文图中的进程名称应该是信息系统的名称。例如，评分系统、订单处理系统、注册系统。

级别1：仍然是系统的相对宽泛的视图，但包含子流程和更多细节。

级别2：提供更多细节，并根据需要继续分解子流程。

级别3：虽然这种详细程度并不常见，但复杂系统可以从该级别的表示中受益。

数据流图 (DFD) 用于表示业务信息系统中的数据流，它表达了系统中的据传从输入到存储间所涉及的程序。

数据流图可以分为 逻辑形 和 物理形 。

逻辑数据流图描述了用以完成某业务功能所涉及的、业务层面的数据流动
物理数据流图描述系统层面的数据流动。

数据流图中的各种规则：

程序：程序表示对数据的处理过程，它可以接收输入，并产生输出，每个程序都有一个名称来标识它所执行的功能，比如：申请付款，计算佣金等。用 圆角矩形 表示。
数据存储: 表示暂时存储的数据 , 数据存储的粒度是以表为单位。用 半框矩形 表示。
- 数据存储必须以数据流连接到程序。
- 每个数据存储必须至少有一个输入数据流和至少一个输出数据流（即使输出数据流是一个确认讯息）。
- 方向：流向文件的数据流表示向文件内写入内容 , 从文件流出的数据流表示从文件读取内容 ;
外部实体(实体)：软件系统之外的人员/组织，实体用 直角矩形 表示。
数据流：数据流是数据从信息系统的一个部分移动到另一个部分的路径。
- 带输入箭头的直线即为 输入数据流
- 带输入箭头的直线即为 输入数据流
- 由于每个程序都将数据从一种形态转换为另一种形态，故每个程序必须至少有一个输入数据流和一个输出数据流。
- 数据流图的一大原则是数据不能自行转换成另一形态，数据必须经由某程序的处理才可被分发至系统的某个部份。通过这条规则，我们可以非常容易地识别出错误画法。

UML图¶

UML 是 Unified Model Language 的缩写，中文是 统一建模语言 ，是由一整套图表组成的标准化建模语言。

在软件开发中，当系统规模比较复杂时，需要用图形抽象地来表达复杂的概念，让整个软件设计更具有可读性，可理解性，以便尽早发现软件设计时存在的潜在问题，从而降低开发风险。同时，也极大地方便了业务人员与开发人员之间的交流。

UML提供了极富表达能力的建模语言，可以让软件开发过程中的不同人员分别得到自己感兴趣的信息。

Page-Jones 在《Fundamental Object-Oriented Design in UML》一书中总结了UML的主要目的，如下：

为用户提供现成的、有表现力的可视化建模语言，以便他们开发和交换有意义的模型。
为核心概念提供可扩展性 (Extensibility) 和特殊化 (Specialization) 机制。
独立于特定的编程语言和开发过程。
为了解建模语言提供一个正式的基础。
鼓励面向对象工具市场的发展。
支持更高层次的开发概念，如协作，框架，模式和组件。
整合最佳的工作方法 (Best Practices)。

UML图分为结构图和行为图：

结构图分为类图、轮廓图、组件图、组合结构图、对象图、部署图、包图。
行为图又分活动图、用例图、状态机图和交互图。
交互图又分为序列图、时序图、通讯图、交互概览图。

范式和反范式¶

SQL是一门声明式的编程语言，就像 Lisp、Haskell或者 XSLT。SQL使用集合（set）作为根本的数据结构，而面向对象的语言使用的是对象（object）。受过传统培训的软件开发人员被所谓的"阻抗失配"所阻碍，因此很多程序员转而使用现成的面向对象的库，以此来避免学习如何高效地使用SQL

范式¶

规范化（Normalization）是数据库设计的一系列原理和技术，主要用于减少表中数据的冗余，增加完整性和一致性。

我们在设计关系型数据库模型的时候，需要对关系内部各个属性之间联系的合理化程度进行定义。

这就有了不同等级的规范要求，这些规范要求被称为范式（NF）。你可以把范式理解为，一张数据表的设计结构需要满足的某种设计标准的级别。

在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。可以理解为，一张数据表的设计结构需要满足的某种设计标准的级别。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

范式的英文名称是 Normal Form，简称 NF。它是英国人E.F.Codd在上个世纪70年代提出关系数据库模型后总结出来的。范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。

目前关系型数据库一共有 6 种范式，按照范式级别，从低到高分别是：

1NF（第一范式）
2NF（第二范式）
3NF（第三范式）
BCNF（巴斯 - 科德范式）
4NF（第四范式）
5NF（第五范式，又叫做完美范式）

数据库的范式设计越高阶，冗余度就越低，同时高阶的范式一定符合低阶范式的要求，比如满足 2NF 的一定满足 1NF，满足 3NF 的一定满足 2NF，依次类推。

一般来说数据表的设计应尽量满足 3NF。但也不绝对，有时候为了提高某些查询性能，我们还需要破坏范式规则，也就是反规范化。

1NF指的是数据库表中的任何属性都是原子性的，不可再分。

这很好理解，我们在设计某个字段的时候，对于字段 X 来说，就不能把字段 X 拆分成字段 X-1 和字段 X-2。事实上，任何的 DBMS 都会满足第一范式的要求，不会将字段进行拆分。

反范式是一种试图解决问题的方法，但通常会同时引发别的问题。一般来说，数据仓库（Data Warehouse）和在线分析系统（OLAP）会较多使用到反规范化的技术，因为它们以复杂查询和报表分析为主。

逻辑数据库设计反模式

在你开始编码之前，需要决定数据库里存储什么信息以及最佳的数据组织方式和内在关联方式。这包含了如何设计数据库的表、字段和关系。

物理数据库设计反模式

在知道了需要存储哪些数据之后，使用你所知的RDBMS技术特性尽可能高效地实现数据管理。这包含了定义表和索引，以及选择数据类型。你也要使用 SQL 的DDL语句，比如CREATE TABLE语句。

查询反模式

你需要向数据库中添加然后获取数据。SQL的查询是使用"数据操作语言"来完成，比如SELECT 、 UPDATE 和 DELETE 语句。

应用程序开发反模式

SQL应该会用在使用 C++、Java、Php、Python或者 Ruby等语言构建的应用程序中。在应用程序中使用SQL的方式有好有坏，该部分内容描述了一些常见错误。

多值属性¶

将数据库中原来存储单一用户标识的字段改成使用逗号分隔的用户标识列表，似乎是一个很简单且合理的解决方案。

程序员通常使用逗号分隔的列表来避免在多对多的关系中创建交叉表，我将这种设计方式定义为一种反模式，称为乱穿马路（Jaywalking），因为乱穿马路也是避免过十字路口的一种方式。

设计一个单值表列是非常简单的：你可以选择一个 SQL 的内置数据类型，以该类型来存储这个表项的数据，比如整型、日期类型或者字符串。但是如何才能做到在一列中存储一系列相关数据的集合呢？

很自然，使用VARCHAR类型字段，并用逗号分隔对应的数据。

选择合适的分隔符

如果存储一个字符串列表而不是数字列表，列表中的某些条目可能会包含分隔符。使用逗号作为分隔符可能会有问题。当然，你到时候可以再换一个字符，但你能确保这个新字符永远不会出现在条目中吗？

列表长度限制

你能在一个 VARCHAR(30) 的结构中存多少数据呢？这依赖于每个条目的长度。如果每个条目只有2个字符长，那你能存10个条目（包括逗号）。但如果每个条目的长度为6，你就只能存4个了。

你怎能确定 VARCHAR(30) 能够支持你未来所需的最长列表呢？多长才够长？

合理使用反模式

出于性能优化的考量，可能在数据库的结构中需要使用反规范化的设计。将列表存储为以逗号分隔的字符串就是反规范化的一个实例。

应用程序可能会需要逗号分隔的这种存储格式的数据，也可能没必要获取列表中的单独项。

同样，如果应用程序接收的源数据是有逗号分隔的格式，而你只需要存储和使用它们并且不对其做任何修改，完全没必要分开其中的值。

树形结构¶

在程序开发中，我们常遇到用树型结构来表示某些数据间的关系，如企业的组织架构、商品的分类、操作栏目等，目前的关系型数据库都是以二维表的形式记录存储数据，而树型结构的数据如需存入二维表就必须进行Schema设计。

存在递归关系的数据很常见，数据常会像树或者以层级方式组织。

在树形结构中，实例被称为节点（node）。每个节点有多个子节点和一个父节点。最上层的节点叫根（root）节点，它没有父节点。最底层的没有子节点的节点叫叶（leaf）。而中间的节点简单地称为非叶（nonleaf）节点。

在层级数据中，你可能需要查询与整个集合或其子集相关的特定对象，例如下述树形数据结构：

组织架构图。职员与经理的关系是典型的树形结构数据，出现在无数的 SQL书籍与论题中。

在组织架构图中，每个职员有一个经理，在树结构中表现为职员的父节点。同时，经理也是一个职员。
话题型讨论。正如引言中介绍的，树形结构也能用来表示回复评论的评论链。在这种树中，评论的子节点是它的回复。

邻接表¶

在很多书籍或文章中，最常见的简单解决方案是添加 parent_id 字段来确定树的父子关系。这样的设计叫做邻接表。

-- 假设用树形结构来描述一个部门的组织架构

-- DDL  用id来标识一个人，用manager_id来表示向上一个人汇报
CREATE TABLE db_test.employees_mgr (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR ( 100 ) NOT NULL,
    manager_id INT NULL,
    INDEX ( manager_id ),
    FOREIGN KEY ( manager_id ) REFERENCES employees_mgr ( id ) 
);


-- test data
insert into db_test.employees_mgr values 
(333, "Yasmina", null), /* Yasmina is the CEO (manager_id is null) */
(198, "John", 333),     /* John has id 198 and reports to 333 (Yasmina) */
(692, "Tarek", 333),    /* Tarek has id 692 and reports to 333 (Yasmina) */
(29, "Pedro ", 198),    /* Pedro has id 29 and reports to 198 (John) */
(4610, "Sarah", 29);    /* Sarah has id 4610 and reports to 29 (Pedro) */



-- 查询一个节点的所有后代或所有父节点。
-- 要查询出如下这种树形效果：查出每个人的汇报对象

+------+---------+--------------------+                                                                                                                                                                          
| id   | name    | path               |                                                                                               
+------+---------+--------------------+                                                                
|  333 | Yasmina | 333                |                                                                                           
|  198 | John    | 333->198           |                                                                                            
|  692 | Tarek   | 333->692           |                                                                                           
|   29 | Pedro   | 333->198->29       |                                                                                              
| 4610 | Sarah   | 333->198->29->4610 |                                                                                             
+------+---------+--------------------+                                                                                                 
5 rows in set (0.00 sec)                                                                                              
mysql>   


-- MySQL8.0中支持with子句
-- 通过recursive递归CTE来查询用户的层级关系
-- 规划一个ID，name，path这样的一个层级临时表

with recursive employee_paths (id, name, path) as (
     -- 先查最顶层节点
     select id, name, cast(id as char(200))
     from db_test.employees_mgr
     where manager_id is null
     union all
     -- 递归查询
     select e.id, e.name, concat(ep.path, '->', e.id)
     from employee_paths as ep
     join db_test.employees_mgr as e
     on ep.id = e.manager_id
    )
     select * from employee_paths;

这种方案的优点很明显：结构简单易懂，由于互相之间的关系只由一个parent_id维护，所以增删改都是非常容易，只需要改动和他直接相关的记录就可以。

缺点当然也是非常的突出：由于直接地记录了节点之间的继承关系，因此对Tree的任何CRUD操作都将是低效的，这主要归根于频繁的“递归”操作，递归过程不断地访问数据库，每次数据库IO都会有时间开销。举个例子，如果想要返回所有节点，也就是某个人的所有子孙节点，看似很简单的操作，就需要用到一堆递归。

合理地使用反范式，当然，这种方案并非没有用武之地，在树的层级比较少的时候就非常实用。这种方法的优点是存储的信息少，查直接上司和直接下属的时候很方便，缺点是多级查询的时候很费劲。所以当只需要用到直接上下级关系的时候，用这种方法还是不错的，可以节省很多空间。

物化路径表¶

邻接表的缺点之一是从树中获取一个给定节点的所有祖先的开销很大。路径枚举的设计通过将所有祖先的信息联合成一个字符串，并保存为每个节点的一个属性，很巧妙地解决了这个问题。

路径枚举是一个由连续的直接层级关系组成的完整路径。如 /usr/local/lib 的 UNIX 路径是文件系统的一个路径枚举，其中 usr 是 local 的父亲，这也就意味着 usr 是 lib 的祖先。

在 Comments 表中，我们使用类型为 VARCHAR 的 path 字段来代替原来的 parent_id 字段。这个 path 字段所存储的内容为当前节点的最顶层的祖先到它自己的序列，就像 UNIX的路径一样，你甚至可以使用'/'作为路径中的分割符。

物化路径其实更加容易理解，其实就是在创建节点时，将节点的完整路径进行记录。

-- 查询某一节点下的所有子节点:（以Fruit为例）
SET @path = (SELECT path FROM pathTree WHERE node_name = 'Fruit');
SELECT * FROM pathTree WHERE path like CONCAT(@path,'/%');

-- 如何查询直属子节点？需要采用MySQL的正则表达式查询：
SET @path = (SELECT path FROM pathTree WHERE node_name = 'Fruit');
SELECT * FROM pathTree WHERE path REGEXP CONCAT(@path,'/','[0-9]$');

-- 查询任意节点的所有上级：（以Yellow为例）：
SET @path = (SELECT path FROM pathTree WHERE node_name = 'Yellow');
SELECT * FROM pathTree WHERE @path LIKE CONCAT(path, '%') AND path <> @path;

-- 插入新增数据：
SET @parent_path = ( SELECT path FROM pathTree WHERE node_name = 'Fruit');
INSERT INTO pathtree (path,node_name) VALUES (CONCAT(@parent_path,'/',LAST_INSERT_ID()+1),'White')

此方案的缺点是树的层级太深有可能会超过PATH字段的长度，所以其能支持的最大深度并非无限的。

如果层级数量是确定的，可以再将所有的列都展开，比较适用于于类似行政区划、生物分类法（界、门、纲、目、科、属、种）这些层级确定的内容。

闭包表¶

比如，地理区域、位置信息存储，地理信息按照层级划分，会分为很多层级，就拿中国的行政区域划分为例，简单的省-市-县-镇-村就要五个级别。

如果系统涉及到跨境的国际贸易，那么存储的地理信息层级会更加深。

闭包表由Closure Table翻译而来，通过父节点、子节点、两节点距离来描述一棵树空间换时间的思想。

Closure Table，一种更为彻底的全路径结构，分别记录路径上相关结点的全展开形式。能明晰任意两结点关系而无须多余查询，级联删除和结点移动也很方便。但是它的存储开销会大一些，除了表示结点的Meta信息，还需要一张专用的关系表。

闭包表，它记录了树中所有节点的关系，不仅仅只是直接父子关系，它需要使用两张表，除了节点表本身之外，还需要使用一张关系表，用来存储祖先节点和后代节点之间的关系（同时增加一行节点指向自身）,并且根据需要，可以增加一个字段，表示深度。

CREATE TABLE `village` (
    `code` VARCHAR(255) PRIMARY KEY, 
    `name` VARCHAR(255), 
    `streetCode` VARCHAR(255) REFERENCES `street` (`code`) ON DELETE SET NULL ON UPDATE CASCADE, 
    `provinceCode` VARCHAR(255) REFERENCES `province` (`code`) ON DELETE SET NULL ON UPDATE CASCADE, 
    `cityCode` VARCHAR(255) REFERENCES `city` (`code`) ON DELETE SET NULL ON UPDATE CASCADE, 
    `areaCode` VARCHAR(255) REFERENCES `area` (`code`) ON DELETE SET NULL ON UPDATE CASCADE

);

-- 部门信息表
CREATE TABLE `departments` (
  `id` int NOT NULL COMMENT 'ID',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '部门名称',
  `parent_id` int DEFAULT NULL COMMENT '父ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='部门表';

-- 部门信息闭包表
CREATE TABLE `departments_closure_table` (
  `ancestor` int NOT NULL COMMENT '祖先节点',
  `descendant` int NOT NULL COMMENT '后代节点',
  PRIMARY KEY (`ancestor`,`descendant`),
  KEY `fk_descendant` (`descendant`),
  CONSTRAINT `fk_ancestor` FOREIGN KEY (`ancestor`) REFERENCES `departments` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_descendant` FOREIGN KEY (`descendant`) REFERENCES `departments` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='部门信息闭包表';


-- 填充部门表
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (1, '集团总部', NULL);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (2, '华北总部', 1);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (3, '华南总部', 1);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (4, '华东总部', 1);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (5, '华中总部', 1);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (6, '华西总部', 1);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (7, '北京子公司', 2);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (8, '天津子公司', 2);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (9, '河北子公司', 2);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (10, '广东子公司', 3);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (11, '广西子公司', 3);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (12, '海南子公司', 3);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (13, '四川子公司', 6);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (14, '重庆子公司', 6);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (15, '贵州子公司', 6);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (16, '云南子公司', 6);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (17, '成都办事处', 13);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (18, '广元办事处', 13);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (19, '雅安办事处', 13);
INSERT INTO `hytto_cs`.`departments`(`id`, `name`, `parent_id`) VALUES (20, '绵阳办事处', 13);


-- 填充闭包信息表
INSERT INTO departments_closure_table (ancestor, descendant, depth)
WITH RECURSIVE cte AS (
  SELECT id as ancestor, id as descendant, 0 as depth
  FROM departments
  UNION ALL
  SELECT cte.ancestor, departments.id, cte.depth + 1
  FROM cte
  JOIN departments ON cte.descendant = departments.parent_id
)
SELECT ancestor, descendant, depth
FROM cte
WHERE ancestor != descendant;




-- 按照部门ID升序进行分页查询，每页显示5个部门
SELECT d.*
FROM departments AS d
JOIN departments_closure_table AS ct ON d.id = ct.descendant
WHERE ct.ancestor = 1 -- 根部门的ID
ORDER BY d.id
LIMIT 0, 5;

主键¶

每个了解数据库设计的人都知道，主键对于一张表来说是一个很重要，甚至必需的部分。这确实是事实，主键是好的数据库设计的一部分。主键是数据库确保数据行在整张表中唯一性的保障，它是定位到一条记录并且确保不会重复存储的逻辑机制。主键也同时可以被外键引用来建立表与表之间的关系。

难点是选择哪一列作为主键。大多数表中的每个属性的值都有可能被很多行使用。例如一个人的姓和名就一定会在表中重复出现，即使电子邮件地址或者美国社保编号或者税单编号也不能保证绝对不会重复。

在这样的表中，需要引入一个对于表的域模型无意义的新列来存储一个伪值。这一列被用作这张表的主键，从而通过它来确定表中的一条记录，即便其他的列允许出现适当的重复项。这种类型的主键列我们通常称其为伪主键或者代理键。

大多数的数据库提供一种和当前处理事务无关的底层方案，来确保每次都能生成全局唯一的一个整数作为伪主键，即使客户端此时正发起并发操作。

一张没有主键的表就好像你的 MP3 播放列表里没有歌名一样。你依然可以听歌，但无法找到想听的那首歌，也没办法确保播放列表中没有重复的歌曲。

伪主键直到 SQL:2003才成为一个标准，因而每个数据库都使用自己特有的 SQL扩展来实现伪主键，甚至不同数据库中对于伪主键都有不同的名称（不同的表述）

名称	数据库
AUTO_INCREMENT	MySQL
GENERATOR	Firebird, InterBase
IDENTITY DB2	Derby, Microsoft SQL Server, Sybase
ROWID	SQLite
SEQUENCE DB2	Firebird, Informix, Ingres, Oracle, PostgreSQL
SERIAL	MySQL, PostgreSQL

虽然各家数据库产品的伪主键叫法不同，但是给伪主键指派的列名，确是出奇的一致，那就是 id。

外键¶

关系数据库的设计基本上可以说就是关于每张独立表之间的关系的设计。引用完整性是合理的数据库设计和操作的非常重要的一部分。

当一列或者多列声明了外键约束后，这些列中的数据必须在其父表（即所引用的表）的主键列或者唯一字段的列中存在。

物理外键是我们学习数据库原理和设计时都会遇到的章节，它的主要优势是可以通过数据库实现强制的 Referential Integrity，即引用完整性。但这样的完整性使用逻辑外键也完全能实现，有人认为逻辑外键由于完全依赖业务代码所以无法真正保证完整性，但这其实是个伪命题，因为物理外键也是由「人」来设置的，你只能保证设置过的物理外键能保证引用完整性，至于那些没考虑到的、设计错误的数据关联关系仍然是物理外键无法解决的，在这一点上物理外键和逻辑外键是没有实质区别的。而实际上当今的云原生架构在数据层面追求的是分布式和最终一致性，单个 DB 存储所有数据的时代早已过去，数据在服已务间流转经是常态，此外国内场景下很多数据也不被允许直接物理删除，物理外键的作用在现代架构下变得微乎其微。物理外键不是银弹，它甚至都没有成为银弹的实力。

说到劣势，物理外键在现代后端架构中的缺点已经越发明显。分场景分析如下：

对于传统企业应用，交付后几乎没有大面积迭代，使用物理外键是无可厚非的，这也是对传统软件开发模式和架构的传承。但现在有越来越多的企业选择使用 SaaS 或自主进行研发和维护，这也就意味着产品的迭代会比此前频繁得多，进而变为下文提到的流量小但迭代频繁的项目。
玩具型项目用不用外键都没有区别。
大流量项目使用物理外键无疑是在埋坑，抛开颇受争议的性能问题不谈，物理外键无法满足分库分表、单元化等现代架构设计的需要，甚至在这些架构下还会成为累赘要额外花费时间改造掉。
小流量项目的迭代速度可不慢，领域模型很难稳定下来，而使用了物理外键也就意味着系统是基于数据库进行的建模，那么当前的物理外键设计迟早有一天要面临变更，这所带来的维护成本（改表困难、业务拆分和聚合困难等）是巨大的，这也是为什么现在很少有人使用存储过程的原因。

在人员职责上，DBA 与业务强耦合本身就是不合理的，这和为什么要做前后端分离是一个道理，这也是为什么当今很多互联网公司会选择一名 DBA 对接一个后端大组甚至事业部的原因，DBA 的职责已经下沉到更核心的数据库稳定性和性能提升上。而在架构中的分层职责上，在持久层耦合业务逻辑是非常不明智的，因为这意味着你的架构会严重依赖某个数据库选型甚至某个特定版本数据库的功能，领域模型与数据模型的耦合也会产生很多人噩梦中的一个 Service 层走天下的情况，业务逻辑很难做进一步的抽象和拆分，至于读写模型分离、CQRS 也就是更不可能的事情了。

在架构中的分层职责上，在持久层耦合业务逻辑是非常不明智的，因为这意味着你的架构会严重依赖某个数据库选型甚至某个特定版本数据库的功能，领域模型与数据模型的耦合也会产生很多人噩梦中的一个 Service 层走天下的情况，业务逻辑很难做进一步的抽象和拆分，至于读写模型分离、CQRS 也就是更不可能的事情了。

使用物理外键能带来收益非常有限，但隐性成本（只要业务还在发展，那未来早晚会变为显性成本）却非常高，其本身又可以被逻辑外键所替代

EAV模型¶

假设要做一个电商的商品管理，有一张商品表。我们先卖一些衣服，需要管理衣服的尺码、颜色、款式等信息，有一天需要卖电脑了，电脑需要主板、CPU、显卡、内存、硬盘、散热等信息，过几天又需要卖手机了，手机有颜色、版本、存储容量、套餐类型等等信息，数据库如何设计？

方案一：新增字段每次新增商品，需要支持不同的信息的话就不停的加字段。

ID Name 尺码颜色款式主板 CPU 显卡内存存储散热版本套餐类型 1 T恤 M 白色喜羊羊 NULL NULL NULL NULL NULL NULL NULL NULL 2 外星人电脑 NULL NULL NULL A i99 RTX8090 32G 2T 水冷 NULL 键鼠套装 3 香蕉手机 NULL 五彩斑斓黑 NULL NULL 晓龙999 NULL 12G 512G 风扇 Pro Plus MAX 碎屏险套餐

这样会造成以下问题：

实现成本高，每次添加商品都需要进行前后端开发、调试，浪费时间和人力。
需要动态变更表结构，在生产环境中安全性太低。
浪费资源，数据库的字段可能会越来越多，而很多字段大部分商品都是不需要的，需要设置为NULL，导致内存大量浪费。
扩展能力一般，有上限。

Entity-Attribute-Value (wiki)是一种数据模型，用于以节省空间的方式对实体进行编码。

Entity：实体，代表一个业务对象，比如上面的例子里的商品。
Attribute：对象的属性，属性并不是作为实体单独的一列来进行存放，而是存储在一组单独的数据库表中。
Value：指特定属性所关联的值。

在现代面向对象的编程模型中，不同的对象类型可能是相连的。比如，多个对象都可能是从同一个基类派生而来，它们既是实际子类的实例，也同时是父类的实例。我们可能想仅使用一张表来存储所有这些不同类型的对象，这样能方便进行比较和计算。但我们也需要将不同的子类分开存储，因为每个子类都有一些特殊的属性，和其他的子类甚至父类都不能共用。

数据库设计¶

概述¶

数据库设计¶

ER图¶

数据流图¶

UML图¶

范式和反范式¶

范式¶

多值属性¶

树形结构¶

邻接表¶

物化路径表¶

闭包表¶

主键¶

外键¶

EAV模型¶

多列属性¶