高斯数据库HCNA之数据库设计基础

一、数据库设计概述

数据库设计是根据数据库系统的特点，针对具体的应用对象构建适合的数据库模式，建立数据库及相应的应用，
使整个系统能有效的采集，存储，处理和管理，从而满足企业中各种用户的使用需求

本章主要介绍了数据库设计的相关概念，整体目标和需要解决的问题，并按照新奥尔良设计方法对需求分析、概念设计、逻辑设计和物理设计几个阶段
的具体工作进行了详细说明，最后结合相关案例对数据库设计的具体实现手段进行了介绍

1、数据库设计

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

2、数据库设计的困难

数据库设计面临的主要困难
熟悉数据库的人员缺乏业务知识和行业知识
熟悉业务知识，了解业务流程的人往往缺乏对数据库产品的了解，对数据库设计流程也不熟悉
在初始阶段没办法明确应用业务的数据库系统的业务需求范围
用户需求在设计过程中不断调整和修改，甚至在数据库模型落地后，开发过程中还会有新需求出现，会对已有的数据库结构造成冲击

3、数据库设计的目标

设计目标
  为用户和各种应用系统提供一个信息基础设施和高效的运行环境
高效的运行环境
  数据库数据的存取效率
  数据库存储空间的利用率
  数据库系统运行管理的效率

4、数据库设计方法-新奥尔良方法

新奥尔良(New Orleans)方法的四个阶段
  需求分析
  概念设计阶段
  逻辑设计阶段
  物理设计阶段

二、需求分析

1、需求分析的意义

需求分析阶段主要是收集信息并进行分析和整理，为后续阶段提供充足信息
是整个数据库设计的基础
最困难，也可能最耗时的阶段
需求分析没做好，会导致整个数据库设计重新返工

需求分析阶段需要
了解现有系统的运行概况
确定新系统的功能要求
收集能够实现目标的基础数据及相关的业务流程

2、数据字典

数据字典是对数据的描述，不是数据本身，包括：
数据项
  数据项名称，含义，数据类型，长度，取值范围，单位，与其他数据项逻辑关系等
  是逻辑设计阶段模型优化的依据
数据结构
  数据结构反映了数据项之间的组合关系，一个数据结构可以由若干数据项和数据结构混合组成
数据流
  在系统内的传输路径，包括数据来源，流向，平均流量，高峰期流量等
数据存储
  数据存取拼读，保留时间长度，数据存取方式
处理过程
  数据处理过程的功能及处理要求，功能是指处理过程用来做什么，要求包括单位时间内处理多少事务，多少数据量，时间相应要求等

三、概念设计

1、概念设计和概念模型

概念设计的任务
分析用户提出的需求，对用户需求进行综合、归纳和抽象，形成一个独立于具体数据库管理系统的概念层次抽象模型，即为概念数据模型

概念模型的主要特点
1、能真实、充分的反映现实世界，包括事物和事物之间的联系，是现实世界的真实模型
2、易于理解，可以和不熟悉数据库的用户进行讨论
3、易于更改，当应用环境和应用要求改变时可以对概念模型进行修改和扩充
4、易于向关系数据模型转换

2、E-R方法

1976年P.P.S.Chen提出了实体-联系(Entitiy-Relationship)方法，即E-R方法
  简单、实用
  是构建概念模型常用的方法之一
E-R方法使用的工具称为E-R图
  E-R图在概念设计阶段使用的比较广泛
  用E-R模型表示的数据库概念非常直观，易于用户理解
  E-R图主要由实体、属性和联系三个要素构成

3、E-R方法实体

4、E-R方法属性

5、E-R方法联系

4、逻辑设计

1、逻辑设计和逻辑模型

这个阶段是将概念模型转化为具体的数据模型的过程
按照概念设计阶段建立的基本E-R图，按选定的目标数据模型(层次、网状、关系、面向对象)，转换成相应的逻辑模型
对于关系型数据库来说，这种转换要符合关系数据模型的原则，得到的就是逻辑数据模型
这个阶段的主要工作就是确定关系模型里面的属性和码(或者说主键)
比较常用的方式是使用E-R设计工具，IDEF1x方法来进行逻辑模型设计，常用的ER图表示法包括IDEF1x，IE模型的Crow's foot，UML类图方式等

2、IDEF1x方法

