数据库规范化设计案例解析

1.介绍

数据库规范化设计是数据库设计的一种重要方法，旨在减少数据库中的冗余数据，提高数据的一致性，确保数据依赖合理，从而提高数据库的结构清晰度和维护效率。规范化设计通过应用一系列的规范化规则（或称“范式”）来实现。

1.1.常见的几种范式：

1. 第一范式（1NF）： 数据库表的每一列都是不可分割的基本数据项，同一列中的每个数据项都必须是单一值，即表是一个二维表，在任何一个时间点，每个列都只能有一个值，不允许有重复的列。

2. 第二范式（2NF）： 在第一范式的基础上，所有非主属性完全依赖于主键，而不仅仅是依赖于主键的一部分（针对组合主键）。这要求实现了第一范式的表必须把那些部分依赖的列移出去，独立成表，通过外键与原表关联。

3. 第三范式（3NF）： 在第二范式的基础上，所有非主属性不仅完全依赖于主键，而且还必须是直接依赖于主键，而不是通过其他非主属性依赖（即消除传递依赖）。这意味着表中的非主属性不依赖于其他非主属性。

4. BCNF（博伊斯-科得范式）： 比3NF更严格，要求表中的每一个决定因素都必须是候选键。这主要处理了一些特殊情况，在这些情况下即使表满足3NF标准，也可能存在冗余。

数据库规范化设计的目的是为了减少数据冗余和改善数据完整性，但是过度规范化可能会导致查询性能的下降和数据操作的复杂性增加。因此，在实际的数据库设计过程中，通常需要在规范化与性能之间做出权衡。

1.2.规范化设计的基本思想和准则

把一个低一级的关系模型分解为高一级关系模型的过程，称为关系模型的规范化。关系 模型分解必须遵守两个准则。

1. 无损连接性：信息不失真（不增减信息）。
2. 函数依赖保持性：不破坏属性间存在的依赖关系。

规范化的基本思想是逐步消除不合适的函数依赖，使数据库中的各个关系模型达到某种 程度的分离。规范化解决的主要是单个实体的质量问题，是对于问题域中原始数据展现的正 规化处理。

规范化理论给出了判断关系模型优劣的理论标准，帮助预测模式可能出现的问题，是数 据库逻辑设计的指南和工具，具体有：

1. 用数据依赖的概念分析和表示各数据项之间的关系。
2. 消除 E-R 图中的冗余联系。

2.为什么要遵循数据库规范化设计

数据库规范化设计的出现主要是为了解决数据库在设计和使用过程中可能遇到的几个关键问题，从而提高数据库的效率和可维护性。这些问题包括：

1. 数据冗余： 在未规范化的数据库中，相同的信息可能在多个地方重复存储。这不仅浪费存储空间，还可能导致数据更新时的不一致性。如果某个数据项在一个地方被更新而在另一个地方没有更新，那么就会产生数据不一致的问题。

2. 更新异常（Update Anomalies）： 当数据库表结构设计得不合理时，可能会出现更新异常，包括插入异常、更新异常和删除异常。比如，如果某些数据需要被重复存储，在更新时就需要更改多个地方，如果忘记更新某些地方，就会导致数据不一致。

3. 插入异常（Insertion Anomalies）： 在某些情况下，因为表的设计不合理，可能无法仅插入需要的信息而不插入其他额外信息。例如，如果一个表设计成同时存储员工信息和部门信息，那么在没有新员工加入时就无法仅添加一个新部门。

4. 删除异常（Deletion Anomalies）： 类似地，如果一个表中存储了多种类型的信息，删除记录时可能会导致某些本应保留的信息被意外删除。比如，如果删除了最后一个属于某一部门的员工记录，也可能意外地删除了该部门的信息。

数据库规范化设计通过将数据结构分解成较小的、逻辑上互相独立的表来解决这些问题。每个表专注于一个主题，并通过关键字（主键和外键）相互关联。这样做有几个优点：

