数据建模设计

数据库系统——建模与设计

一、数据建模

数据库的设计不仅需要处理规则的理解，更重要的是数据需求的理解与表达。

表达计算机世界的模型称为数据模型，而表达信息世界的模型称为概念模型。抽象是具有层次的，将现实世界的问题抽象成概念模型，就是数据建模的过程，而将概念模型抽象成数据模型的过程，就是数据库设计的过程。

1.1 实体-联系模型

实体-联系【Entity-Relationship，E-R】数据模型用于描述概念模型，其基本观点为：世界是由一组称作实体的基本对象和这些对象之间的联系组成。

实体是现实世界可区分于所有其他对象的一个对象。实体有类和个体的区分，一般称个体的实体为实例，而一类实体称为实体集。
  实体使用属性来刻画，其是实体所具有的某一方面特性；实体的型刻画了一个实体，而实体的值刻画了一个实例。属性可以具体的分为：
  -单一属性与复合属性，根据属性值的复合情况，如家庭住址由省份、市区等复合而成；
  -单值属性与多值属性，根据属性值的数量情况，如一个人可能具有多个联系方式；
  -可空属性与非空属性，根据属性值的可空情况；
  -原始属性与导出属性，根据属性值是否可由其他属性导出，如年龄可以由出生年份导出。
实体还具有一个唯一区分每一实例的属性或其集合，称为码。

联系是指多个实体之间的相互关联。实体是相对稳定的，但是联系是多样化的。
  参与发生联系的实体的数目，称为联系的度或元，例如两个实体之间的相互联系称为二元联系。较为特殊的，一元联系是指实体本身的联系，例如零件之间的装配关系。
  实体在练习中扮演的功能称为实体的角色，当同一实体的不同实例参与一个联系时，为区别实例参与联系的方式，需要显式指明其角色。例如在零件的一元联系中，虽然零件之间均是装配关系，但仍需指明不同零件的父件与子件关系。

此外，在多元联系中，还要区分联系的对应关系，这种区分描述了联系的基数，是指一个实体的实例通过一个联系能与另一实体中相关联的实例的述目。包括：
  -一对一联系，实体A的一个实例只与实体B的一个实例发生联系，且反之，实体B的一个实例也只与实体A的一个实例发生联系。例如一个经理经营一个商店，而一个商店也只有一个经理；
  -一对多联系，实体A的一个实例与实体B的多个实例发生联系，但反之，实体B的一个实例只与实体A的一个实例发生联系。例如一个画家绘制多幅作品，但一幅作品只由一个画家绘制；
  -多对多联系，实体A的多个实例与实体B的多个实例发生联系，且反之，实体B的多个实例也与实体A的多个实例发生联系。例如一个学生可以选修多门课，而一门课也可以被多个学生选修。
  进一步，联系的基数还要区分零个、一个、不定多个与固定多个，通常以实体参与联系的最小和最大基数来标记，记为( M i n C a r d . . . M a x C a r d ) (MinCard…MaxCard)(MinCard…MaxCard)，例如书架参与存放的图书的基数为( 0… m ) (0…m)(0…m)，而图书参与书架联系的基数为( 1…1 ) (1…1)(1…1)，其语义为一个书架可以存放0或多本书，而一本书只可以存放在1个书架中。并由此定义联系的参与概念：实例至少有一个参与到联系中，称为完全参与联系，其基数为( 1… m ) (1…m)(1…m)；而实例可以不参与联系，称为部分参与联系，其基数为( 0… m ) (0…m)(0…m)。其意义为当联系为部分参与联系时，关系中就会允许出现空值。

1.2 实体-联系图