IDEF1X(Integration DEFinition for Information Modeling)
  信息模型集成定义
  IDEF1X是IDEF系列方法中IDEF1的扩展版本，是在E-R(实体联系)方法的原则基础上，增加了一些规则，使语义更为丰富的一种方法
IDEF1X特点
  良好的可扩展性
  简明的一致性结构
  便于理解
  可以自动化生成模型

3、逻辑模型中的实体

4、实体中的属性

5、主键,外键和索引之间的关系

6、实体间的关系

7、IDEF1x中基数的图例

8、基数的不同反映了不同的关系

9、识别性关系

10、非识别性关系

11、嵌套关系

12、子类关系

13、逻辑模型基本概念小结

实体就是描述业务的元数据
主键是识别实体每一个实例唯一性的标识
只有存在主键，实体之间才会存在关系，没有外键不能建立关系
关系的基数反映了关系之间的业务规则
  一个客户只能拥有一个一类储蓄账户
  一个客户可以拥有多个储蓄账户
  一个订单只能对应一个发货运单
  一个产品包括多个零件

14、范式理论

范式理论(Normal Form)
在数据库设计的时候要满足的设计规范
  满足不同程度的要求为满足不同的范式
  把属性放置在正确的实体的这个过程称为范式化(normalization)
发展史
  1971~1972：Codd系统的提出了1NF、2NF和3NF的概念，讨论了规范化问题
  1974：Codd和Boyce共同提出了新范式，BCNF
  1976：Fagin提出了4NF
  之后的研究人员进一步提出5NF

15、范式理论的意义

设计逻辑模型时候最常遇到的问题
  哪些属性应当放到实体中，怎么放
范式化的意义
  减少数据冗余
  提供良好的可扩展性
  消除数据更新时候可能产生的数据不一致情况

16、范式之间的关系

17、值域

18、第一范式

19、原子性的程度

原子性概念在实际应用中容易出现歧义
  不可再分的程度如何确定边界
  具有编码规则的代码实际上都是复合型代码，从规则上讲都是可分的
  比如身份证号码、收集号码，都是可以进一步拆分出更细粒度的数据
从值域的角度来理解
  身份证号码的值域：只要符合身份证编码规则的就是合法的身份证，就是原子性的数据

20、第二范式

21、第三范式

22、一句话精炼

1、要有主键
2、依赖于整个主键
3、只能依赖于主键

23、雪花型模型

24、逻辑模型建设注意事项

建立命名规则
命名规则的意义
  统一命名，避免歧义
  防止冗余的实体或者属性产生
  有利于工作中不同角色的人员之间通过规范的命名和属于进行交流
  便于使用
实体和属性的命名建议
  实体名称：分类域大写+实体描述符(全称，首字母大写)
  属性名称：使用全称，首字母大写，一些约定俗称的空格缩写
  避免英语和拼音的混用
  如果是缩写，一定是英语的缩写，避免使用拼音的声母缩写
  
逻辑模型建设注意事项2
按照设计流程设计逻辑数据模型
确定实体和属性
  定义实体的主键(PK)
  定义部分非键属性(Non-Key Attribute)
  定义非唯一属性组
  添加相应的注释内容
  
逻辑模型建设注意事项3
确定实体与实体之间的关系
  通过外键来体现
  决定实体之间是否是可识别的关系
  确定关系的基数属于1:1,1:n还是n:m

补充实体的非键值属性
  按照3NF的规则，判定添加的属性是否符合3NF的设计原则
  如果新增属性违反3NF，需要进行实体拆分，确定新的实体和关系
  添加注释

5、物理设计

1、物理设计和物理模型

物理设计
在用户确认的逻辑模型基础上，以数据库系统运行效率，业务操作效率，前端应用效率等因素为出发点对模型进行的调整
面向物理实施过程的具体细节
最终目的是转化为目标数据库的可部署的定义语言(DDL)
工作内容，包括但不限于
  实体非正则化处理
  表和字段的物理命名
  确定字段的类型，长度，精度，大小写敏感等属性
  增加逻辑模型中不存在的物理对象：索引，约束，分区等

2、相同事物不同名称

3、逻辑模型和物理模型对比

4、物理模型反范式处理

从性能和应用需求出发
  物理模型是以性能为出发点，支持应用需求，兼顾数据库物理限制
  CPU无限快，内存无限多，存储无限大，带宽无限宽，还有必要反范式处理么
