一、表的设计步骤

参考网址：https://www.jianshu.com/p/62867b4ff514

不知道各位看官，是否有过想尝试设计表的想法呢？
数据间纷乱复杂的关系，又该如何下手呢？
满足什么样的设计原则，才可以符合正确、完整、一致且安全的要求呢？
或许，小采风想和各位一起学习一下表的设计规范。子曾经曰过：欲穷天下之事，深谙套路二字。踏着深深的套路，走进今天的力学笃行。

一、表的设计步骤

• 需求分析：存储 处理 安全 完整

有位同学，专职于后台产品经理，负责产品具体的业务逻辑，有幸有过一次深聊。业务逻辑，需要正确的框架和允许范围的波动。人算不如天算，充分考虑产品的存储需求、数据处理需求、数据的安全需求和完整性要求，这是文档要求，我们这些小白能做的，莫过于尽力符合文档需求，往往不是一蹴而就的。

• 逻辑设计：结构 关系 冗余 异常

什么样的表设计，是足够合理呢？符合三大范式设计原则的表，至少是正确完整的，但是往往不是足够高效的，需要进行一定的反范式化设计。
1 第一范式（不再具体介绍）
字段只有单一属性且不可分割，由基本数据类型组成，必须是简单的二维表；
2 第二范式
不能允许非主键列对部分主键存在依赖关系；
3 第三范式
既不部分依赖于业务主键，也不传递依赖于业务主键；

看完三大范式，不要急着骂小采风。这不就是天书吗？完全没有办法去理解，我们还是看一看具体的例子，尝试搞清楚这些苦涩的范式吧！

//从选课表中理解第二范式
create table select_course1(
   stu_id int(10) comment '学号',
   stu_name varchar(20) comment '姓名',
   course_name varchar(10) comment '课程名称',
   course_credit int(2)  comment '学分',
   //学生和课程作为主键，唯一标识
   primary key(stu_id,course_name)
)engine=Innodb;

简单一看，这个表的设计，是没有问题的。可是，如果有一天高数从6个学分变成8个学分了，我们还需要一步步更改表中每行记录，完成高数学分的修改吗？问题的本质是，非主键course_credit列对复合主键列（stu_id,course_name）中的course_name存在部分依赖，即高数学分依赖于高数课程，违反第二范式会产生插入、更新和删除异常，应该将其更改为两张表，具体如下：

//更改为选择表和课程表
create table select_course2(
   stu_id int(10) comment '学号',
   stu_name varchar(20) comment '姓名',
   course_name varchar(10) comment '课程名称',
   //学生和课程作为主键，唯一标识
   primary key(stu_id,course_name)
)engine=Innodb;
create table course(
   course_name varchar(10) comment '课程名称',
   course_credit int(2) comment '学分',
   primary key(course_name)
)engine=Innodb;

那么存在一个问题，如何生成一张和select_course1相同的表呢？可以使用关联（join）将select_course2和course连接生成；

稍事休息，一起来看一看第三范式的理解：

//学生信息表
create table stu_info(
   stu_id int(10) comment '学号',
   stu_name varchar(20) comment '姓名',
   school_name varchar(20) comment '学院名称',
   school_tel varchar(20) comment '学院电话',
   primary key(stu_id)
)engine=Innodb;

这张表的问题在于，从学号可以知道学院名称，从学院名称可以知道学院电话，本质是，school_tel部分依赖于stu_id，即学院电话部分依赖于学院学号，违反第三范式，应该将其拆分成两张表，具体如下：

create table stu_info(
   stu_id int(10) comment '学号',
   stu_name varchar(20) comment '姓名',
   school_name varchar(20) comment '学院名称',
   primary key(stu_id)
)engine=Innodb;
create table school_info(
   school_name varchar(20) comment '学院名称'，
   school_tel varchar(20) comment '学院电话',
   primary key(school_name)
)engine=Innodb;

关于三大范式的理解，先暂时告一段落，不然小采风已经迷乱。

• 物理设计：规范 存储引擎 数据类型 结构

物理设计主要考虑一下问题：

1 定义数据库、表及字段的命名规范
2 选择合适的存储引擎
3 为字段选择合适的数据类型
4 数据库结构

• 维护优化：SQL优化 索引优化

个人认为，相比于前面三点，这点是最为重要，像是终极大boss一样，需求分析会存在漏洞，逻辑设计会有不足之处，物理设计会有bug，但是维护优化环节，需要更多的经验和更细致的考量。近日，了解了些数据库架构中的内容，主从拓扑，主从切换，MMM和MHA等知识，主库的读写分离，从库的读负载均衡，主从切换时的异步延迟，从库IO线程读取binlog日志的不同，binlog日志bindump命令的阻塞等，面对这样历史大难题，小采风只能知难而退了，留给看官中的您，让世界更美好了。

本文似乎有点头重脚轻，说是力学笃行篇，却并没有一点干货呢？看官们不要着急，下面让我们从现实出发，看看现实场景中，一个业务的表是如何设计的（不再进行具体的表创建等），尾随套路的步伐，一步步登上封顶。

