数据库设计规范

关系型数据库有六种常见范式

1、按照范式级别,从低到高:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF,完美范式)

2、数据库的范式设计越高阶,冗余度就越低,同时高阶范式一定符合低阶范式的要求

3、一般在关系型数据库设计中,最高遵循 BCNF,普遍 3NF

4、反规范化:为了提高某些查询性能,还需要破坏范式规则

 

键和相关属性

1、范式的定义会使用到主键和候选键,数据库中的键(Key)由一个或者多个属性组成

2、超键 / 码:能够唯一标识一条记录的属性或属性集

(1)标识性:一个数据表的所有记录都具有不同的超键

(2)非空性:不能为空

(3)有些时候也把码称作“键”

3、候选键 / 候选码:能够唯一标识一条记录的最小属性集

(1)标识性:一个数据表的所有记录都具有不同的候选键

(2)最小性:任一候选键的任何真子集都不能唯一标识一个记录,比如在成绩表中(学号,课程号)是一个候选键,单独的学号、课程号都不能决定一条记录

(3)非空性:不能为空

(4)如果超键不包括多余的属性,那么这个超键就是候选键,即候选键是最小的超键

4、主键:某个能够唯一标识一条记录的最小属性集

(1)唯一性:一个数据表只能有一个主键

(2)标识性:一个数据表的所有记录都具有不同的主键取值

(3)非空性:不能为空

(4)人为的选取某个候选码为主码

5、外键:如果数据表 R1 中的某属性集不是 R1 的主键,而是另一个数据表 R2 的主键,那么这个属性集就是数据表 R1 的外键

6、主属性:包含在任一候选键中的属性称为主属性。简单来说,主属性是候选码所有属性的并集

7、非主属性:不包含在候选码中的属性称为非主属性。非主属性是相对于主属性来定义的

8、全码:当所有的属性共同构成一个候选码时,这时该候选码为全码,例如(教师,课程,学生),假如一个教师可以讲授多门课程,某门课程可以有多个教师讲授,学生可以听不同教师讲授的不同课程,那么,要区分关系中的每一个元组,这个关系模式 R 的候选码应为全部属性构成 (教师、课程、学生),即主码

9、代理键:当不适合用任何一个候选键作为主键时(如数据太长等),添加一个没有实际意义的键作为主键,这个键就是代理键,(如常用的序号 1、2、3)

10、自然键:自然生活中唯一能够标识一条记录的键(如身份证)

 

第一范式

1、最核心,所有表必须满足

2、要求:任何一张表必须有主键,确保数据表中每个字段的值必须具有原子性,每一个字段不可再分,即数据表中每个字段的值为不可再次拆分的最小数据单位

 

第二范式

1、建立在第一范式的基础之上,还要满足数据表里的每一条数据记录,都是可唯一标识的

2、关系模式 R 满足第一范式,并且 R 得所有非主属性都完全依赖于 R 的每一个候选关键属性

3、如果知道主键的所有属性的值,就可以检索到任何元组(行)的任何属性的任何值

4、非主键只依赖候选键的问题

(1)数据冗余

(2)插入异常

(3)删除异常

(4)更新异常

 

第三范式

1、建立在第二范式的基础之上

2、要求:所有非主键字段直接依赖主键,不要产生传递依赖;所有非主键字段之间不能有依赖关系,必须相互独立

3、设 R 是一个满足第一范式条件的关系模式,X 是 R 的任意属性集,X 非传递依赖于 R 的任意一个候选关键字

 

反范式化

1、遵循业务优先原则:首先满足业务需求,再尽量减少冗余

2、反范式化:通过在数据表中增加冗余字段来提高数据库的读性能

3、规范化、性能

(1)性能比规范化更重要

(2)通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间

(3)通过在给定的表中插入计算列,以方便查询

4、缺点

(1)占用更多存储空间

(2)修改一个表中字段,同步修改另一个表中冗余的字段,否则数据不一致

(3)若采用存储过程来支持数据的更新、删除等额外操作,如果更新频繁,非常消耗系统资源

(4)在数据量小的情况下,反范式不能体现性能的优势,可能还会让数据库的设计更加复杂

5、当冗余信息有价值,或能大幅度提高查询效率时,才会采取反范式的优化

6、增加冗余字段建议

(1)该冗余字段不需要经常进行修改

(2)该冗余字段查询时不可或缺

7、数据仓库、数据库

(1)数据库设计目的在于捕获数据,而数据仓库设计对历史数据的分析需求强

(2)数据库对数据的增删改实时性要求强,需要存储在线的用户数据,而数据仓库存储的一般是历史数据

(3)数据库设计需要尽量避免冗余 ,但为了提高查询效率也允许一定的冗余度,而数据仓库在设计上更偏向采用反范式设计

 

巴斯-科德范式

1、BCNF:Boyce-Codd NormalForm

2、BCNF 没有新的设计规范加入,只是对第三范式中设计规范要求更强,使得数据库冗余度更小,称为修正的第三范式 / 扩充的第三范式,不被称为第四范式

