【重难点】【MySQL 08】数据库建表规范、数据库设计三大范式、数据库设计经验

【重难点】【MySQL 08】数据库建表规范、数据库设计三大范式、数据库设计经验

一、数据库建表规范

1.表命名规范

1. 表名前应该加上前缀，表的前缀用系统或者模块的英文名称缩写，前缀全部大写或者首字母大写，表名中包含的单词首字母大写
2. 表名应该有意义，并且易于理解，最好使用可以表达功能的英文单词或缩写，如果用英文单词表示，建议使用完整的英文单词
3. 表名不可以太长，最好不要超过 3 个英文单词长度（22 个字母）
4. 表名的英文单词应该使用单数形式，如员工表命名：应为 Employee，而不是 Employees
5. 如果是后台表命名时应该在表名基础上加上后缀 _b（back 首字母）
6. 在表创建完成前，应该为表添加表的注释

2.表字段命名规范

1. 数据库表字段应该是有意义而且易于理解的，最好是能够表达字段含义的英文字母
2. 系统中所有属于内码，即仅用于标识唯一性和程序内部用到的标识性字段，字段名称建议取为 ID，采用类型为整型或长整型
3. 系统中属于是业务内的编号字段，代表一定的业务信息，建议字段命名为 code，如工作单编号 wf_code
4. 不要在字段名中包含数据类型和数据库关键字，如：datetime
5. 不要在字段名命名时重复表名，可以使用表名首字母（不包括表名前缀）

3.表设计规范

1. 所有字段在设计时，除以下数据类型：timestamp、image、datetime、smalldatetime、uniqueidentifier、sql_variant、binary、varbinary，必须有默认值。字符型的默认值为一个空字符串 ’ '，数值型的默认值为 0，逻辑型的默认值也为 0，且 0 表示 “假”，1 表示 “真”，datetime、smalldatetime 类型的字段没有默认值，必须为 NULL
2. 当字段定义为字符型时，建议使用 varchar，而不用 nvarchar
3. 建议在大多数表中，应该都有 CreatorID（创建者）和 CreatedTime（创建时间）
4. 字段必须填写描述信息
5. 尽量遵守第三范式的标准
6. 加索引规则
   • 表建好后数据库自动为表生成一个索引（为自动增长的列生成唯一索引），如果再对这一列添加索引，数据库会给出一个警告，内容大致是：已经为这一列添加了索引，建议修改索引名称和自动增长列名保持一致
   • 在添加索引时，建议索引名称和列名保持一致
   • 如果字段与其它表的关键字相关联而未设置为外键，需要建立索引
   • 如果字段需做模糊查询之外的条件查询，需要建立索引
   • 除了主键允许建立聚簇索引外，其它字段建立索引必须为非聚簇索引

4.存储过程命名规范

存储过程的命名请遵循以下命名规范：USP_ + 系统模块缩写（与表前缀类似）+ _ + 功能标识 + 存储过程操作的主要表名或功能的英文单词或英文单词缩写。例如：

• 用于新增的存储过程：USP_MESSAGE_Add_Model
• 用于修改的存储过程：USP_MESSAGE_Upt_Model
• 用于删除的存储过程：USP_MESSAGE_Del_Model

注：USP 是 user stored procedure 的首字母

5.存储过程设计规范

在存储过程中必须说明以下内容：

1. 目的：说明此存储过程的作用
2. 作者：此存储过程的创建者，在此请使用中文全名，不允许使用英文
3. 创建日期
4. 修改记录：修改记录需包含修改顺序号、修改者、修改日期、修改原因。修改时不能直接在原来的代码上修改，也不能删除原来的代码，只能先将原来的代码注释掉，再重新增加正确的代码。修改顺序号的形式为：log1、log2、log3…，根据修改次数顺序增加，同时在注释掉的原来的代码块和新增的代码块前后注明修改顺序号
5. 对存储过程各参数及变量的中文注释

建议：在数据库中创建一个文本文件保存创建脚本

6.视图命名规范

视图的命名请遵循以下命名规范：UV_ + 系统模块缩写（与表前缀类似）+ _ + 功能标识 + 代表视图查询的主要表名或功能的英文单词或英文单词缩写

注：UV 是 userView 的缩写

7.视图设计规范

在视图中必须说明以下内容：

1. 目的：说明此视图的作用
2. 创建者：请使用中文全名，不允许使用英文
3. 修改记录：包括修改者、修改日期、修改原因
4. 对视图各参数及变量的中文注释

建议：在数据库中创建一个文本文件保存创建脚本

8.触发器命名规范

Insert 触发器加 ‘_i’，Delete 触发器加 ‘_d’，Update 触发器加 ‘_u’

9.触发器设计规范

在触发器中必须说明以下内容：

1. 目的：说明此触发器的作用
2. 创建者：请使用中文全名，不允许使用英文
3. 修改记录：包括修改者、修改日期、修改原因
4. 对触发器各参数及变量的中文注释