• 减少了数据冗余，节省存储空间。
• 通过减少冗余，降低了数据不一致的风险。
• 简化了数据的维护，使插入、更新和删除操作更加直接且不易出错。
• 可以提高查询效率，因为查询可以针对更小、更专注的表执行。

总之，数据库规范化设计是为了提高数据的完整性和降低数据冗余，从而提高数据库的性能和维护的效率。然而，在实际应用中，过度规范化可能会导致查询性能下降，因此在数据库设计时需要根据具体情况做出平衡。

3.为什么有时候要反规范化设计【不遵循数据库规范化设计】

数据库的反规范化设计是规范化设计的反向过程，主要目的是为了优化数据库查询性能、减少查询复杂度、提高数据读取速度。反规范化通过有意地引入数据冗余和组合多个表来达到这些目的。在某些情况下，尽管规范化设计可以提高数据一致性并减少数据冗余，但它也可能导致查询性能下降，尤其是在需要频繁执行大量联结操作的大型数据库系统中。因此，在实际应用中，为了平衡规范化带来的好处与性能需求，可能会采取反规范化的策略。

反规范化设计的常见策略包括：

1. 合并表： 将多个相关的表合并为一个表，减少查询时需要的联结操作。这样做的缺点是可能会增加数据冗余和维护的复杂度。

2. 添加冗余列： 在某个表中添加额外的列来存储经常一起查询的数据，以避免执行联结。这可以提高查询性能，但需要额外的空间来存储冗余数据，并且在数据更新时需要维护这些冗余列的一致性。

3. 预计算汇总信息： 存储计算得出的汇总信息（如总和、平均值等），避免每次查询时都进行计算。这适用于数据更新频率较低而查询频率较高的情况。

4. 使用物化视图： 物化视图是基于SQL查询结果的表，实质上是将查询结果存储起来以便快速访问。它们可以用于存储经常需要执行的复杂查询的结果，从而提高这些查询的性能。

5. 使用索引： 尽管不是传统意义上的反规范化技术，但通过为数据库表添加适当的索引，可以显著提高查询性能。索引可以帮助数据库更快地定位数据，尤其是在大量数据的表中。

反规范化设计需要谨慎进行，因为它可能会引入数据一致性和维护的问题。在决定使用反规范化时，重要的是要权衡其带来的性能提升与潜在的数据冗余和一致性维护成本之间的关系。通常，在数据读取操作远远多于数据更新操作，且系统性能是一个关键考虑因素的场景中，反规范化可能是一个有益的选择。

3.案例说明数据库设计过程

3.1.建立一个基础的样例

我们先建立一张表，这张表是一张用户信息表，其中有用户的基本信息，包含用户名、年龄以及地址。

CREATE TABLE "public"."tb_userinfo" ("id" int4 NOT NULL,"loginname" varchar(10),"username" varchar(10),"age" int4,"address" varchar(255),PRIMARY KEY ("id")
)
;COMMENT ON COLUMN "public"."tb_userinfo"."id" IS 'ID';COMMENT ON COLUMN "public"."tb_userinfo"."loginname" IS '用户登录名称';COMMENT ON COLUMN "public"."tb_userinfo"."username" IS '用户名称';COMMENT ON COLUMN "public"."tb_userinfo"."age" IS '年龄';COMMENT ON COLUMN "public"."tb_userinfo"."address" IS '地址';

INSERT INTO "public"."tb_userinfo"("id", "loginname", "username", "age", "address") VALUES (1, 'xiaocai', '小菜', 18, '北京市石景山区鲁谷街道');

3.2.第一范式

我们在看数据库设计时，也要结合现实情况去考虑我们的表是否满足范式。如上的案例在使用中如果对地址信息没有更加细节的操作则是满足第一范式的。

我们先来看看第一范式的具体定义：

 数据库表的每一列都是不可分割的基本数据项，同一列中的每个数据项都必须是单一值，即表是一个二维表，在任何一个时间点，每个列都只能有一个值，不允许有重复的列。

从中我们可以提取出：

• 唯一性，表中的数据有主键，表中的每一行都是唯一的。
• 原子性，表中的每一列都是不可分割的基础数据项。

