数据库表的设计笔记

数据库设计简介

优良的设计	糟糕的设计
减少数据冗余	存在大量数据冗余
避免数据维护异常	存在数据插入，更新，删除异常
节约存储空间	浪费大量存储空间
高效的访问	访问数据低效

设计步骤

需求分析

数据库需求的作用点：

1. 数据是什么
2. 数据有哪些属性
3. 数据和属性各自的特点有哪些

逻辑设计

使用ER图对数据库进行逻辑建模

物理设计

根据数据库自身的特点把逻辑设计转换为物理设计。

维护优化

1. 新的需求进行建表
2. 索引优化
3. 大表拆分

需求分析

1. 了解系统中所要存储的数据
2. 了解数据的存储特点(例如：是否具有时效性,有时效性可采取过期清理的方案，大量非核心数据分布分表，归档，清理规则)
3. 了解数据的生命周期

要搞清楚的一些问题

1. 实体及实体之间的关系(1对1，1对多，多对多)
2. 实体所包含的属性有什么？
3. 哪些属性或属性的组合可以唯一标识一个实体。

实例演示

以一个小型的电子商务网站为例，在这个电子商务网站的系统中包括了几个核心模块:用户模块，商品模块，订单模块，购物车模块，供应商模块。

用户模块

用于记录注册用户信息

包括属性：用户名，密码，电话，邮箱，身份证号，地址，姓名，昵称…

可选唯一标识属性：用户名，身份证，电话

存储特点：随系统上线时间逐渐增加，需要永久存储。

商品模块

用于记录网站中所销售的商品信息

包括属性：商品编码，商品名称，商品描述，商品品种，供应商名称，重量，有效期，价格…

可选唯一标识属性：(商品名称，供应商名称)，（商品编码）

存储特点：对于下线商品可以归档存储。

订单模块

用于用户订购商品的信息

包括属性：订单号，用户姓名，用户电话，收获地址，商品编号，商品名称，数量，价格，订单状态，支付状态，订单类型…

可选唯一标识属性：(订单号)

存储特点：永久存储(分表，分库存储)

购物车模块

用于保存用户购物时选对的商品

包括属性：用户名，商品编号，商品名称，商品价格，商品描述，商品分类，加入时间，商品数量…

可选唯一标识(用户名，商品编号，加入时间)，(购物车编号)

存储特点：不用永久存储(设置归档，清理规则)

供应商模块

用于保存所销售商品的供应商信息

包括属性：供应商编号，供应商名称，联系人，营业执照号，地址，法人…

可选唯一标识：(供应商编号)，(营业执照号)

存储特点：永久存储

逻辑设计

1. 将需求转化为数据库逻辑模型
2. 通过ER图的型式对逻辑模型进行展示
3. 同所选用的具体的DBMS系统无关

名词解释

关系:一个关系对于通常所说的一张表。

元组：表中的一行即为一个元组。

属性：表中的一列即为一个属性；每一个属性都有一个名称，称为属性名。

候选码：表中的某个属性组，它可以唯一确定一个元组，即主键或唯一索引。

主码：一个关系有多个候选码，选的其中一个为主码。

域：属性的取值范围。

分量：元组中的一个属性值。

ER图例说明

矩形：表示实体集，矩形内写实体集的名字。

菱形：表示联系集。

椭圆：表示实体的属性。

线段：将属性连接到实体集，或将实体集连接到联系集。

设计范式概要

常见数据库设计范式包括：
第一范式，第二范式，第三范式及BC范式
当然还有第四及第五范式

这是目前我们大多数数据库设计所要遵循的范式。

数据操作异常及数据冗余

操作异常：

1. 插入异常：如果某实体随着另一个实体的存在而存在，即缺少某个实体时无法表示这个实体，那么这个表就存在插入异常。
2. 更新异常：如果更改表所对应的某个实体实例的单独属性时，需要将多行更新，那么就说这个表存在更新异常。
3. 删除异常：如果删除表的某一行来反映某实体实例失效时导致另一个不同实体实例信息丢失，那么这个表中就存在删除异常。

数据冗余：是指相同的数据在多个地方存在，或者说表中的某个列可以由其他列计算得到，这样就说表中存在着数据冗余。

第一范式

定义：数据库表中的所有字段都是单一属性，不可再分的。这个单一属性是由基本的数据类型所构成的，整数，浮点数，字符串，等；

换句话说 第一范式要求数据库中的表都是二维表。都有行和列构成。

第二范式