E-R图可以图像化表示数据库的全局逻辑结构。较为基础的是Chen方法，包括如下几个主要构件：
  -矩形，代表实体；
  -椭圆，代表属性。其中，如果出现多值属性和导出属性，则使用双线椭圆与虚线椭圆。属性中的码使用下划线，如果出现复合码，则使用相同的数字标记；
  -直线，表示实体与属性的关联；
  -菱形，代表联系。其中，使用从联系指向实体的单箭头表示1:1联系，使用从联系指向1端的单箭头及指向多端的直线表示1:m联系，使用直线表示m:n联系，或使用实体的数字标记表明联系的基数；使用单直线表示完全参与联系，使用双直线表示部分参与联系，或使用实体的数字标记表明联系的基数。
  考虑一个图书管理模型，实体为读者、图书、书架，联系为借阅、保存，其E-R图为

此外，还有Crow’s foot方法，其区别于Chen方法，主要构建如下：
  -双矩形框，代表实体及其属性。实体名称位于上矩形框，属性位于下矩形框，属性中的键使用下划线；
  -菱形，代表联系，联系的菱形可以省略。其基数使用不同的连接线形象的表示，或使用实体的数字标记表明联系的基数。
那么上述图书管理模型的例子还可以表述成

二、数据库设计

1、数据库建模的过程：概念模型->逻辑模型->物理模型

2、概念模型：ER模型

概念模型的用途:

概念模型用于信息世界的建模
是现实世界到机器世界的一个中间层次
是数据库设计的有力工具
数据库设计人员和用户之间进行交流的语言

对概念模型的基本要求:

较强的语义表达能力
能够方便、直接地表达应用中的各种语义知识
简单、清晰、易于用户理解

2.1、ER的实体

客观存在并可相互区别的事物称为实体。可以是具体的人、事、物或抽象的概念。

2.2、ER的属性

数据对象所具有的属性、特性等，例如学生具有姓名、学号、年级等属性。

2.3、ER的关系

现实世界中事物内部以及事物之间的联系在信息世界中反映为实体内部的联系和实体之间的联系。

具体的关系：

1对1（1:1）

1对多（1:N）

多对多（M:N）

具体举例：

完整的E-R举例：

3、逻辑模型：对概念模型的进一步细化

逻辑模型主要包括网状模型、层次模型、关系模型、面向对象模型等，按计算机系统的观点对数据建模，用于DBMS实现。

数据模型由三部分组成：数据结构、数据操作和数据约束。

(1)数据结构：数据结构主要描述数据的类型、内容、性质、以及数据之间的联系，是整个数据模型的基础，而针对数据的操作和数据之间的约束都是建立在数据结构的基础上的；

(2)数据操作：主要定义了在相应的数据结构上的操作类型和操作方式（数据库中的增删改查等）；

(3)数据约束：数据约束主要用来描述数据库中数据结构之间的语法、词义联系以及彼此之间的相互约束和制约关系（如MySQL中使用外键保证数据之间的数据完整性

逻辑数据模型的内容包括所有的实体、实体的属性、实体之间的关系以及每个实体的主键、实体的外键（用于维护数据完整性）。其主要目标是尽可能详细的描述数据，但是并不涉及这些数据的具体物理实现。逻辑数据模型不仅会最终影响数据库的设计方向，并最终会影响到数据库的性能（如主键设计、外键等都会最终影响数据库的查询性能）

3.1、逻辑模型的设计方式——三范式

规范的的数据库是需要满足一些规范的来优化数据数据存储方式。

3.1.1、第一范式

当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求。通俗的讲：数据库表中的所有字段值都是不可分解的原子值。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

3.1.2、第二范式

如果关系模式R满足第一范式，并且所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。通俗的说：第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

订单信息表

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键（主键=订单编号+商品编号）相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可

3.1.3、第三范式

需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）这样一个表结构，就存在上述关系：学号–> 所在院校 --> (院校地址，院校电话)

这样的表结构，我们应该拆开来，如下。

（学号，姓名，年龄，性别，所在院校）–（所在院校，院校地址，院校电话）。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

3.2、其他注意事项

