【数据库】数据库设计二之范式与反范式 and 数据库设计实例

最新推荐文章于 2022-04-01 15:30:00 发布

废物点心777

最新推荐文章于 2022-04-01 15:30:00 发布

阅读量965

点赞数

分类专栏：数据库 GaussDB 文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/weixin_45783164/article/details/105082293

版权

数据库同时被 2 个专栏收录

10 篇文章

订阅专栏

GaussDB

3 篇文章

订阅专栏

前言：

上一章我们看了数据库设计的一整套方法，今天呀，我们来介绍一下范式和反范式以及通过一个实例来看一下数据库的设计，其实范式也是属于逻辑设计

一、范式理论

1.、什么是范式

满足不同程度的要求为满足不同的范式
把属性放置在正确的实体的这个过程称为范式化（normalization）

发展历史

-1971~1972：Codd系统地提出了1NF、2NF和3NF的概念，讨论了规范化问题
1974： Codd和Boyce共同提出了新范式，BCNF
1976： Fagin提出了4NF
之后的研究人员进一步提出5NF

遵循规范化理论设计出的关系数据模型

能够避免冗余数据的产生；
降低数据不一致的风险;
模型具有良好的可扩展性；
可以灵活调整以反映出不断变化的业务规则。

2、范式之间的关系

范式之间的关系：

满足最低要求的叫第一范式，记为1NF。
在第一范式满足进一步要求的为第二范式，2NF。
以此类推。
一个低一级范式的关系模式通过模式分解（Schema Decomposition）可以转换为若干个高一级范式的关系模式的集合。---->比如第二范式通过模式分解，生成若干个第三范式关系模式，这种过程就叫做范式化。

3、值域

定义一个属性取值的有效范围
在值域里面的值都是合法数据。
值域体现了规则。

4、第一范式（1NF）

属性取值的原子性（不可再分）。
对原子性的理解很容易出现分歧，比如身份证里面就包含了出生日期，性别的属性，那是否需要拆分呢？按照理论上来说它是可以拆分的，不具备原子性。但从值域的角度来讲，在身份证这个值域里面它具备了原子性就不用继续拆分了。
属性取值数量是单一的，不能是值域里面的子集。
需要有主键。
实体中的属性不存在重复组问题。

问题：
1.数值格式不统一不说了，还包含非数值的字符
2.更大的问题是，有两个人的电话号码存放的电话数量超过一个。也就是违反了“属性取值数量是单一的，不能是值域里面的子集”这种情况。两个号码是电话号码值域里面的子集合。

在这里插入图片描述

上面的表示比较常见的拆分手段，实际上还是不符合第一范式。因为出现了repeating group，重复组问题

Repeating group，技术上讲取值是原子性的，但在概念上把相同的属性进行了重复

Repeating group所产生的问题

有些记录会产生空值。（实际应用场合，这种大宽表会带来数据的稀疏性，例如数据挖掘的宽表使用）
结构会产生不稳定性，比如有些人有3个电话号码，甚至更多。所以可能要经常更新表结构，导致模型的不稳定性
也就是实际应用中常说的会因为业务的发展而对模型带来不稳定性冲击
这个也会在使用数据的时候产生歧义，那个手机号码应该放在第一列，那个手机号码放在第二位，规则是什么，要获取客户的联系方式的时候以那个电话为准？
导致业务使用数据的时候会有语义上的混乱和不明确。

那么应该怎么办呢？
这个就ok!!!
在这里插入图片描述

5、第二范式(2NF)

第二范式的两个必要条件：

首先要满足第一范式。
每一个非主属性都完全函数依赖于任何一个候选键。
第2范式强调的是完全函数依赖。
简单的理解2NF就是所有非主键字段都要依赖于整个主键，而不是其中的一部分
简单的来说，如果一个实体的主键字段只有一个，那么基本上这个实体就是符合第二范式的
对于不满足2NF比如订单日期。实际上它依赖于部分主键，订单编号，所以在这个表里面，会随着订 - 单编号的重复而出现大量的重复。数据冗余了。

那么怎么修改呢？
修改方法：
在这里插入图片描述

6、第三范式（3NF）

首先要满足第二范式。
每一个非主属性不会传递性依赖于键码。

在这里插入图片描述

简单的理解3NF就是所有非主键字段都要依赖于整个主键，而不会依赖于非主键的其他属性

我们就拆表！
拆表，利用外键形成关系
在这里插入图片描述

7、其他范式

理解：
1NF：要有主键。2NF：依赖于整个主键。3NF：只能依赖于主键

因为在实际应用中，模型做到满足第三范式就已经足够了，现在数据库设计最多满足3NF，普遍认为范式过高，虽然具有对数据关系更好的约束性，但也导致数据关系表增加而令数据库IO更易繁忙，
所以在现实项目中，基本没有实现到3NF以上级别的案例。所以在培训过程中，不用介绍3NF之后的范式标准了，有兴趣的学员，可以课后自行查看相关资料学习
BCNF，4NF，5NF就是主键加强。
BCNF：符合3NF，并且，主属性不依赖于主属性。
4NF：要求把同一表内的多对多关系删除。
5NF：从最终结构重新建立原始结构

8、雪花模型

在这里插入图片描述

雪花模型是符合三范式要求的。所以作为一个扩展知识的点简单介绍。

9、注意事项

建立命名规则：
命名规则的意义：
• 统一命名，避免歧义。
• 防止冗余的实体或者属性产生。
• 有利于工作中不同角色的人员之间通过规范的命名和属于进行交流。
• 便于使用。