定义：数据库的表中不存在非关键字端对任一候选关键字段的部分函数依赖。
部分函数依赖是指存在着组合关键字(多个主键)中的某一关键字决定非关键字的情况。
换句话说：所有单关键字段的表都符合第二范式。

存在的问题：

1. 插入异常：上述图中饮料一厂如果不提供数据的话就找不到它的相关信息。只有它提供信息时才可以找到一厂的信息。
2. 删除异常：如果把可乐信息删除掉的话就找不到饮料一厂的消息。
3. 更新异常：如果要更新饮料一厂的电话的话要更新多行的电话
4. 数据冗余：饮料一厂的信息数据冗余。
   

第三范式

定义：第三范式是在第二范式的基础之上定义的，如果数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。

上述表存在问题：
(分类，分类描述)对于每一个商品都会进行记录，所以存在着数据冗余。同时也还存在数据的插入，更新及删除异常。

Boyce.Codd范式(BCNF)

定义：在第三范式的基础之上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BC范式。
也就是说如果是复合关键字，则复合关键字之间也不能存在函数依赖关系。

假定：供应商联系人只能受雇于一家供应商，每家供应商可以供应多个商品则存在如下决定关系：
(供应商，商品ID)->(联系人，商品数量)

(联系人，商品ID)->(供应商，商品数量)

存在下列关系因此不符合BCNF要求：
(供应商)->(供应商联系人)
(供应商联系人)->(供应商)
并且存数据操作异常及数据冗余。

对范式的个人理解

• 第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

• 第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。

• 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.

注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性

第一范式

1. 每一列属性都是不可再分的属性值，确保每一列的原子性
2. 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。

第二范式

每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。

例如：订单表只能描述订单相关的信息，所以所有的字段都必须与订单ID相关。产品表只能描述产品相关的信息，所以有的字段都必须与产品ID相关。因此在同一张表中不能同时出现订单信息与产品信息。

比如一张表是(A,B,C,D)，其中(A,B)是主键，如果存在B->C就违反了2NF，因为C只需要主键的一部分就可以被决定了

第三范式

数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。像：a–>b–>c属性之间含有这样的关系，是不符合第三范式的。比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）

这样一个表结构，就存在上述关系。 学号–> 所在院校 --> (院校地址，院校电话)

这样的表结构，我们应该拆开来，如下。

（学号，姓名，年龄，性别，所在院校）–（所在院校，院校地址，院校电话）

BC范式

在第三范式的基础上，不允许出现有主键的一部分被主键另一部分或者其他部分决定。

注意事项：

1.第二范式与第三范式的本质区别：在于有没有分出两张表。
第二范式是说一张表中包含了多种不同实体的属性，那么必须要分成多张表，第三范式是要求已经分好了多张表的话，一张表中只能有另一张标的ID，而不能有其他任何信息，（其他任何信息，一律用主键在另一张表中查询）。

2.必须先满足第一范式才能满足第二范式，必须同时满足第一第二范式才能满足第三范式。

物理设计要注意什么

1. 选择合适的数据库管理系统。
2. 定义数据库，表及字段的命名规范
3. 根据所选的DBMS系统选择合适的字段类型
4. 反范式化设计(空间换时间)

存储引擎(了解，主要使用默认引擎)

表及字段的命名规则

所有的对象命名应该遵循下述原则：

1. 可读性原则：使用大小写来格式化库对象名字以获得良好的可读性。(这里要注意有些DBMS系统对表名的大小写是敏感的)
2. 表意姓原则：对象的名字应该能够描述它所标识的对象。例如，对于表，表的名称应该能够体现表中存储的数据内容；对于存储过程，存储过程名称应该能够体现存储过程的功能。
3. 长名原则：尽可能少使用或者不使用缩写，适用于数据库名之外的任一对象。

字段类型的选择原则

列的数据类型一方面影响影响数据存储空间的开销，另一方面也会影响数据查询性能。当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型，对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

以上选择原则主要是从下面两个角度考虑：

1. 在对数据进行比较(查询条件，JOIN条件及排序)操作时：同样的数据，字符处理往往比数字处理慢。
2. 在数据库中，数据处理以页为单位，列的长度越小，利于性能提升。

char和varchar如何选择

原则：

1. 如果列中要存储的数据长度差不多是一致的，则应该考虑用char；否则应该考虑用varchar。
2. 如果列中的最大数据长度小于50Byte，则一般也考虑用char。(当然，如果这个列很少用，则基于节省空间和减少I/O的考虑，还是可以选择用varchar)
3. 一般不宜定义大于50Byte的char类型列。