建议：在数据库中创建一个文本文件保存创建脚本

二、数据库设计三大范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式，在实际开发中最为常见的设计范式有三个

1.第一范式（确保每列保持原子性）

第一范式是最基本的范式，如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式

想要合理遵循第一范式需要根据系统的实际需求。比如某些数据库系统中需要用到 “地址” 这个属性，本来直接将 “地址” 属性设计成一个数据库表的字段就行，但是如果系统经常访问 “地址” 属性中的 “城市” 部分，那么就一定要将 “地址” 这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式

2.第二范式（确保表中的每列都和主键相关）

第二范式在第一范式的基础之上更进一层，第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表只能保存一种数据，不可以把多种数据保存在同一张数据表中

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的复合主键。这样就产生了一个问题：这个表是以订单编号和商品编号作为复合主键，这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关，所以这里违反了第二范式的设计原则。而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。这样设计，在很大程度上减小了数据库的冗余，如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可

3.第三范式（确保每列都和主键直接相关，而不是间接相关）

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不是间接相关

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户的其它信息（比如姓名、所属公司等）。这样在查询订单信息的时候，可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的的内容，减小了数据冗余

4.反范式化

其实关系型数据库不止 3 种范式，目前一共有 6 种，从低到高分别是：1NF、2NF、3NF、BCNF（巴斯-科德范式）、4NF 和 5NF（又叫完美范式）。范式等级越高，冗余越小，一般来说，数据库只需满足第三范式即可

没有冗余的数据库设计可以做到，但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体的做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段、允许冗余，以达到用空间换时间的目的

5.范式化和反范式化的优缺点

范式化
优点：

• 可以尽量地减少数据冗余
• 范式化的更新操作比反范式化更快
• 范式化的表通常比反范式化更小

缺点：

• 对于查询需要对多个表进行关联
• 更难进行索引优化

反范式化
优点：

• 可以减少表的关联
• 可以更好地进行索引优化

缺点：

• 存在数据冗余及数据维护异常
• 对数据的修改需要更多的成本

三、数据库设计经验

1.原始单据与实体之间的关系

可以是一对一、一对多、多对多的关系。在一般情况下，它们是一对一的关系：即一张原始单据对应且只对应一个实体。在特殊情况下，它们可能是一对多或多对一的关系，即一张原始单据对应多个实体，或多张原始单据对应一个实体。这里的实体可以理解为基本表，明确这种对应关系后，对我们设计录入界面大有好处

例如，一份员工履历资料，在人力资源信息系统中，对应了三个基本表：员工基本情况表、社会关系表、工作简历表

2.主键与外键

主键与外键的设计，在全局数据库的设计中，占有重要地位。一般而言，一个实体不能既无主键又无外键。在 E-R 图中，处于叶子部位的实体，可以定义主键，也可以不定义主键，但必须要有外键

3.基本表的性质

基本表与中间表、临时表不同，因为它具有如下四个特性：

1. 原子性：基本表中的字段是不可再分解的
2. 原始性：基本表中的记录是原始数据（基础数据）的记录
3. 演绎性：由基本表与代码表中的数据，表中的记录是要长期保存的

理解基本表的型之后，在设计数据库时，就能将基本表与中间表、临时表区分开来

4.要善于识别与正确处理多对多的关系

若两个实体之间存在多对多的关系，则应消除这种关系。消除的办法是在两者之间增加第三个实体。这样，原来一个多对多的关系，现在变为两个一对多的关系。要将原来两个实体的属性合理地分配到第三个实体中去。这里的第三个实体，实质上是一个较复杂的关系，它对应一张基本表。一般来说，数据库设计工具不能识别多对多的关系，但能处理多对多的关系

例如，在 “图书馆信息系统” 中，“图书” 是一个实体，“读者” 也是一个实体。这两个实体之间，是一个典型的多对多关系：一本书在不同时间可以被多个读者借阅，一个读者又可以借多本图书。为此，要在二者之间增加第三个实体，该实体取名为 “借还书”，它的属性为：借还时间、借还标志（0 表示借书、1 表示还书），另外，它还应该有两个外键（“图书” 的主键、“读者” 的主键），使它能与 “图书” 和 “读者” 连接

5.主键的取值方法

主键是供程序员使用的表间连接工具，可以是无意义的数字串，由程序自增来实现。也可以是有意义的字段名或字段名的组合。当主键是字段名的组合时，建议字段的个数不要太多，多了不但索引占用空间大，而且速度也慢

6.正确认识数据冗余

主键与外键在多表中的重复出现不属于冗余，这个概念必须清楚，事实上有许多人还不清楚。非键字段的重复出现才是数据冗余，而且是一种低级冗余，即重复性冗余。什么是高级冗余呢？高级冗余是指字段的派生出现

