数据库设计¶

概述¶

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

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

• 制定计划

• 需求分析

• 系统设计

• 程序编制

• 测试以及运行维护

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

• 数据库规划

• 需求描述与分析

• 数据库与应用程序设计

• 数据库设计与实现

• 测试

• 运行维护

数据库设计¶

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

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

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

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

三大模式¶

外模式

• 外模式又称子模式或用户模式，对应于用户级。 它是某个或某几个用户所看到的数据库的数据视图，是与某一应用有关的数据的逻辑表示。外模式是从模式导出的一个子集，包含模式中允许特定用户使用的那部分数据。用户可以通过外模式描述语言来描述、定义对应于用户的数据记录(外模式)，也可以利用数据操纵语言(Data Manipulation Language，DML)对这些数据记录进行操作。外模式反映了数据库系统的用户观。

• 概念模式又称模式或逻辑模式，对应于概念级。 它是由数据库设计者综合所有用户的数据，按照统一的观点构造的全局逻辑结构，是对数据库中全部数据的逻辑结构和特征的总体描述，是所有用户的公共数据视图(全局视图)。它是由数据库管理系统提供的数据模式描述语言(Data Description Language，DDL)来描述、定义的。概念模式反映了数据库系统的整体观。

• 内模式又称存储模式，对应于物理级。 它是数据库中全体数据的内部表示或底层描述，是数据库最低一级的逻辑描述，它描述了数据在存储介质上的存储方式和物理结构，对应着实际存储在外存储介质上的数据库。内模式由内模式描述语言来描述、定义的。内模式反映了数据库系统的存储观。内模式是数据库模型中的一部分，用于描述数据库在物理存储层面上的组织方式和访问方法。它定义了数据库中数据的实际存储结构和存储方式，以及数据的物理表示和存储位置。内模式主要面向数据库管理系统（DBMS）的开发者和维护者，用于实现和优化数据库的存储和访问。

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

• 用户都需要什么数据?需要在数据表中保存哪些数据？

• 如何保证数据表中数据的正确性，当插入、删除、更新的时候该进行怎样的约束检查?

• 如何降低数据表的数据冗余度 ，保证数据表不会因为用户量的增长而迅速扩张?

• 如何让负责数据库维护的人员更方便地使用数据库?

• 使用数据库的应用场景也各不相同，可以说针对不同的情况，设计出来的数据表可能 千差万别 。

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

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

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

• 数据冗余、信息重复，存储空间浪费

• 数据更新、插入、删除的异常

• 无法正确表示信息

• 丢失有效信息

• 程序性能差

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

• 节省数据的存储空间

• 能够保证数据的完整性

• 方便进行数据库应用系统的开发

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

• 用户需求分析

• 概念结构设计

• 逻辑结构设计

• 物理结构设计

• 数据库实施阶段

• 数据库运行和维护阶段

用户需求分析阶段¶

对现实世界要处理的对象进行详细的调查，通过对原系统的了解，收集支持新系统的基础数据并对其进行处理，在此基础上确定新系统的功能。

需求分析的任务：

1. 详细调查现实世界要处理的对象（组织、部门、企业等）
2. 充分了解原系统（手工系统或计算机系统）工作状况

3. 明确用户的各种需求 4.

4. 分析用户的要求是设计数据库的起点

5. 结果是否准确地反映了用户的实际要求，将直接影响到后面各个阶段的设计，并影响到设计结果是否合理和实用 7.
6. 编写系统分析报告和需求规格说明书。

主要有两种方法：自顶向下，自底向上

需求分析的重点：

• 调查的重点是“数据”和“处理”，获得用户对数据库要求

• 信息要求：

  • 用户需要从数据库中获得信息的内容与性质
  • 由信息要求可以导出数据要求，即在数据库中需要存储哪些数据

• 处理要求

  • 用户要完成的处理功能
  • 对处理性能的要求

• 安全性与完整性要

需求分析的难点：