注：utf-8每个占3个Byte 16个字符占48个Byte。

原因：varchar除了必要的数据外还需要额外的数据存储变长数据的相关信息，而在检索数据时也要检索数据的起始位置。

decimal和float如何选择

原则：

1. decimal用于存储精确数据，而float只能用于存储非精确数据。故精确数据只能选择用decimal类型。
2. 由于float的存储空间开销一般比decimal小(精确到7为小数只需要4个字节，而精确到15为小数只需要8字节)故非精确数据优先选择float类型。

时间类型如何存储

1. 使用int来存储时间字段的优缺点
   优点：字段长度比datetime小。
   缺点：使用不方便，要进行函数转换。
   限制：只能存储到2038-1-19 11：14：07即2^32为2147483648

2. 需要存储的时间粒度
   年 月 日 小时 分 秒 周

但使用int存储取出的时候还有进一步转化为datetime类型故如果存储的日期不常用到则使用int经常要用到则用datetime

如何选择主键

1. 区分业务主键和数据库主键
   主键用于标识业务数据，进行表与表之间的关联；数据库主键为了优化数据存储(没有人为规定主键，Innodb会生成6个字节的隐含主键，所以最好自己定义主键)
2. 根据数据库的类型，考虑主键是否要顺序增长
   有些数据库时按主键的顺序逻辑存储的
3. 主键的字段类型所占空间要尽可能的小
   对于使用聚集索引方式存储的表，每个索引后都会附加主键信息。

避免使用外键约束

1. 降低数据导入的效率(每次插入都要检查是否符合外键约束，高并发下会逐渐显现出其劣势，一般情况下对于高并发的互联网企业下，一般不要使用外键约束)
2. 增加维护的成本
3. 虽然不建议使用外键约束，但是相关联的列上一定要建立索引。(如果要进行表关联，虽然不建立外键但是要建立索引)

避免使用触发器

1. 降低数据导入的效率。
2. 可能会出现意想不到的数据异常。
3. 使业务逻辑变的复杂。

关于预留字段

1. 无法准确的知道预留字段的类型。
2. 无法准确的知道预留字段中所存储的内容。
3. 后期维护预留字段所需要的成本，同增加一个字段所需要的成本是相同的。
4. 严禁使用预留字段。(预留的额外的列)

反范式化表设计

反范式化是针对范式化而言的，在前面介绍了数据库设计的第三范式，所谓的反范式化就是为了性能和读取效率的考虑而适当的对第三范式的要求进行违反，而允许存在少量的数据冗余，换句话来说反范式化就是使用空间来换取时间。

为什么反范式化

1. 减少表的关联数量(减少磁盘的io操作)
2. 增加数据的读取效率
3. 反范式化一定要适度。(少量的反范式化也存在异常但异常是可控的)

数据库的维护和优化要做什么

维护和优化中要做什么

1. 维护数据字典
2. 维护索引
3. 维护表结构
4. 在适当的时候对表进行水平拆分或垂直拆分。

如何维护数据字典

1. 使用第三方工具对数据字典进行维护
2. 利用数据库本身的备注字段来维护数据字典。以MySQL为例。
   
3. 导出数据字典
   

如何维护索引

如何选择合适的列建立索引?

1. 出现在WHERE从句，GROUP BY从句，ORDER BY从句中的列
2. 可选择性高的列要放到索引的前面(并不是一定要这么做，sql优化器会进行部分优化)

注意事项

1. 索引并不是越多越好，过多的索引不但会降低写效率，而且会降级读的效率(sql优化器有了多种选择，选择的过程中会造成效率的下降)
2. 定期维护索引碎片。
3. 在SQL语句中不要使用强制索引关键字。
4. 索引中不要包括太长的数据类型。

如何维护表结构

注意事项

1. 使用在线变更表结构的工具
2. 同时对数据字典进行维护
3. 控制表的宽度和大小

数据库中适合的操作

1. 批量操作(数据库中适合)VS逐条操作(适合在产品中完成)
2. 禁止使用Selet * 这样的查询(会造成io浪费，表结构进行变更会对程序产生影响)
3. 控制使用用户自定义函数(对索引产生影响)
4. 不要使用数据库中的全文索引(需要另外建立索引文件，使用专业的搜索引擎进行替代)

数据库表的垂直拆分和水平拆分

表的垂直拆分

减少页每一行数据的数据量，提高查找速度。但数据量不能有变化。

表的水平拆分

减少每张表的数据量

参考