3、若一个关系达到第三范式,并且它只有一个候选键,或它的每个候选键都是单属性,则该关系自然达到 BCNF 范式

4、在 3NF 的基础上,消除主属性对候选键的部分依赖,或传递依赖关系

5、如果在关系 R 中,U 为主键,A 属性是主键的一个属性,若存在 A -> Y,Y 为主属性,则该关系不属于 BCNF

 

第四范式

1、多值依赖:属性之间的一对多关系,记为 K → A

(1)平凡的多值依赖:全集 U = K + A,一个 K 可以对应于多个 A,即 K → A,此时整个表就是一组一对多关系

(2)非平凡的多值依赖:全集 U = K + A + B,一个 K 可以对应于多个 A,也可以对应于多个 B,A 与 B 互相独立,即 K → A,K → B,整个表有多组一对多关系,且一组是相同的属性集合,多组是互相独立的属性集合

(3)函数依赖事实上是单值依赖,所以不能表达属性值之间的一对多关系

2、第四范式在满足 BCNF 基础上,消除非平凡且非函数依赖的多值依赖,即把同一表内的多对多关系删除

 

第五范式

1、完美范式 / 域键范式(DKNF)

2、在满足第四范式(4NF)基础上,消除不是由候选键所蕴含的连接依赖,如果关系模式 R 中的每一个连接依赖,均由 R 的候选键所隐含,则称此关系模式符合第五范式

3、函数依赖是多值依赖的一种特殊的情况,而多值依赖实际上是连接依赖的一种特殊情况,但连接依赖不像函数依赖和多值依赖可以由语义直接导出 ,而是在关系连接运算时才反映出来,存在连接依赖的关系模式仍可能遇到数据冗余及插入、修改、删除异常等问题

4、第五范式处理无损连接问题,该范式基本没有实际意义 ,因为无损连接很少出现,而且难以察觉,实用价值也是最小,只存在理论研究中

 

ER 模型

1、实体关系模型

2、描述现实生活中客观存在的事物、事物的属性,以及事物之间关系的一种数据模型

3、在开发基于数据库的信息系统的设计阶段,通常使用 ER 模型来描述信息需求和信息特性,理清业务逻辑,从而设计出数据库

4、三要素

(1)实体:数据对象,对应现实生活中的真实存在的个体,在 ER 模型中,用矩形来表示;实体分为两类:强实体、弱实体;强实体:不依赖于其他实体的实体;弱实体:对另一个实体有很强的依赖关系的实体

(2)属性:实体的特性,在 ER 模型中用椭圆形来表示

(3)关系:实体之间的联系,在 ER 模型中用菱形来表示

5、区分实体、属性,从系统整体的角度,可以独立存在的是实体,不可再分的是属性,即属性不能包含其他属性

6、在 ER 模型的 3 个要素中,关系又可以分为 3 种类型

(1)一对一:指实体之间的关系是一一对应的

(2)一对多:指一边的实体通过关系,可以对应多个另外一边的实体,相反,另外一边的实体通过这个关系,则只能对应唯一的一边的实体

(3)多对多:指关系两边的实体,都可以通过关系,对应多个对方的实体

7、ER 模型转换成具体数据表

(1)一个实体,通常转换成一个数据表

(2)一个多对多的关系 ,通常转换成一个数据表

(3)一个 1 对 1,或 1 对多的关系,通过表的外键来表达,而不是设计一个新的数据表

(4)属性转换成表的字段

 

数据表的设计原则

1、数据表的个数越少越好

(1)关系数据库管理系统的核心在于对实体和联系的定义,即 E-R 图(Entity Relationship Diagram)

(2)数据表越少,证明实体和联系设计得越简洁,既方便理解又方便操作

2、数据表中的字段个数越少越好

(1)字段个数越多,数据冗余的可能性越大

(2)设置字段个数少的前提是各个字段相互独立,而不是某个字段的取值可以由其他字段计算出来

(3)字段个数少是相对的,通常在数据冗余、检索效率中进行平衡

3、数据表中联合主键的字段个数越少越好

(1)设置主键是为了确定唯一性,当一个字段无法确定唯一性时,就需要采用联合主键的方式,即多个字段来定义一个主键

(2)联合主键中的字段越多,占用的索引空间越大,不仅加大理解难度,还增加运行时间和索引空间

4、使用主键和外键越多越好

(1)数据库的设计实际上就是定义各种表,以及各种字段之间的关系,这些关系越多,证明这些实体之间的冗余度越低,利用度越高

(2)不仅保证了数据表之间的独立性,还能提升相互之间的关联使用率

 

数据库对象编写规范

1、关于库

(1)【强制】库的名称必须控制在 32 个字符以内,只能使用英文字母、数字和下划线,建议以英文字母开头

(2)【强制】库名中英文一律小写,不同单词采用下划线分割,须见名知意

(3)【强制】库的名称格式:业务系统名称_子系统名

(4)【强制】库名禁止使用关键字

(5)【强制】创建数据库时必须显式指定字符集,并且字符集只能是 utf8 或 utf8mb4