3.2.1.不满足第一范式的场景

当实际生产中我们需要查询用户表中北京市或者北京市石景山行政区范围的数据时，则是不满足第一范式的场景的。应为address在这个场景中需要细化，是可以拆分的。

所以我们为了使表满足第一范式，新增表（不修改，为了对比）tb_userinfo_1nf

CREATE TABLE "public"."tb_userinfo_1nf" ("id" int4 NOT NULL,"loginname" varchar(10),"username" varchar(10),"age" int4,"province" varchar(10),"prefecture_city" varchar(10),"county" varchar(10),"details_address" varchar(255),PRIMARY KEY ("id")
)
;COMMENT ON COLUMN "public"."tb_userinfo_1nf"."id" IS 'ID';COMMENT ON COLUMN "public"."tb_userinfo_1nf"."loginname" IS '用户登录名称';COMMENT ON COLUMN "public"."tb_userinfo_1nf"."username" IS '用户名称';COMMENT ON COLUMN "public"."tb_userinfo_1nf"."age" IS '年龄';COMMENT ON COLUMN "public"."tb_userinfo_1nf"."province" IS '省名称';
COMMENT ON COLUMN "public"."tb_userinfo_1nf"."prefecture_city" IS '市名称';
COMMENT ON COLUMN "public"."tb_userinfo_1nf"."county" IS '县名称';
COMMENT ON COLUMN "public"."tb_userinfo_1nf"."details_address" IS '详细地址';

INSERT INTO "public"."tb_userinfo_1nf"("id", "loginname", "username", "age", "province", "prefecture_city", "county", "details_address") VALUES (1, 'xiaocai', '小菜', 18, '北京市', '北京市区', '石景山区', '鲁谷街道');

3.2.1.1.后续影响

在上面的表结构调整中，我们有正面的作用也有负面的影响

3.2.1.1.1.正面作用

当我们需要按照行政区查询时，我们可以基于如下方式进行查询

select * from tb_userinfo_1nf where province='北京市' and prefecture_city='北京市区' and county='石景山区'

3.2.1.1.2.负面影响

在上面的满足第一范式的情况下，我们将address信息进行了拆解，但是造成了很多重复的操作，当我们需要修改行政区信息时，需要修改4个字段，分别为：province、prefecture_city、county、details_address，会造成：

• 遗漏修改导致数据库错误
• 更新字段多，数据操作效率降低

3.3.第二范式

在第一范式的修改中我们发现经过拆解之后，冗余字段多了。而且如果我们需要查询行政区树，得到的结果只能是tb_userinfo_1nf表中可以查询到的数据，但是实际上北京市的行政区数据不以数据为转移，他就在那。

所以我们优化之前的表设计到第二范式如下：

CREATE TABLE "public"."tb_userinfo_2nf" ("id" int4 NOT NULL,"loginname" varchar(10),"username" varchar(10),"age" int4,"xzqdm" varchar(10),"details_address" varchar(255),PRIMARY KEY ("id")
)
;COMMENT ON COLUMN "public"."tb_userinfo_2nf"."id" IS 'ID';COMMENT ON COLUMN "public"."tb_userinfo_2nf"."loginname" IS '用户登录名称';COMMENT ON COLUMN "public"."tb_userinfo_2nf"."username" IS '用户名称';COMMENT ON COLUMN "public"."tb_userinfo_2nf"."age" IS '年龄';COMMENT ON COLUMN "public"."tb_userinfo_2nf"."xzqdm" IS '县级行政区代码';COMMENT ON COLUMN "public"."tb_userinfo_2nf"."details_address" IS '详细地址';

