欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

关系数据库设计:谈谈规范化技术

程序员文章站 2022-05-09 09:02:35
...

关系数据库设计:谈谈规范化技术


大家好,我是只谈技术不剪发的 Tony 老师。今天我们来聊聊关系数据库的规范化设计问题。本文不涉及数据库教材上晦涩难懂的各种公式,而是从实际应用出发,通过简单直白的方式介绍规范化的设计过程和常见范式。

为什么需要规范化?

很多教材和文章都是直接从第一范式开始介绍如何进行数据库设计,完全忽略了对事物前因后果的分析;从而导致我们看完之后,只知道要关系数据库要进行规范设计,但却不知道为什么要这么做。因此,我们首先来给大家介绍一下规范化之前发生了什么。

假设我们需要为某公司设计一个数据库,用于管理员工、部门、职位等相关的信息。首先从直观上考虑,可以将员工信息、所在部门以及职位信息存储到一个表中,如下图所示:

关系数据库设计:谈谈规范化技术
每一行数据对应一个员工的信息,包括他/她所在的部门、职位等。如果真的这么设计,我们在实际应用中很快就会发现以下各种问题:

  • 数据冗余,同一个部门的信息存储了多份,这就需要占用更多的磁盘空间。另外,数据冗余有时候也可能是指在不同的表中存储了重复的信息;
  • 插入异常,假如现在需要成立一个新的部门,由于还没有增加新的员工,因此无法录入这个部门的信息;
  • 更新异常,如果需要修改某个部门信息,需要更新多行数据,效率低下;不小心忽略了某些记录的话,还会会导致数据不一致,尤其是当一个信息存储到多个表中时更容易出现这种情况。
  • 删除异常,如果某个部门的所有员工都被删除,将会导致这个部门的信息也将不复存在;

关系数据库之父 Edgar F. Codd 显然意识到了这些问题,并且为此引入了规范化(Normalization)的设计过程。规范化使用范式(normal form)来定义和衡量,范式就是数据库设计时遵循的一种标准级别。Codd 最早提出了第一范式(1NF)、第二范式(2NF)以及第三范式(3NF),每个范式都基于前面的范式定义,例如第二范式需要先满足第一范式。

????更高级别的范式包括 BC 范式(BCNF)、第四范式(4NF)、基本元组范式(ETNF)、第五范式(5NF)、DK 范式(DKNF)以及第六范式(6NF);一般来说,满足第三范式的数据库就可以避免数据冗余和操作异常问题。

通过以上介绍,我们知道了规范化是数据库设计过程中的一系列原理和技术,使用范式来定义和衡量,主要用于减少表中数据的冗余,消除异常,提高数据完整性和一致性

下面我们基上面的非规范化数据库结构,逐步介绍第一范式到第三范式的实现过程。

第一范式

第一范式(First Normal Form)要求满足以下条件:

  • 表中的字段都是不可再分的单一属性;
  • 表需要定义主键(PRIMARY KEY)。

简单来说,首先就是每个属性要有单独的字段。在上面的不规范设计中,员工的个人电话和工作电话存储在一个字段中,破坏了原子性。另外,还需要为表定义一个主键,用于唯一识别表中的每一行数据;假设每个部门中的员工不会同名,可以使用部门名称加员工姓名作为主键。

将上面的示例修改成以下结构就可以满足第一范式:

关系数据库设计:谈谈规范化技术
第一范式要求表中的字段具有不可分割的原子特性;不过我们知道,原子是化学反应不可再分的基本微粒,但在物理状态中可以分割,它是由原子核和绕核运动的电子组成。因此,我们同样需要考虑字段不可分割到底是针对什么而言。

例如,上面的“姓名”字段,实际上也可以拆分成两个字段:姓氏和名字。那么到达要不要拆分呢?显然这个取决于应用程序如何使用这些信息,一般我们将姓名作为一个字段存储;有些应用可能需要拆分,这样在给客户发送消息时可以方便地显示为“尊敬的刘先生/女生”。