二、需求分析

• 产品需求
  实现图书类的电商网站
• 具备功能
  用户登录、用户管理、商品展示、商品管理、在线销售
• 以电商网站为例考虑

三、逻辑设计

• 用户登录及管理

//用户信息表：用户名为主键
用户名，密码，手机号，姓名，出生日期，在线状态
//具体分析
1 以用户名为主键，满足第二范式设计；
2 不存在部分依赖和传递依赖关系，符合第三范式设计

• 商品展示及管理

//商品信息表：（商品名称，分类名称）联合主键
商品名称，分类名称，出版社名称，图书价格，
图书描述，作者
//具体分析
1 图书描述对商品名称存在依赖关系；
2 仅有分类名称，没有分类描述，完整性不足；

可以考虑将商品信息拆分成三张表，如下：

//商品信息表：商品名称为主键
商品名称，出版社名称，图书价格，图书描述，作者
//分类信息表：分类名称为主键
分类名称，分类描述
//商品分类关系表：（商品名称，分类名称）联合主键
商品名称，分类名称

• 订单管理

//订单表：订单编号为主键
订单编号，下单用户，下单日期，订单金额，
订单商品分类，订单商品名，订单商品单价，
订单商品数量，支付金额
//具体分析
1、订单商品分类，订单商品名，订单商品单价已经
存在于商品及分类表中，数据严重冗余；
2、订单金额可以有订单计算而来；

考虑将订单表分为下面两张表，如下：

//订单表：订单编号为主键
订单编号，下单用户名，下单日期，支付金额
//订单商品关联表：（编号，分类，商品名）联合主键
订单编号，订单商品分类，订单商品名，商品数量

以上设计，当然不是最合理的，不过基本满足三大范式的要求，基本实现正确性、完整性和一致性，但是这样的文档式设计，能实现高效查询吗？

//查询某位用户的订单总金额
select 下单用户名，sum（d.商品价格*b.商品数量）
from 订单表 a join 订单商品关联表 b on a.订单编号=b.订单编号
join 商品分类关系表 c on c.商品名称=b.商品名称 
and c.分类名称=b.分类名称
join 商品信息表 d on d.商品名称=c.商品名称
group by 下单用户名
//具体分析
一条简单的查询，需要关联四张表，这样会严重影响查询效率

教科书中，往往并不是最理想的情况，仍然需要对表进行适当的修改：

• 反范式化设计：增加冗余，减少表的关联

//1 在订单商品关联表中增加商品价格字段，
减少关联商品信息表
订单编号，订单商品分类，订单商品名，
商品数量，商品价格
//2 去除商品分类信息表，
直接基于商品信息表和分类信息表查询

//查询某位用户的订单总金额
select 下单用户名，sum(b.订单商品名*b.商品数量)
from 订单表 a join 订单商品关联表 b
on a.订单编号=b.订单编号
group by 下单用户名

世界总是这般公平，反范式化通过增加冗余，提高查询效率，但是同时增加表结构的冗余和数据维护异常的难度，所以，适当情境下自由适当权衡。

四、物理设计

• 存储引擎选择
  不同的场景需要不同的存储引擎选择，主要是以下几个方面：
  事务支持，表锁行锁，读写性能，索引优化，主键外键
• 字段数据类型选择
  1 选择原则
  优先考虑数字类型，其次是日期或二进制类型，最后是字符类型；
  相同级别的数据类型，优先选择占空间小的数据类型；
  2 主键选择
  主键应尽可能小，因为主键索引中包含非主键属性；
  主键应该是顺序增长的，提高插入和删除效率；
  3 VARCHAR和CHAR类型：以字符为单位，不是以字节为单位
  VARCHAR：
  字符串列的最大长度比平均长度大，发挥变长字符特性；
  字符串序列少被更新，减少存储碎片；
  CHAR：
  字符串序列存储长度近似的值；
  适合存储短字符串；
  适合存储更新频度比较高的字符串；
  4 整数类型和实数类型
  整数类型的位数，由实际场景选择；
  float和double类型的数据精度问题，在财务类应用中需要注意；
  5 日期类型：推荐使用date
  字符串存储8字节，datetime存储4字节，int存储4字节，date类型3字节；
  date类型有很多日期时间函数可以使用；

纸上得来终觉浅，觉知此事要躬行。昨天舍友参加了阿里中间件技术的介绍，主讲人一句话，如果说淘宝技术是喜马拉雅山脉 ，那么中间件技术就是珠穆朗玛峰。在mysql的架构中，中间件用于洪水猛兽般的高并发请求时的读写分离和读负载均衡，这是技术造福于社会的终极智慧，向工程师看齐。好久没有这样的长文了，四个字，与君同行。

 

 

57人点赞

 

数据库

 

 

作者：采风JS
链接：https://www.jianshu.com/p/62867b4ff514
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。