• 确定用户最终需求是困难的
• 用户缺少计算机知识，不能准确地表达自己的需求，他们所提出的需求往往不断地变化
• 设计人员缺少用户的专业知识，不易理解用户的真正需求，甚至误解用户的需求

需求分析的方法（步骤）：

• 调查（用户）需求，（与用户）达成共识
• 调查组织机构情况
• 调查各部门的业务活动情况
• 在熟悉业务活动的基础上，协助用户明确对新系统的各种要求 -

• 分析与表达这些需求¶

概念结构设计阶段¶

通过对用户需求进行综合、归纳与抽象，形成一个独立于具体 DBMS 的概念模型。主要工作：

• 识别实体、属性、码（主键）；
• 确定实体之间的联系（1:1、1:N、M:N）；
• 绘制 E-R 图；
• 消除冗余和冲突。

逻辑结构设计阶段¶

将概念结构转换为某个 DBMS 所支持的数据模型。主要工作：

• E-R 图 → 关系模式（表）；
• 确定主键、外键；
• 规范化处理（达到 3NF 或 BCNF）；
• 优化关系模式；
• 设计视图、完整性约束。

重点：E-R 到关系模式的转换规则、函数依赖、范式判断（1NF、2NF、3NF、BCNF）

物理结构设计阶段¶

为逻辑数据模型选取一个最适合应用环境的物理结构

数据库实施阶段¶

数据库运行和维护阶段¶

ER 图¶

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

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

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

• 在 ER 模型中，实体显示为 圆角矩形 ，其名称位于上方，其属性列在实体形状的主体中。实体代表了一种对象或者概念。例如，员工、部门和职位都可以称为实体。

• 分成两部分的矩形 代表实体集， 阴影部分 是实体集名字，第二部分包括实体集属性，主码属性带下划线。属性表示实体的某种特性，例如员工拥有姓名、性别、工资、所在部门等属性。

• 菱形 代表联系集。

• 未分割的矩形 代表联系集的属性。

• 联系集与其属性以 虚线 连接。

• 线段 将实体集与联系集连接。

详细可以参考visual-paradigm

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

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

数据流图¶

数据流图或数据流程图（Data Flow Diagram），缩写为 DFD。数据流图 DFD 是描述系统中数据流程的一种图形工具，它标志了一个系统的逻辑输入和逻辑输出，以及把逻辑输入转换逻辑输出所需的加工处理。

https://www.visual-paradigm.com/cn/guide/data-flow-diagram/what-is-data-flow-diagram/

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

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

分层细化的数据平衡原则

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

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

• 所有外部实体以及进出它们的主要数据流都显示在上下文图上。

• 该图不包含任何数据存储。
• 上下文图中的进程名称应该是信息系统的名称。例如，评分系统、订单处理系统、注册系统。

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

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

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

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

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

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

• 物理数据流图描述系统层面的数据流动。

数据流图的四大基本组件:

数据流图中的各种规则：

• 程序： 程序表示对数据的处理过程，它可以接收输入，并产生输出，每个程序都有一个名称来标识它所执行的功能，比如：申请付款，计算佣金等。用 圆角矩形 表示。

• 数据存储: 表示暂时存储的数据 , 数据存储的粒度是以表为单位。用 半框矩形 表示。

• 数据存储必须以数据流连接到程序。

• 每个数据存储必须至少有一个输入数据流和至少一个输出数据流（即使输出数据流是一个确认讯息）。

• 方向：流向文件的数据流 表示 向文件内写入内容 , 从文件流出的数据流 表示 从文件读取内容 ;

• 外部实体(实体)： 软件系统之外的人员/组织，实体用 直角矩形 表示。

• 数据流： 数据流是数据从信息系统的一个部分移动到另一个部分的路径。

• 带输入箭头的直线即为 输入数据流

• 带输入箭头的直线即为 输入数据流

• 由于每个程序都将数据从一种形态转换为另一种形态，故每个程序必须至少有一个输入数据流和一个输出数据流。

• 数据流图的一大原则是数据不能自行转换成另一形态，数据必须经由某程序的处理才可被分发至系统的某个部份。通过这条规则，我们可以非常容易地识别出错误画法。