实体和属性的命名建议：
• 实体名称：分类域大写+实体描述词（全称，首字母大写）。
• 属性名称：使用全称，首字母大写，一些约定俗称的空格缩写。
• 避免英语和拼音的混用。
• 如果是缩写，一定是英语的缩写，避免使用拼音的声母缩写。

命名规则最主要的思想还是要统一，命名统一才能有利于大家交流和规范开发

按照设计流程设计逻辑数据模型
确定实体和属性：
定义实体的主键（PK）。
定义部分非键属性（Non-Key Attribute）。
定义非唯一属性组。
添加相应的注释内容。
确定实体与实体之间的关系
通过外键来体现。
决定实体之间是否是可识别的关系。
确定关系的基数属于1:1, 1:n还是n:m。
补充实体的非键值属性
按照3NF的规则，判定添加的属性是否符合3NF的设计原则。
如果新增属性违反3NF，需要进行实体拆分，确定新的实体和关系。
添加注释。

二、物理设计

1、物理设计

在用户确认的逻辑模型基础上，以数据库系统运行效率，业务操作效率，前端应用效率等因素为出发点对模型进行的调整。
面向物理实施过程的具体细节。
最终目的是转化为目标数据库的可部署的定义语言（DDL）。
工作内容，包括但不限于

实体非正则化处理；
表和字段的物理命名；
确定字段的类型，长度，精度，大小写敏感等属性；
增加逻辑模型中不存在的物理对象：索引，约束，分区等。

2、相信的事务不同的名称

操作型文件系统	关系型理论	逻辑模型	物理模型
文件（File）	关系（Relation）	实体（Entity）	表（Table）
行记录（Record）	元组（Tuple）	实例（Instance）	行（Row）
列记录（Field）	属性（Attribute）	属性（Attribute）	字段（Column）

3、逻辑模型和物理模型对比（重点！！）

LDMvs PDM

	逻辑模型	物理模型
包含内容	实体、属性	表，字段
键值	主键	索引，唯一性约束
名称定义	业务名称	物理命名（受到数据库产品限制）
正则化	符合3NF	依据性能进行非正则化处理
冗余数据	无冗余数据	含冗余数据
派生数据	无派生数据	含派生数据
面向用户	业务人员和建模人员	DBA和应用开发人员

键值：物理模型一般不使用PRIMERY KEY，更多的使用UNIQUE+NOT
NULL约束来实现。因为用主键约束对数据质量过高，所以在物理实现上，一般会降低约束性要求，主键更多的反映在逻辑概念上。

4、物理模型反范式处理

从性能和应用需求出发

物理模型是以性能为出发点，支持应用需求，兼顾数据库物理限制。
CPU无限快，内存无限多，存储无限大，带宽无限宽，还有必要反范式处理么？

有限的资源，有限的硬件条件提出了物理模型反范式化的需求。

反范式化：反范式处理也叫非正则化处理。就是和范式化过程相反的过程和技术手段。把模型从第三范式降级到第二范式，或者第一范式的过程。

反范式处理需要适度进行

对于特定配置的硬件系统，在满足应用功能目标和性能指标的前提下，适度进行。
带来数据冗余问题。
有可能会导致数据不一致问题。

反范式例子1：
增加冗余列，避免频繁发生表关联操作（Join）
在这里插入图片描述

反范式例子2：
增加冗余列，利用repeating group来减少SQL的复杂度。
在前端报表应用中为了便于报表展现而采用的纵横转换。
在这里插入图片描述

反范式例子3：
增加派生列，减少函数计算
在这里插入图片描述

5、反范式常见手段

常见反范式化处理方式

增加重复组(repeating groups)------>例子2
预关联(pre-joins)----->例子1
派生字段------>例子3
建立汇总表或临时表
表拆分（水平拆分或者垂直拆分）

影响

并非对所有处理过程都能带来性能提升，有些负面影响需要综合考虑进行平衡。
反范式会降低数据模型的灵活性。
带来数据不一致的风险。

6、维护数据完整性

反范式处理后增加了数据冗余性，需要一定的管理措施来维护数据完整性。
批处理维护

批处理维护是指对复制列或派生列的修改积累一定的时间后，运行一批处理作业或存储过程对复制或派生列进行修改，这只能在对实时性要求不高的情况下使用。
批处理的方法是集中时间点运行批量处理作业，所以时效性不高，数据库内数据在一定时间范围内，存在有可能存在数据不一致情况；

应用逻辑

在应用实现过程中，在同一事务中对所有涉及的表进行增、删、改操作。
风险较大，容易遗漏，特别是在需求变化时，不易于维护。

应用逻辑如果出现bug，很容易造成冗余数据不一致情况，比如update A表数据之后忘记update B表里面冗余的数据了。

触发器

实时处理性高。
但对于数据库压力较大，尤其是高并发环境，触发器数量需要严格控制。

触发器实时性处理效果好，应用更新A表数据后，数据库自动触发去更新B表数据。但是触发器的代价就是会造成数据库压力

7、对象命名规范示例

对象	前缀	范例	说明
表	t_	t_tablename	t_表名
普通视图	v_	v_viewname	v_视图名
索引	ix_	ix_tablename_columnname	这是最常用的索引，用ix_表示。如果表名或字段名过长，则用表名和字段名的缩写表示，尽量使用通用缩写或去元音的缩写方式。
触发器	trg_	trg_triggername	trg_触发器名
存储过程	p_	p_procedurename	p_存储过程名
函数	f_	f_functionname	f_函数名