CREATE TABLE "public"."tb_xzqinfo" ("id" int4 NOT NULL,"pid" int4,"xzqdm" varchar(10),"xzqmc" varchar(50),PRIMARY KEY ("id")
)
;INSERT INTO "public"."tb_xzqinfo"("id", "pid", "xzqdm", "xzqmc") VALUES (1, NULL, '11', '北京市');
INSERT INTO "public"."tb_xzqinfo"("id", "pid", "xzqdm", "xzqmc") VALUES (2, 1, '1101', '北京市区');
INSERT INTO "public"."tb_xzqinfo"("id", "pid", "xzqdm", "xzqmc") VALUES (3, 2, '110101', '东城区');
INSERT INTO "public"."tb_xzqinfo"("id", "pid", "xzqdm", "xzqmc") VALUES (4, 2, '110107', '石景山区');

3.4.第三范式

如果在这个时候我们有很多的详细地址都在鲁谷街道。则就需要我们再次修改改进

CREATE TABLE "public"."tb_userinfo_3nf" ("id" int4 NOT NULL,"loginname" varchar(10),"username" varchar(10),"age" int4,"address_id" int4,PRIMARY KEY ("id")
)
;COMMENT ON COLUMN "public"."tb_userinfo_3nf"."id" IS 'ID';COMMENT ON COLUMN "public"."tb_userinfo_3nf"."loginname" IS '用户登录名称';COMMENT ON COLUMN "public"."tb_userinfo_3nf"."username" IS '用户名称';COMMENT ON COLUMN "public"."tb_userinfo_3nf"."age" IS '年龄';COMMENT ON COLUMN "public"."tb_userinfo_3nf"."address_id" IS '详细地址ID';INSERT INTO "public"."tb_userinfo_3nf"("id", "loginname", "username", "age", "address_id") VALUES (1, 'xiaocai', '小菜', 18, 1);

CREATE TABLE "public"."tb_address_info" ("id" int4 NOT NULL,"xzqdm" varchar(10),"details_address" varchar(50),PRIMARY KEY ("id")
)
;INSERT INTO "public"."tb_address_info"("id", "xzqdm", "details_address") VALUES (1, '110107', '鲁谷街道');

如此便形成如下结构

3.5.BC范式

让我们通过一个具体的例子来说明如何从第三范式（3NF）优化到博伊斯-科得范式（BCNF）。这个过程主要涉及识别并解决非候选键决定候选键的情况，从而确保数据库结构中的每个决定因素都是候选键。

假设有一个学校的课程安排表CourseSchedule，包含以下字段：

• RoomID（教室ID）
• CourseID（课程ID）
• TimeSlot（上课时间）
• InstructorID（授课教师ID）

这个表设计满足3NF：每个非主属性都直接依赖于主键，并且不存在非主属性依赖于其他非主属性的情况。但我们假设在这个学校中，一个教师在同一个时间段只能教授一个课程，而且这个课程只能在一个教室进行。这意味着InstructorID和TimeSlot联合起来可以唯一确定RoomID和CourseID。

这里，虽然InstructorID和TimeSlot不是整个表的主键，但它们作为一个组合却能决定其他字段，这违反了BCNF的原则，因为存在一个非候选键的组合决定了候选键。

3.5.1.解决步骤

为了将这个表结构优化到BCNF，我们可以将表分解为两个表：

1. 课程安排表（CourseSchedule）：

   • CourseID（课程ID，主键）
   • RoomID（教室ID）
   • TimeSlot（上课时间）

2. 教师授课时间表（InstructorTeachingSchedule）：

   • InstructorID（授课教师ID，主键的一部分）
   • TimeSlot（上课时间，主键的一部分）
   • CourseID（课程ID）

在这种分解后的设计中，CourseSchedule表中的RoomID完全依赖于CourseID和TimeSlot，而InstructorTeachingSchedule表确保了InstructorID和TimeSlot的组合唯一确定了CourseID，满足BCNF的要求。这样，每个表都确保了它的所有决定因素都是候选键，解决了原设计中的问题。

3.6.反规范化

上面的规范化都是建立在数据数据统一维护的思想上，这样在运维时本质上只需要修改一处就可以达到应对需求的变动，但是数据想要获取更加完全的信息则需要频繁的表关联才可以，如第三范式中，我们想要知道某个人的地址，并且需要显示其省名称、市名称则需要编写如下sql。