数据流图分层原则¶

核心原则一：功能分解原则 (Functional Decomposition)¶

DFD 分层是基于功能分解的，目的是将一个复杂的系统或流程逐步分解为若干个可管理、可理解的子流程。自顶向下细化 (Top-Down Refinement):

• 零层图（上下文图）： 将整个系统视为一个单一的过程，显示系统与所有外部实体之间的数据接口。这是最高抽象层。

• 一层图 (Level 1 DFD)： 将零层图中的单一过程分解为 $N$ 个主要的子过程（通常 $N$ 不超过 7 个，以保持可读性）。

• 更低层次图： 对一层图中的复杂过程（如 $1.0$ 过程）继续分解为 1.1, 1.2, 1.3 等更细致的过程，直到达到不能再分解的基本加工为止。

目的： 实现清晰的分工和模块化，使设计者和用户能够从不同层次理解系统。

核心原则二：平衡原则 (Principle of Balancing)¶

这是 DFD 分层中最严格、最关键的原则，确保了不同层次图表之间的数据流一致性。

定义：

• 父图（Parent Diagram）中某一个过程的输入数据流和输出数据流，必须与其对应的子图（Child Diagram）中所有过程的输入数据流和输出数据流之和保持完全一致。

简单来说： 数据不能凭空产生，也不能凭空消失。一个高层过程的“入口”和“出口”，必须与展开后的所有细节的“总入口”和“总出口”完全匹配。

数据流图的平衡原则指的是上下级数据流图之间数据流的一致性。在DFD中，较高层次的DFD（父图）被细化为较低层次的DFD（子图），平衡原则确保这种分解过程中数据流的完整性和一致性。具体来说：

• 父图中的数据流必须在子图中有所对应：每一个在父图中出现的数据流，在子图中也必须出现。

• 子图的输入和输出数据流必须与父图中该过程的输入和输出数据流相匹配：这意味着子图的总输入和总输出必须与父图中对应过程的输入和输出完全一致。

平衡原则是数据流图设计的关键规则之一，它帮助保证了系统分析和设计的准确性与一致性。

UML 图¶

UML 是 Unified Model Language 的缩写，中文是**统一建模语言** ，是由一整套图表组成的标准化建模语言。是对象管理组织（Object Management Group，OMG）的一个标准。目前，统一建模语言 (UML) 的最新主要标准版本是 UML 2.5.1

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

• UML是为软件开发的各个阶段提供模型化和可视化支持的规范语言， 从需求规格描述到系统完成后的测试和维护。

• UML可以用于数据建模，业务建模，对象建模，组件建模等。

• UML提供了多种类型的模型描述图（diagram），借助这些图可以使应用程序更易理解。

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

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

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

结构图¶

结构式建模图（Structure diagrams）强调的是**系统式的建模。结构图定义了一个模型的静态架构**。它们回答了“系统由什么组成？”的问题。

它们通常被用来对那些构成模型的'要素'建模，诸如：类，对象，接口和物理组件。另外，它们也被用来对元素间关联和依赖关系进行建模。

类图¶

类图（Class Diagram）展示了面向对象系统的构造模块。

描绘了模型或部分模型的静态视图，显示它包含的属性和行为，而不是详细描述操作的功能或完善方法。类图最常用来表达多个类和接口之间的关系。

泛化（Generalizations），聚合（aggregations）和关联（associations）分别是类之间继承，复合或应用，及连接的表现。

对象图¶

描述在某一特定时刻，类的实例（对象）以及它们之间的具体链接关系。

构件图/组件图¶

描述软件系统中物理组件的组织、连接和依赖关系。

配置图/部署图¶

描述运行时处理节点（如硬件设备、服务器）以及部署在其上的构件（制品）。

组合结构图¶

描述一个类或构件的内部结构，包括它的端口、内部零件和连接器。

包图¶

描述模型中包的组织和依赖关系，用于大型系统的模块化管理。

轮廓图/剖面图¶