(6)【建议】对于程序连接数据库账号,遵循权限最小原则。使用数据库账号只能在一个 DB 下使用,不准跨库。程序使用的账号原则上不准有 DROP 权限

(7)【建议】临时库以 tmp_ 为前缀,并以日期为后缀;备份库以 bak_ 为前缀,并以日期为后缀

2、关于表、列

(1)【强制】表和列的名称必须控制在 32 个字符以内,表名只能使用英文字母、数字和下划线,建议以英文字母开头

(2)【强制】表名、列名一律小写,不同单词采用下划线分割,须见名知意

(3)【强制】表名要求有模块名强相关,同一模块的表名尽量使用统一前缀

(4)【强制】创建表时必须显式指定字符集为 utf8 或 utf8mb4

(5)【强制】表名、列名禁止使用关键字

(6)【强制】创建表时必须显式指定表存储引擎类型,如无特殊需求,一律为 InnoDB

(7)【强制】建表必须有 comment(注释)

(8)【强制】字段命名应尽可能使用表达实际含义的英文单词或缩写

(9)【强制】布尔值类型的字段命名为 is_ 描述

(10)【强制】禁止在数据库中存储图片、文件等大的二进制数据,通常文件很大,短时间内造成数据量快速增长,数据库进行数据库读取时,通常会进行大量随机 I/O 操作,文件很大时,I/O 操作很耗时,通常存储于文件服务器,数据库只存储文件地址信息

(11)【建议】建表时关于主键:表必须有主键;强制要求主键为 id,类型为 int 或 bigint,且为 auto_increment 建议使用 unsigned 无符号型;标识表里每一行主体的字段不要设为主键,并建立 unique key 索引,因为如果设为主键且主键值为随机插入,则会导致 Innodb 内部页分裂和大量随机 I/O,性能下降

(12)【建议】核心表(如用户表)必须有行数据的创建时间字段(create_time)和最后更新时间字段(update_time),便于查问题

(13)【建议】表中所有字段尽量都是 NOT NULL 属性,业务可以根据需要定义 DEFAULT 值,因为使用 NULL 值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题

(14)【建议】所有存储相同数据的列名和列类型必须一致,一般作为关联列,如果查询时关联列类型不一致,会自动进行数据类型隐式转换,会造成列上的索引失效,导致查询效率降低

(15)【建议】中间表(或临时表)用于保留中间结果集,名称以tmp_开头。备份表用于备份或抓取源表快照,名称以bak_开头。中间表和备份表定期清理

(16)【示范】一个较为规范的建表语句

CREATE TABLE user_info ( 
    `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键', 
    `user_id` bigint(11) NOT NULL COMMENT '用户id', 
    `username` varchar(45) NOT NULL COMMENT '真实姓名', 
    `email` varchar(30) NOT NULL COMMENT '用户邮箱', 
    `nickname` varchar(45) NOT NULL COMMENT '昵称', 
    `birthday` date NOT NULL COMMENT '生日', 
    `sex` tinyint(4) DEFAULT '0' COMMENT '性别', 
    `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己,最多50个汉字', 
    `user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址', 
    `user_register_ip` int NOT NULL COMMENT '用户注册时的源ip', 
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', 
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', 
    `user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态,1为通过,2为审核中,3为未 通过,4为还未提交审核',
    PRIMARY KEY (`id`), 
    UNIQUE KEY `uniq_user_id` (`user_id`), 
    KEY `idx_username`(`username`), 
    KEY `idx_create_time_status`(`create_time`,`user_review_status`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息';

(17)【建议】创建表时,可以使用可视化工具,可以确保表、字段相关的约定都能设置上,实际通常很少手动写 DDL 语句,可以使用一些可视化工具来创建和操作数据库和数据表,可视化工具除了方便,还能直接帮我们将数据库的结构定义转化成 SQL 语言,方便数据库和数据表结构的导出和导入

3、关于索引

(1)【强制】InnoDB 表必须主键为 id int / bigint auto_increment,且主键值禁止被更新

(2)【强制】InnoDB 和 MyISAM 存储引擎表,索引类型必须为 BTREE

(3)【建议】主键的名称以 pk_ 开头,唯一键以 uni_ 或 uk_ 开头,普通索引以 idx_ 开头,一律使用小写格式,以字段的名称或缩写作为后缀

(4)【建议】多单词组成的列名,取前几个单词首字母,加末单词组成索引名,如:sample 表 member_id 上的普通索引名:idx_sample_mid

(5)【建议】单个表上的索引个数不能超过 6 个

(6)【建议】在建立索引时,多考虑建立联合索引,并把区分度最高的字段放在最前面

(7)【建议】在多表 JOIN 的 SQL 里,保证被驱动表的连接列上有索引,这样 JOIN 执行效率最高

(8)【建议】建表或加索引时,保证表里互相不存在冗余索引

 

PowerDesigner

1、数据库建模工具

2、制作数据流程图、概念数据模型、 物理数据模型

3、包括数据库模型设计的全过程

posted @   半条咸鱼  阅读(435)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示