zystrivego
0%

数据库设计基础

发表于 Valine：

数据库设计方法-新奥尔良(New Orleans)方法的四个阶段:

  • 需求分析阶段

  • 概念设计阶段

  • 逻辑设计阶段

  • 物理设计阶段

需求分析

即分析数据存储的要求，产出物有数据流图、数据字典、需求说明书。产品经理，BA该考虑的事情，编写系统需求分析报告，包括用户规格说明书和数据字典。

概念设计

概念设计的目的是根据需求分析的结果，将用户对数据的需求综合成一个统一的概念模型，它是整个数据库设计的关键。概念模型是现实世界和DBMS支持的数据模型之间的桥梁。

设计出E-R图，也即实体-属性图，与物理实现无关，说明有哪些实体，实体有哪些属性。让不懂数据库的业务人员也能看懂。E-R图对立于任何一种数据模型，它不为任何一种DBMS所支持。

联系：描述实体内部以及实体之间的联系。
通常分为三类:

  • 一对一联系(1:1):例如一个班级有一个班主任。

  • 一对多联系(1:n):例如一个班级有n个学生组成。

  • 多对多联系(m:n):例如学生选修课程。一个学生可以选修多门课程，一门课程也可以被多个学生选修。

    逻辑设计

    逻辑设计的任务就是把概念模型转换成某个具体的DBWS所支持的数据模型。

  • 按照概念设计阶段建立的基本E-R图，按选定的目标数据模型(层次、网状、关系、面向对象) ,转换成相应的逻辑模型。

  • 对于关系型数据库来说，这种转换要符合关系数据模型的原则，得到的就是逻辑数据模型。

  • 这个阶段主要的工作就是确定关系模型里面的属性和码(或者说主键)。

  • 比较常用的方式是使用E- R设计工具，IDEF1x方法来进行逻辑模型建设，常用的ER图表示法包括IDEF1x,IE模型的Crow’s foot，UML类图方式等。

    IDEF1X方法

    IDEF1X(Integration DEFinition for Information Modeling)

信息模型集成定义。

IDEF1X是IDEF系列方法中IDEF1的扩 展版本，是在E-R (实体联系)方法的原则基础上，增加了一些规则，使语义更为丰富的一种方法。

IDEF1X特点:有四个，如下所示：

  • 良好的可扩展性。

  • 简明的一致性结构。

  • 便于理解。

  • 可以自动化生成模型。

    逻辑模型中的实体

    实体就是描述业务的元数据。

主键是识别实体每一个实例唯一性的标识。

只有存在外键，实体之间才会存在关系，没有外键不能建立关系。
根据实体的特点，划分为两类:

  • 独立型实体(Independent Entity)

  1. 直角矩形表示。

  2. 不依赖于其他实体，可以独立存在。

  • 依赖型实体(Dependent Entity)

  1. 圆角矩形表示。

  2. 必须依赖于其它实体而存在。

  3. 依赖型实体中的主键必须是独立实体主键的一部分或者全部。

    范式理论

    值域

    定义一个属性取值的有效范围

在值域里面的值都是合法数据。

值域体现了规则。

第一范式

属性的原子性，重复组概念

第二范式

非键属性要完整依赖于主键（比如复合主键，属性不能只依赖于其中一个主键）

第二范式

非键属性不能传递依赖于主键

物理设计

  1. 实体非正则化处理
  2. 表和字段的物理命名
  3. 确定字段类型，长度，精度等
  4. 增加逻辑模型中不存在的物理对象，比如索引，约束等。

反范式处理

有限的资源，有限的硬件条件提出了物理模型反范式化的需求。
带来数据冗余问题。
有可能会导致数据不一致问题。

  • 增加冗余列
    比如订单和客户信息冗余在一个表中，违反第三范式，但是能减少join连表查询
  • 增加重复组(repeating groups)
    比如第一号码，第二号码，违反了第一范式，但是方便前端展示，避免了纵横转换
  • 派生列
    比如中年组，老年组，减少在sql中的函数计算

表的物理化

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

字段的物理化

  • 尽量使用短字段的数据类型。

长度较短的数据类型不仅可以减小数据文件的大小，提升IO性能;同时也可以减小相关计算时的内存消耗，提升计算性能。比如对于整型数据，如果可以用smallint就尽量不用int，如果可以用int就尽量不用bigint。

  • 使用一致的数据类型。

表关联列尽量使用相同的数据类型。如果表关联列数据类型不同，数据库必须动态地转化为相同的数据类型进行比较，这种转换会带来一定的性能消耗 。选择高效数据类型。

  • 字段的约束DEFAULT

如果能够从业务层面补全字段值，就不建议使用DEFAULT约束，避免数据加载时产生不符合预期的结果

  • 给明确不存在NULL值的字段加上NOT NULL约束。

  • 唯一约束/主键约束

  • 检查约束因为对于数据质量提出了要求，不满足约束的数据在插入数据表会导致SQL失败。

索引

  1. 在经常需要搜索查询的列;

  2. 在作为主键的列，上创建索引，强制该列的唯一性;

  3. 在经常使用连接的列上创建索引;

  4. 在经常需要根据范围进行搜索的列上创建索引;

  5. 在经常需要排序的列上创建索引;

  6. 在经常使用WHERE子句的列上创建索引。

  7. 索引建多了，会有负面影响

  • 占用更多的空间
  • 插入基表数据的效率会下降
  1. 删除无效的索引，避免空间浪费

物理模型产出物

  • 物理数据模型;

  • 物理模型命名规范;

  • 物理数据模型设计说明书;

  • 生成DDL建表语句。

使用建模软件

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