用于扩展UML元模型，定义特定领域（如特定技术或行业）的UML方言。

行为图¶

行为图用于描述系统的动态行为、功能以及对象间的交互。它们回答了“系统如何运行？”的问题。

交互图¶

交互图是行为图的一个子集，专门描述对象之间消息的发送和接收，侧重于控制流。

1. 顺序图 (Sequence Diagram)：描述对象之间按**时间顺序**的消息交换。
2. 通信图 / 协作图 (Communication Diagram)：描述对象之间的消息交换，侧重于对象间的**结构连接**（UML 1.x 的协作图）。
3. 定时图 (Timing Diagram)：描述对象或参与者的状态在**明确时间维度**下的变化。
4. 交互概览图 (Interaction Overview Diagram)：结合了活动图和顺序图/通信图的元素，用于描述复杂的交互流程的概览。

其他行为图¶

用例图 (Use Case Diagram)：描述系统提供给用户的功能（用例），以及与系统交互的参与者。

活动图 (Activity Diagram)：描述系统操作的步骤和流程，包括**并行**和**分支**控制流。

状态机图 / 状态图 (State Machine Diagram)：描述**单个对象**在其生命周期内可能经历的所有状态、事件以及状态转移。

范式和反范式¶

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 扩展来实现伪主键，甚至不同数据库中对于伪主键都有不同的名称（不同的表述）

虽然各家数据库产品的伪主键叫法不同，但是给伪主键指派的列名，确是出奇的一致，那就是 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：指特定属性所关联的值。

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

多列属性¶

设计规约¶

数据库设计和建模是一些很少得到应有关注的主题，尤其是在项目开始时，但是一旦事情失去控制，问题会变得非常棘手。

在设计数据库技术方案时，我们是有自己的设计理念或者原则，还是更多依据直觉去设计？是否曾经懊悔线上发生过的一次低级故障？是否思考过怎样才能避免？设计规范的价值在于提供了一份工作检查清单，我们不断从错误中积累有效经验，指导未来的工作。

是否使用预留字段？

在项目交付前，需求不断在变，如何在需求改变的同时尽可能减少对表结构的修改是我们现在考虑的问题。对于一般情况而言，在设计的时候我们可以适当添加一些预留的字段，需求改变的时候可以用上，或者说是添加不足的字段。

在数据表中，不仅设计了当前所需要的字段，而且还在其中留出几个字段作为备用。比方说，设计了一个人员表（Person），其中已经添加了各种必要的字段，包括姓名（Name）、性别（Sex）、出生年月日（birthday）等等。大功告成之后，忽然想到，将来系统中应该还会有很多其它与人相关的内容吧，比方说毕业院校，比方说工作单位等等，尽管现在根本不需要填写，以后可能还是会用到的吧。拍脑袋一项，那就加入 5 个 varchar2 型的字段，分别叫做 Text1、Text2……Text5，然后又想，应该还有一些日期型的字段需要备用，就又建立了三个 date 型的字段，分别起名叫做 date1、date2、date3。

【解决方案】

其实上面的这种设计方式就是一种“过度设计”，我们应该做的就是“按需设计”。因此要禁止在表中建立预留字段，理由如下：

1.无法准确的知道预留字段的类型，所以无法选择合适的类型。

2.无法准确的知道预留字段中所存储的内容，预留字段的命名很难做到见名识义

3.后期维护预留字段所要的成本，同增加一个字段所需要的成本是相同的。对预留字段类型的修改，会对表进行锁定。（修改一个字段的成本，大于新增字段）。

推荐解决办法是，当需要增加相关的信息的时候：

如果数量很少，而且信息的性质与原表密切相关，那么就可以直接在原表上增加字段，并将相关的数据更新进去； 如果数量较大，或者并非是原表对象至关重要的属性，那么就可以新增一个表，然后通过键值连接起来； 对于表的数据的存储位置所导致的性能问题，我们可以通过在特定时间对数据库的数据进行重组来解决，而这项工作对于长期运行的数据库来说，也是需要定期进行的。