字段冗余，业务上的高频率字段可以冗余在其他表上，减少表关联查询。
业务上考虑，允许存在传递依赖，满足第二范式，不满足第三范式。目的是防止出现表膨胀，以及业务常用的数据SQL查询次数增加或关联查询。
增加一些必要的非业务字段。比如创建时间、更新时间、是否删除、版本号，以及某个业务操作的时间记录、操作人记录等。尽量做到操作可追溯。
索引的增加，索引是数据库设计必须要考虑的，增加的规则：表的主键、业务上常用的字段等。
表名、字段名、索引名要规范。
表名设计时考虑增加模块的前缀。
字段名设计时字段名可读，保持一致的规则，比如：主键都是XX_ID结尾，时间都是XX_DATE结尾或者XX_AT，操作人、更新人、审核人等都是XX_BY,XX_UID。整个数据库统一规则
索引名规范，比如：IDX_表名_字段名表示对应的索引
字段类型要统一。比如主键都是BIGINT类型，字典都是INT类型。
增加字典表。考虑业务上的字段是否是枚举类型，如果是枚举类型，尽量使用字典保存数值。好处是：1、数字类型存储更小、过滤更快 2、业务上修改描述时，改动最小
数据库层面的外键约束、触发器、存储过程等都尽量不要使用。考虑业务层代码上实现相同的功能。业务层代码上实现的更容易维护、扩展等，后期有效率问题时也更容易通过其他方式解决。

4、物理模型：物理机器的具体实现

物理模型是概念数据模型和逻辑数据模型在计算机中的具体表示。该模型描述了数据在物理存储介质上的具体组织结构，不但与具体的数据库管理系统相关，同时还与具体的操作系统以及硬件有关。

可以通过物理模型直接生成对应数据库的SQL，也在此模型上调整对应数据库特有的内容。比如Oracle的表空间等。

三、数仓建模——建模流程

1、建模流程

其实就是业务模型->概念模型->逻辑模型->物理模型的这样一个流程，下面我们详细解释一下各个模型阶段都要做什么

业务建模(需求沟通)

• 根据业务部门进行划分，理清部门之间的关系，然后将各个部门的具体业务程序化，与业务部门开会协商出需求的指标、保存年限、维度等等。
• 总体来讲，就是要知道他们需要哪些指标以及他们能提供哪些数据。
• 业务建模的时间最长，而且与公司实际的业务环境息息相关，因此在这里需要根据实际生产环境和业务需求确认好数据仓库使用的工具和平台。

主要解决业务层面的分解和程序化。搞清楚系统边界，确定好主题域

• 因此，业务建模阶段其实是一次和业务人员梳理业务的过程，在这个过程中，不仅能帮助我们技术人员更好的理解业务，另一方面，也能够发现业务流程中的一些不合理的环节，加以改善和改进。

概念建模|领域建模(画图想好怎么做)

• 将业务模型抽象化，分组合并类似的概念，细化概念，抽象出实体与实体之间的联系，理清各组概念之间的联系。
• 说白了就是画图，把指标需要的哪些数据封装到一个实体里，实体与实体之间的关联等等用ER图表示出来。
• 先画出局部ER图，最后再综合画出全局ER图。

主要是对业务模型进行抽象处理，生成领域概念模型

• 在原有数据库基础上建立了一个比较稳固完善的模型，因为数据仓库是对原有数据库系统中的数据进行集成和重组而形成的数据集合，所以数据仓库的概念模型设计，首先要对原有数据库系统加以分析理解，看在原有的数据库系统中“有什么”、“怎样组织的”和“如何分布的”等，然后再来考虑应当如何建立数据仓库系统的概念模型。
• 数据仓库的概念模型是面向企业全局建立的，它为集成来自各个面向应用的数据库的数据提供了统一的概念视图。
  概念模型的设计是在较高的抽象层次上的设计，因此建立概念模型时不用考虑具体技术条件的限制。
• 领域概念建模就是运用了实体建模法，从纷繁的业务表象背后通过实体建模法，抽象出实体，事件，说明等抽象的实体，从而找出业务表象后抽象实体间的相互的关联性，保证了我们数据仓库数据按照数据模型所能达到的一致性和关联性

逻辑建模(表设计)

