数据库设计规范
关系型数据库有六种常见范式
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、包括数据库模型设计的全过程
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战