有限的资源，有限的硬件条件提出了物理模型反范式化的需求
反范式处理需要适度进行
  对于特定配置的硬件系统，在满足应用功能目标和性能指标的前提下，适度进行
  带来数据冗余问题
  有可能会导致数据不一致问题

5、反范式示例

6、反范式常见手段

常见反范式化处理方式
  增加重复组(repeating groups)
  预关联(pre-joins)
  派生字段
  建立汇总表或临时表
  表拆分(水平拆分或者垂直拆分)
影响
  并非对所有处理过程都能带来性能提升，有些负面影响需要综合考虑进行平衡
  反范式会降低数据模型的灵活性
  带来数据不一致的风险

7、维护数据完整性

反范式处理后增加了数据冗余性，需要一定的管理措施来维护数据完整性
批处理维护
  批处理维护是指对复制列或派生列的修改积累一定的时间后,运行一批处理作业或存储过程对复制或派生列进行修改，这只能在对实时性要求不高的情况下使用
应用逻辑
  在应用实现过程中，在同一事务中对所有涉及的表进行增、删、改操作
  风险较大，容易遗漏，特别是在需求变化时，不易于维护
触发器
  实时处理性高
  但对于数据库压力较大，尤其是高并发环境，触发器数量需要严格控制

8、建立物理化命名规范

建立命名规范，对实体进行物理化命名
根据数据库物理特性进行命名
名称有效字符的范围(避免使用非法字符出现在名称中)
避免使用物理数据库的保留关键字
命名尽量采用富有意义、易于记忆、描述性强、简短及具有唯一性的英文词汇，不准采用汉语拼音
制定项目组范围内统一的命名规则，并严格遵守
名称缩写要达到约定

9、对象命名规范示例

10、表的物理化

进行反范式化操作
决定是否要分区
  对于大表进行分区，减少IO扫描量，增速范围查询
决定是否要拆分历史表和当前表
  历史表时冷数据，可以放在低俗存储上，当前表时热数据，使用高速存储
  历史表可以使用压缩方法减少占用的存储空间

11、字段的物理化

字段的物理化1
选择合适的类型，考虑以下因素
尽量使用短字段的数据类型
  长度较短的数据类型不仅可以减小数据文件的大小，提升IO性能，同时也可以减小相关计算时的内存消耗，提升计算性能
  比如对于整数类型， 如果可以用smallint就尽量不用int，如果可以用int就尽量不用bigint
使用一致的数据类型
  表关联列尽量使用相同的数据类型。如果表关联列数据类型不同，数据库必须动态的转化为相同的数据类型进行比较，这种转换会带来一定的性能消耗
选择高效数据类型

字段的物理化2
字段的约束
DEFAULT
  如果能够从业务层面补全字段值，就不建议使用DEFAULT约束，避免数据加载时产生不符合预期的结果
NOT NULL
  给明确不存咋NULL值的字段加上NOT NULL约束
唯一约束/主键约束
  主键 = 唯一 + NOT NULL
  如果条件允许，就增减
检查约束
  检查约束因为对于数据质量提出了要求，不满足约束的数据再插入数据表会导致SQL失败

12、索引的创建和使用

可以增加索引的情况
  在经常需要搜索查询的列
  在作为主键的列上创建索引，强制该列的唯一性
  在经常使用连接的列上创建索引
  在经常需要根据范围进行搜索的列上创建索引
  在经常需要排序的列上创建索引
  在经常使用WHERE子句的列上创建索引
索引建多了，会有负面影响
  占用更多地空间
  插入基表数据的效率会下降
删除无效的索引，避免空间浪费

13、其他物理化手段

根据其他特定需求
  是否采用压缩
  是否需要对数据进行加密
  是否需要对数据进行脱敏

14、使用建模软件

使用建模软件来进行逻辑建模和物理建模
  功能强大而丰富
  正向生成DDL，反向解析
  在逻辑模型和物理模型中自由切换使用视图
  全面满足建模中的各种需求，高效进行建模
相关软件
  CA ERWin
  SAP PowerDesigner
  ER/Studio
  pgModeler
  Dbeaver Community

15、物理模型产出物

物理数据模型
物理模型命名规范
物理数据模型设计说明书
生成DDl建表语句