• 将概念模型实体化，具体考虑概念对应的属性，事件考虑事实属性，维度考虑维度属性。
• 总体来说就是建表，前面已经画出了关系图，这里只要将表里头有哪些字段考虑出来就可以，如果是事实表就考虑事实字段和业务主键，如果是维度表就考虑维度属性，SCD策略等等。在这里需要确定数据粒度，如果多个指标都用到一个字段，则取粒度最小的指标。如果不确定指标的量度，则取毫秒级作为粒度。

物理建模(建表)

• 综合现实的大数据平台、采集工具、etl工具、数仓组件、性能要求、管理要求等多方面因素，设计出具体的项目代码，完成数仓的搭建。

2、建模的过程

• 假设我们现在在构建一张订单表

从多个维度进行统计组合，形成多维度数据集，来从多个角度观察业务过程的好坏

选择业务过程

• 确认哪些业务处理流程是数据仓库应该覆盖的，是维度方法的基础。因此，建模的第一个步骤是描述需要建模的业务流程。例如，需要了解和分析一个零售店的销售情况，那么与该零售店销售相关的所有业务流程都是需要关注的。为了描述业务流程，可以简单地使用纯文本将相关内容记录下来，或者使用“业务流程建模标注”（BPMN）方法，也可以使用统一建模语言（UML）或其他类似的方法。
• 业务过程就是需要那种业务场景下产生的订单表(划分到那个业务线和数据域)
• 业务过程就是用户下单的订单记录表

选择数据域

申明粒度

• 粒度就是确认一条记录代表的含义或者是细化到何种程度(一条记录代表一个订单还是多个订单，如拼团的时候团长的单)
• 在选择维度和事实前必须声明粒度，因为每个候选维度或事实必须与定义的粒度保持一致。在一个事实所对应的所有维度设计中强制实行粒度一致性是保证数据仓库应用性能和易用性的关键。
• 从给定的业务流程获取数据时，原始粒度是最低级别的粒度。建议从原始粒度数据开始设计，因为原始记录能够满足无法预期的用户查询。汇总后的数据粒度对优化查询性能很重要，但这样的粒度往往不能满足对细节数据的查询需求。
• 不同的事实可以有不同的粒度，但同一事实中不要混用多种不同的粒度。维度模型建立完成之后，还有可能因为获取了新的信息，而回到这步修改粒度级别。

确认维度

• 维度的粒度必须和第二步所声明的粒度一致。
• 维度表是事实表的基础，也说明了事实表的数据是从哪里采集来的。
• 典型的维度都是名词，如日期、商店、库存等。维度表存储了某一维度的所有相关数据，例如，日期维度应该包括年、季度、月、周、日等数据。

确认事实

• 这一步识别数字化的度量，构成事实表的记录。它是和系统的业务用户密切相关的，因为用户正是通过对事实表的访问获取数据仓库存储的数据。大部分事实表的度量都是数字类型的，可累加，可计算，如成本、数量、金额等。

3、模型设计的思路

• 业务需求驱动，数据驱动，构造数据仓库有两种方式：一是自上而下，一是自下而上。

自上而下

• Bill Inmon先生推崇“自上而下”的方式，即一个企业建立唯一的数据中心，就像一个数据的仓库，其中数据是经过整合、经过清洗、去掉脏数据的、标准的，能够提供统一的视图。要建立这样的数据仓库，并不从它需要支持哪些应用入手，而是要从整个企业的环境入手，分析其中的概念，应该有什么样的数据，达成概念完成整；

自下而上

• Ralph Kimball先生推崇“自下而上”的方式，他认为建设数据仓库应该按照实际的应用需求，加载需要的数据，不需要的数据不要加载到数据仓库中。这种方式建设周期较短，客户能够很快看到结果。（针对客户的需求，需求要什么就做什么）

4、模型落地实现

• 按照命名规范创建表
• 开发生成维表和事实表的代码
• 进行代码逻辑测试，验证数据加工逻辑的正确性
• 代码发布，加入调度并配置相应的质量监控和报警机制