例如，商品中的 “单价、数量、金额” 三个字段，“金额” 是由 “单价” 乘以 “数量” 派生出来的，它就是冗余，属于高级冗余。冗余的目的是为了提高处理速度

低级冗余会增加数据的不一致性，因为统一数据，可能从不同时间、地点、角色上多次录入。因此，我们提倡高级冗余（派生性冗余），反对低级冗余（重复性冗余）

7.E-R图没有标准答案

信息系统的 E-R 图没有标准答案，因为它的设计与画法不唯一，只要它覆盖了系统需求的业务范围和功能内容，就是可行的。反之，要修改 E-R 图，尽管它没有唯一的标准答案，并不意味着可以随意设计。好的 E-R 图的标准是：结构清晰、关联简洁、实体个数适中、属性分配合理、没有低级冗余

8.视图技术在数据库设计中很有用

与基本表、代码表、中间表不同，视图是一种虚表，它依赖数据源的实表而存在。视图是供程序员使用数据库的一个窗口，是基表综合的一种形式，是数据处理的一种方法，是用户数据保密的一种手段。为了进行复杂处理、提高运算速度和节省存储空间。视图的定义深度一般不得超过三层，若三层视图仍不够用，则应在视图上定义临时表，在临时表上再定义视图。这样反复交迭定义，视图的深度就不受限制了

对于某些与国家政治、经济、技术、军事和安全利益有关的信息系统，视图的作用更加重要。这些系统的基本表完成物理设计之后，立即在基本表上建立第一层视图，这层视图的个数和结构，与基本表的个数和结构完全相同。并且规定，所有的程序员，一律只准在视图上操作。只有数据库管理员，带着多个人员共同掌握的 “安全钥匙”，才能直接在基本表上操作

9.中间表和临时表

中间表是存放统计数据的表，它是为数据仓库、输出报表或查询结果而设计的，有时它没有主键和外键（数据仓库除外）

临时表是程序员个人设计的，存放临时记录，为个人所用。基表和中间表由 DBA 维护，临时表由程序员自己用程序自动维护

10.完整性约束表现在三个方面

域的完整性：用 Check 来实现约束，在数据库设计工具中，对字段的取值范围进行定义时，有一个 Check 按钮，通过它定义字段的值域

参照完整性：用 PK、FK、表级触发器来实现

用户定义完整性：它是一些业务规则，用存储过程和触发器来实现

11.防止打补丁式数据库设计需要遵循“三少” 原则

1. 一个数据库中的表的个数越少越好。只有表的个数少了，才能说明系统的 E-R 图少而精，去掉了重复的多余的实体，形成了对客观世界的高度抽象，进行了系统的数据集成，放置了打补丁式的设计
2. 一个表中组合主键的字段个数越少越好。因为主键的作用，一是建立主键索引，二是作为子表的外键，所以组合主键的字段个数少了不仅节省了运行时间，而且节省了索引存储空间
3. 一个表中的字段个数越少越好。只有字段的个数少了，才能说明在系统中不存在数据重复，且很少有数据冗余，更重要的是要学会 “列变行”，这样就防止了将子表中的字段拉入到主表中去，在主表中留下许多空余的字段。所谓 “列变行”，就是将主表中的一部分内容拉出去，另外单独建一个子表。这个方法很简单，有的人就是不习惯、不采纳、不执行

数据库设计的实用原则是：在数据冗余的处理速度之间找到合适的平衡点。“三少” 是一个整体概念，综合观点，不能孤立某一个原则。该原则是相对的，不是绝对的

提倡 “三少” 原则是希望开发者学会利用数据库设计技术进行系统的数据集成。数据集成的步骤是将文件系统集成为应用数据库，将应用数据库集成为主题数据库，将主题数据库集成为全局综合数据库。集成的程度越高，数据共享性就越强，信息孤岛现象就越少，整个企业信息系统的全局 E-R 图中实体的个数、主键的个数、属性的个数就会越少

提倡 “三少” 原则的目的是防止开发者利用打补丁技术，不断地对数据库进行增删改，使企业数据库变成了随意设计的 “垃圾堆”，或 “大杂院”，最后造成数据库中的基本表、代码表、中间表、临时表过于庞杂，导致企业的信息系统难以维护

12.提高数据库运行效率的方法

在给定的系统硬件和系统软件条件下，提高数据库系统运行效率的方法有：

1. 在数据库物理设计时，降低范式、增加高级冗余、少用触发器、多用存储过程
2. 当计算非常复杂，而且记录条数非常巨大（例如一千万条），复杂计算要现在数据库外面，以文件系统方式用 C++ 语言计算处理完成之后，最后才入库追加到表中
3. 发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是：以该表主键的某个值为分界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分割为两个表
4. 对数据库管理系统 DBMS 进行系统优化，即优化各种系统参数，如缓冲区个数
5. 在使用面向数据的 SQL 语言进行程序设计时，尽量采取优化算法

总之，要提高数据库的运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化这三个层次上同时下功夫