select tb_userinfo_3nf.*,tb_xzqinfo.xzqdm,tb_xzqinfo.xzqmc,tb_address_info.details_address from tb_userinfo_3nf 
left join tb_address_info on tb_userinfo_3nf.address_id=tb_address_info.id
left join tb_xzqinfo on tb_xzqinfo.xzqdm=tb_address_info.xzqdm

在这个例子中，如果我们的数据体谅增加，更多的连接操作，必然影响查询速度，所以为了提高某些查询，我们会做一些破坏规范的行为，我们称之为（反规范化处理）。

3.6.1.增加冗余列

对于上述的案例中，我们在日常业务场景中，行政区信息的变化是非常罕见的，但是我们在查询时是需要关联的，所以我们可以将行政区信息中一些不会频繁（比如半年、一年以上才有可能变化，甚至永远不变）的信息添加到表中。如这个案例中我们将行政区名称写入到表中，形成如下表:

CREATE TABLE "public"."tb_address_info_fnf" ("id" int4 NOT NULL,"xzqdm" varchar(10),"xzqmc" varchar(10),"details_address" varchar(50),PRIMARY KEY ("id")
)
;INSERT INTO "public"."tb_address_info_fnf"("id", "xzqdm", "xzqmc", "details_address") VALUES (1, '110107', '石景山区', '鲁谷街道');

如此我们的查询便可以优化为如下查询：

select tb_userinfo_3nf.*,tb_address_info_fnf.xzqdm,tb_address_info_fnf.xzqmc,tb_address_info_fnf.details_address from tb_userinfo_3nf 
left join tb_address_info_fnf on tb_userinfo_3nf.address_id=tb_address_info_fnf.id

3.6.2.重新组表或物化视图

在增加冗余列中我们看到了如果我们需要查询县级行政区，可以增加冗余字段，如果我们现在需要查询市级、省级呢？

我们可以将前面的树表行政区，重新构建为一张详情表，从叶子节点开始组织。

create  materialized view mvw_xzq_hierarchy
as
WITH tb_xzqinfo_county AS (SELECT  txc.id,txc.xzqmc,txc.xzqdm,txc.pidFROM tb_xzqinfo txcwhere txc.id not in (select pid from tb_xzqinfo tx where tx.id!=txc.id AND tx.pid is not null))
select 
tb_xzqinfo_county.xzqdm as county_code,tb_xzqinfo_county.xzqmc as county_name,
tx_city.xzqdm as city_code,tx_city.xzqmc as city_name,
tx_p.xzqdm as province_code,tx_p.xzqmc as province_namefrom tb_xzqinfo_county
left join tb_xzqinfo tx_city on tx_city.id=tb_xzqinfo_county.pid
left join tb_xzqinfo tx_p on tx_p.id=tx_city.pid

3.6.3.辅助字段，减少复杂度

在上面重新组表的代码中我们发现以下sql的可读性、运行效率都好

WITH tb_xzqinfo_county AS (SELECT  txc.id,txc.xzqmc,txc.xzqdm,txc.pidFROM tb_xzqinfo txcwhere txc.id not in (select pid from tb_xzqinfo tx where tx.id!=txc.id AND tx.pid is not null))

这个里面是通过id信息下是否存在子节点便判定其为叶子节点，如果我们在数据库表上增加一个字段，其便可以变为：

create  materialized view mvw_xzq_hierarchy_fzzd
as
WITH tb_xzqinfo_county AS (SELECT  txc.id,txc.xzqmc,txc.xzqdm,txc.pidFROM tb_xzqinfo txcwhere txc.xzq_type='county')
select 
tb_xzqinfo_county.xzqdm as county_code,tb_xzqinfo_county.xzqmc as county_name,
tx_city.xzqdm as city_code,tx_city.xzqmc as city_name,
tx_p.xzqdm as province_code,tx_p.xzqmc as province_namefrom tb_xzqinfo_county
left join tb_xzqinfo tx_city on tx_city.id=tb_xzqinfo_county.pid
left join tb_xzqinfo tx_p on tx_p.id=tx_city.pid