厦门网站建设公司哪个好,免费做图网站,设计手机网站页面尺寸大小,网络公司做网站1.介绍
数据库规范化设计是数据库设计的一种重要方法#xff0c;旨在减少数据库中的冗余数据#xff0c;提高数据的一致性#xff0c;确保数据依赖合理#xff0c;从而提高数据库的结构清晰度和维护效率。规范化设计通过应用一系列的规范化规则#xff08;或称“范式”旨在减少数据库中的冗余数据提高数据的一致性确保数据依赖合理从而提高数据库的结构清晰度和维护效率。规范化设计通过应用一系列的规范化规则或称“范式”来实现。
1.1.常见的几种范式 第一范式1NF 数据库表的每一列都是不可分割的基本数据项同一列中的每个数据项都必须是单一值即表是一个二维表在任何一个时间点每个列都只能有一个值不允许有重复的列。 第二范式2NF 在第一范式的基础上所有非主属性完全依赖于主键而不仅仅是依赖于主键的一部分针对组合主键。这要求实现了第一范式的表必须把那些部分依赖的列移出去独立成表通过外键与原表关联。 第三范式3NF 在第二范式的基础上所有非主属性不仅完全依赖于主键而且还必须是直接依赖于主键而不是通过其他非主属性依赖即消除传递依赖。这意味着表中的非主属性不依赖于其他非主属性。 BCNF博伊斯-科得范式 比3NF更严格要求表中的每一个决定因素都必须是候选键。这主要处理了一些特殊情况在这些情况下即使表满足3NF标准也可能存在冗余。
数据库规范化设计的目的是为了减少数据冗余和改善数据完整性但是过度规范化可能会导致查询性能的下降和数据操作的复杂性增加。因此在实际的数据库设计过程中通常需要在规范化与性能之间做出权衡。
1.2.规范化设计的基本思想和准则
把一个低一级的关系模型分解为高一级关系模型的过程称为关系模型的规范化。关系 模型分解必须遵守两个准则。
无损连接性信息不失真不增减信息。函数依赖保持性不破坏属性间存在的依赖关系。
规范化的基本思想是逐步消除不合适的函数依赖使数据库中的各个关系模型达到某种 程度的分离。规范化解决的主要是单个实体的质量问题是对于问题域中原始数据展现的正 规化处理。
规范化理论给出了判断关系模型优劣的理论标准帮助预测模式可能出现的问题是数 据库逻辑设计的指南和工具具体有
用数据依赖的概念分析和表示各数据项之间的关系。消除 E-R 图中的冗余联系。
2.为什么要遵循数据库规范化设计
数据库规范化设计的出现主要是为了解决数据库在设计和使用过程中可能遇到的几个关键问题从而提高数据库的效率和可维护性。这些问题包括 数据冗余 在未规范化的数据库中相同的信息可能在多个地方重复存储。这不仅浪费存储空间还可能导致数据更新时的不一致性。如果某个数据项在一个地方被更新而在另一个地方没有更新那么就会产生数据不一致的问题。 更新异常Update Anomalies 当数据库表结构设计得不合理时可能会出现更新异常包括插入异常、更新异常和删除异常。比如如果某些数据需要被重复存储在更新时就需要更改多个地方如果忘记更新某些地方就会导致数据不一致。 插入异常Insertion Anomalies 在某些情况下因为表的设计不合理可能无法仅插入需要的信息而不插入其他额外信息。例如如果一个表设计成同时存储员工信息和部门信息那么在没有新员工加入时就无法仅添加一个新部门。 删除异常Deletion Anomalies 类似地如果一个表中存储了多种类型的信息删除记录时可能会导致某些本应保留的信息被意外删除。比如如果删除了最后一个属于某一部门的员工记录也可能意外地删除了该部门的信息。
数据库规范化设计通过将数据结构分解成较小的、逻辑上互相独立的表来解决这些问题。每个表专注于一个主题并通过关键字主键和外键相互关联。这样做有几个优点
减少了数据冗余节省存储空间。通过减少冗余降低了数据不一致的风险。简化了数据的维护使插入、更新和删除操作更加直接且不易出错。可以提高查询效率因为查询可以针对更小、更专注的表执行。
总之数据库规范化设计是为了提高数据的完整性和降低数据冗余从而提高数据库的性能和维护的效率。然而在实际应用中过度规范化可能会导致查询性能下降因此在数据库设计时需要根据具体情况做出平衡。
3.为什么有时候要反规范化设计【不遵循数据库规范化设计】
数据库的反规范化设计是规范化设计的反向过程主要目的是为了优化数据库查询性能、减少查询复杂度、提高数据读取速度。反规范化通过有意地引入数据冗余和组合多个表来达到这些目的。在某些情况下尽管规范化设计可以提高数据一致性并减少数据冗余但它也可能导致查询性能下降尤其是在需要频繁执行大量联结操作的大型数据库系统中。因此在实际应用中为了平衡规范化带来的好处与性能需求可能会采取反规范化的策略。
反规范化设计的常见策略包括 合并表 将多个相关的表合并为一个表减少查询时需要的联结操作。这样做的缺点是可能会增加数据冗余和维护的复杂度。 添加冗余列 在某个表中添加额外的列来存储经常一起查询的数据以避免执行联结。这可以提高查询性能但需要额外的空间来存储冗余数据并且在数据更新时需要维护这些冗余列的一致性。 预计算汇总信息 存储计算得出的汇总信息如总和、平均值等避免每次查询时都进行计算。这适用于数据更新频率较低而查询频率较高的情况。 使用物化视图 物化视图是基于SQL查询结果的表实质上是将查询结果存储起来以便快速访问。它们可以用于存储经常需要执行的复杂查询的结果从而提高这些查询的性能。 使用索引 尽管不是传统意义上的反规范化技术但通过为数据库表添加适当的索引可以显著提高查询性能。索引可以帮助数据库更快地定位数据尤其是在大量数据的表中。
反规范化设计需要谨慎进行因为它可能会引入数据一致性和维护的问题。在决定使用反规范化时重要的是要权衡其带来的性能提升与潜在的数据冗余和一致性维护成本之间的关系。通常在数据读取操作远远多于数据更新操作且系统性能是一个关键考虑因素的场景中反规范化可能是一个有益的选择。
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, 鲁谷街道);如此便形成如下结构 #mermaid-svg-kVoiC4rSlL9YpXkB {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-kVoiC4rSlL9YpXkB .error-icon{fill:#552222;}#mermaid-svg-kVoiC4rSlL9YpXkB .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-kVoiC4rSlL9YpXkB .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-kVoiC4rSlL9YpXkB .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-kVoiC4rSlL9YpXkB .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-kVoiC4rSlL9YpXkB .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-kVoiC4rSlL9YpXkB .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-kVoiC4rSlL9YpXkB .marker{fill:#333333;stroke:#333333;}#mermaid-svg-kVoiC4rSlL9YpXkB .marker.cross{stroke:#333333;}#mermaid-svg-kVoiC4rSlL9YpXkB svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-kVoiC4rSlL9YpXkB .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-kVoiC4rSlL9YpXkB .cluster-label text{fill:#333;}#mermaid-svg-kVoiC4rSlL9YpXkB .cluster-label span{color:#333;}#mermaid-svg-kVoiC4rSlL9YpXkB .label text,#mermaid-svg-kVoiC4rSlL9YpXkB span{fill:#333;color:#333;}#mermaid-svg-kVoiC4rSlL9YpXkB .node rect,#mermaid-svg-kVoiC4rSlL9YpXkB .node circle,#mermaid-svg-kVoiC4rSlL9YpXkB .node ellipse,#mermaid-svg-kVoiC4rSlL9YpXkB .node polygon,#mermaid-svg-kVoiC4rSlL9YpXkB .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-kVoiC4rSlL9YpXkB .node .label{text-align:center;}#mermaid-svg-kVoiC4rSlL9YpXkB .node.clickable{cursor:pointer;}#mermaid-svg-kVoiC4rSlL9YpXkB .arrowheadPath{fill:#333333;}#mermaid-svg-kVoiC4rSlL9YpXkB .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-kVoiC4rSlL9YpXkB .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-kVoiC4rSlL9YpXkB .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-kVoiC4rSlL9YpXkB .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-kVoiC4rSlL9YpXkB .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-kVoiC4rSlL9YpXkB .cluster text{fill:#333;}#mermaid-svg-kVoiC4rSlL9YpXkB .cluster span{color:#333;}#mermaid-svg-kVoiC4rSlL9YpXkB div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-kVoiC4rSlL9YpXkB :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;} tb_userinfo_3nf tb_address_info tb_xzqinfo 3.5.BC范式
让我们通过一个具体的例子来说明如何从第三范式3NF优化到博伊斯-科得范式BCNF。这个过程主要涉及识别并解决非候选键决定候选键的情况从而确保数据库结构中的每个决定因素都是候选键。
假设有一个学校的课程安排表CourseSchedule包含以下字段
RoomID教室IDCourseID课程IDTimeSlot上课时间InstructorID授课教师ID
这个表设计满足3NF每个非主属性都直接依赖于主键并且不存在非主属性依赖于其他非主属性的情况。但我们假设在这个学校中一个教师在同一个时间段只能教授一个课程而且这个课程只能在一个教室进行。这意味着InstructorID和TimeSlot联合起来可以唯一确定RoomID和CourseID。
这里虽然InstructorID和TimeSlot不是整个表的主键但它们作为一个组合却能决定其他字段这违反了BCNF的原则因为存在一个非候选键的组合决定了候选键。
3.5.1.解决步骤
为了将这个表结构优化到BCNF我们可以将表分解为两个表 课程安排表CourseSchedule CourseID课程ID主键RoomID教室IDTimeSlot上课时间 教师授课时间表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_idtb_address_info.id
left join tb_xzqinfo on tb_xzqinfo.xzqdmtb_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_idtb_address_info_fnf.id3.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.idtb_xzqinfo_county.pid
left join tb_xzqinfo tx_p on tx_p.idtx_city.pid3.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_typecounty)
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.idtb_xzqinfo_county.pid
left join tb_xzqinfo tx_p on tx_p.idtx_city.pid