另一个类似的情况是地址信息,例如“XX省XX市XX区XX小区”,存储到一个字段还是拆分成多个字段?大部分情况下,应用程序可能需要统计不同地区的用户情况,拆分成多个字段便于分析。不过这时候需要注意的是如何确保数据的标准化,因为不同的用户虽然住在相同的小区,但会输入不一致的数据;所以最好提供一组标准的数据,提供下拉列表给用于进行选择。

除了基本的数字、字符、日期等数据类型之外,SQL 还提供了一些复杂的类型,例如数组、XML、JSON 以及自定义类型等。假如我们使用一个 JSON 字段存储电话号码,数据如下所示:

{
  "phoneNumbers": [
    {
      "type": "office",
      "number": "61238888"
    },
    {
      "type": "mobile",
      "number": "13612345678"
    }
  ]
}

那么这种设计算不算违反第一范式?从定义来说这显然不属于第一范式,因为这个字段中包含了多个可以分割的属性。

但是,从 SQL 标准来说这些类型都属于原生类型,而且提供了对这种数据进行处理和查询的内置函数和方法;如果从应用程序的角度来看,例如电商平台中的产品信息、博客文章中的评论信息,可以将它们看作一个原子数据存储在 XML 或者 JSON 字段中,因为没有进行分割处理的需求。

????SQL 是关系数据库的标准语言,但 SQL 远远不只能够存储和处理关系模型,XML 或者 JSON 文档、多维数组、图形存储以及流数据处理已经成为了 SQL 标准中的一部分,具体可以参考这篇文章

以上表结构满足第一范式,但仍然存在数据冗余(例如部门信息),可能导致插入异常、删除异常、修改异常等问题;所以我们还需要进一步规范化。

第二范式

第二范式(Second Normal Form)要求满足以下条件:

  • 满足第一范式;
  • 非主键字段必须完全依赖于主键或者候选键,不能只依赖于主键或者候选键的一部分。

上面表结构中的“部门地址”取决于“部门名称”,也就是主键的一部分;这种依赖关系称为部分函数依赖(partial functional dependency)。显然,此时表中的部门信息存在冗余,可能导致各种操作异常。

为此我们可以将部门信息单独存储到一张部门表中,并且在部门表和员工表之间维护一个一对多的关系。我们继续将表的结构修改如下:

关系数据库设计:谈谈规范化技术
我们将员工表拆成了 3 个表,员工表中的部门编号和职位编号是外键,分别引用了部门表的主键和职位表的主键。另外,我们为每个表增加了一个 id 主键字段(工号、部门编号、职位编号)。因为部门名称、职位名称等信息并不适合作为主键;如果使用部门名称作为主键,当需要修改某个部门的名称,员工表中可能需要相应修改多条记录。

如果考虑到同一个部门中可能存在同名的员工,直接在员工表中增加一个 id 主键字段也可以满足第二范式的要求。

关系数据库设计:谈谈规范化技术
以上表结构满足第二范式,但仍然存在数据冗余(例如部门信息),可能导致插入异常、删除异常、修改异常等问题;所以我们还需要进一步规范化。

第三范式

第三范式要求满足以下条件:

  • 满足第二范式;
  • 属性不依赖于其它的非主属性,也就是非关键字段不依赖于其他非关键字段。

当主键决定字段 A,字段 A 又决定字段 B 时,称为传递函数依赖(transitive functional dependency)。例如员工编号决定了部门编号,部门编号决定了部门名称;如果将部门信息和员工信息放在一张表中,就存在这种依赖。显然,在上一节中将员工表拆分成三个表之后就不存在这种问题,因此满足第三范式。

最终,我们设计的公司数据库结构(ER 图)如下:

关系数据库设计:谈谈规范化技术
其中,部门和员工的关系是一对多的关系;职位和员工的关系也是一对多的关系。

