一、表的设计步骤

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

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

一、表的设计步骤

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

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

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

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

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

//更改为选择表和课程表
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 '学院名称',
   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 不存在部分依赖和传递依赖关系，符合第三范式设计

• 商品展示及管理

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

• 订单管理

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

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

//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
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。