现在我们来回顾一下非规范化设计时的几个问题:

  • 部门、员工以及职位信息分别存储一份,通过外键保持它们之间的联系。因此,不存在数据冗余的问题;
  • 如果想要成立一个新的部门,直接录入部门信息即可,解决了插入异常的问题;
  • 如果某个部门的所有员工都被删除,该部门的信息不会受到影响,不存在删除异常;
  • 如果需要修改部门信息,直接更新部门表即可,不会导致数据不一致。

对于前三个范式而言,只需要将不同的实体/对象单独存储到一张表中,并且通过外键建立它们之间的联系即可满足。这也是大多数在线交易系统数据库理想的设计方法。

反规范化

简单来说,规范化就是将大表拆分成多个小表,并且通过外键建立它们之间的联系。但是,规范化可能导致连接查询(JOIN)过多。例如,为了查看员工所在的部门名称和职位名称,我们需要关联查询 3 个表:

SELECT e.emp_name, e.hire_date, d.dept_name, j.job_title
FROM employee e 
JOIN department d ON (d.dept_id = e.dept_id)
JOIN job j ON (j.job_id = e.job_id)
WHERE e.emp_name = '孙尚香';

emp_name|hire_date |dept_name|job_title|
--------|----------|---------|---------|
孙尚香   |2002-08-08|财务部    |财务经理  |

如果表中的数据量很大,过多的表连接查询会增加数据库的 IO 操作,从而降低数据库的性能。因此,有时候为了提高某些查询或者应用的性能而故意降低规范反的程度,也就是反规范化(denormalization)。一般来说,数据仓库(Data Warehouse)和在线分析系统(OLAP)会使用到反规范化的技术,因为它们以复杂查询和报表分析为主。

常用的反规范化方法包括增加冗余字段、增加计算列、将小表合成大表等。例如想要知道每个部门的员工数量的话,需要同时连接部门表和员工表;可以在部门表中增加一个字段(emp_numbers),查询时就不需要再连接员工表,但是每次增加或者删除员工时需要更新该字段。

需要注意的是,反规范化会增加更新和修改数据的开销,导致数据存在冗余,可能带来数据完整性和一致性的问题;因此,通常我们应该先进行规范化设计,再根据实际情况考虑是否需要反规范化

关于外键

在数据库结构设计时,还有一个经常争论的问题就是需不需要使用外键(FOREIGN KEY)。外键是数据库用于实现参照完整型的约束,利用数据库的外键可以保证数据的完整性和一致性;外键的级联操作可以方便数据的自动处理,减少了程序出错的可能性。

例如,员工属于部门,员工的部门字段上可以创建一个外键引用部门表的主键。此时,我们必须先创建部门,然后才能为该部门创建员工;不会出现员工属于一个不存在的部门的情况,保证了数据的完整性。同时,如果要删除一个部门的话,必须同时处理该部门下的员工;可以选择级联删除员工或者将员工的部门修改为其他部门等操作。

既然外键拥有这么多好处,为什么我们还要讨论是否需要使用外键呢?主要是性能问题。因为任何事情都是有代价的,数据库为了维护外键需要牺牲一定的性能,尤其是在大数据量高并发的情况下。因此出现了另一种解决方案,就是将完整性检查放到应用层去实现,而应用程序相对比较容易扩展。

不过,在应用端实现约束也可能导致一些问题。首先,无法百分之百保证不会出现问题,尤其是多个应用同时共享一个数据库时。缺失外键可能导致数据库的结构不明确,需要依赖相应的文档进行说明。

总之,在系统的设计之初应该尽量使用外键确保完整性。如果随着业务增长出现性能问题,可以考虑在应用中实现约束。


总结

本文从非规范化数据库结构可能导致的问题出发,介绍了关系数据库为什么应该进行规范化设计以及常用的各种范式。同时,我们还讨论了特殊应用场景下的反规范化问题和外键的取舍。

如果觉得文章对你有用,欢迎关注❤️、评论????、点赞????