第一章、简介

数据库的设计规则，主要是表的设计规则，而表的设计规则中的主键与外键又是核心。

第二章、表基本的设计规则

2.1、表的基本设计规则

https://jingyan.baidu.com/article/642c9d340509f9644a46f781.html

https://bbs.****.net/topics/20146076?utm_source=blogxgwz26

https://blog.****.net/dannyiscoder/article/details/78688428

最好做好静态表和动态表的分离。这里解释一下静态表和动态表的含义，静态表：存储着一些固定不变的资源，比如城市/地区名/国家。动态表：一些频繁修改的表
不要有null值，有null值的话，数据库在进行索引的时候查询的时间更久，从而浪费更多的时间！建议可以为null的值转换成not null default ‘’
2张表的多对多的表关系，最好设计成3张表，即增加一张中间表，之前的两张表和中间表的关系是一对多的关系。
建表的时候，字段长度尽量要比实际业务的字段大3-5个字段左右(考虑到合理性和伸缩性)，最好是2的n次方幂值。不能建比实际业务太大的字段长度，这是因为如果字段长度过大，在进行查询的时候索引在B-Tree树上遍历会越耗费时间，从而查询的时间会越久；但是绝对不能建小，否则mysql数据会报错，程序会抛出异常；
对于频繁修改的字段(一般是指状态类字段)最好用独立的数字或者单个字母去表示，不用使用汉字或者英文
数据库不要存储任何资源文件，比如照片/视频/网站等，可以用文件路径/外链用来代替，这样可以在程序中通过路径，链接等来进行索引
关系映射：多对一或者一对多的关系,关联一张表最好通过id去建立关系，而不是去做重复数据，这样做最大的好处就是中间的关系表比较清楚明白。
通过单一字段表示该行记录是否可用，通过一个单一字段去控制表是否可用，比如通常起名为isVaild，预制的含义为0为有效，1为无效，这样便于以后我们去剔除数据或者重整数据，使其成为boolean性质的数据更加便于我们去操控。
预留备用字段：在设计一张表的时候应该预制2到3个空白字段，用于以后的扩展，因为你也不是确定这张表以后不会扩展。
建议单库不超过100个表。建议单表数据量不超过100W。建议单表字段数量上限控制在20~50个。
不经常变化的数据，比如具名身份证信息，需要建立数据词典。如果用户需求要增加变更配置项，只需要修改数据字典表记录即可，不需要修改代码。https://blog.****.net/weixin_42476601/article/details/84261992

1.2、基本表、中间表、临时表

基本表——就是你建数据库时用建的最开始的那个表，不是经过连接而得的。比如你要建一个学生管理数据库，其中学生表就是基本表。https://zhidao.baidu.com/question/435197188.html
临时表——临时表其实是放在数据库tempdb里的一个用户表，当你关闭sql连接的时候临时表就会自动删除，普通表不会。https://www.cnblogs.com/OpenCoder/p/7576602.html
中间表——中间表是数据库中专门存放中间计算结果的数据表。报表系统中的中间表是普遍存在的。一步算不出来、实时计算等待时间过长等情况，都会导致中间表的生成。https://blog.****.net/raqsoft/article/details/78970818

1.3、基本表的三个范式

转载https://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html

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

1.3.1．第一范式(确保每列原子性，即字段不能再分割了)

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

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

上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。

再比如价格 = 单价 * 数量，应该把价格这个字段，分为单价字段和数量字段。

1.3.2．第二范式(确保表中的每列都和主键相关。尽量不要设联合主键，一个表只设一个主键)

第二范式在第一范式的基础之上更进一层。

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

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

订单信息表：

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示：

这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

1.3.3．第三范式(确保每列都和主键列直接相关,而不是间接相关)

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

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

在数据冗余和处理速度之间找到合适的平衡点。原则是相对的，不是绝对的。
做表设计，读懂需求就对了。先不要管性能，先实现需求。表设计好了，写SQL的时候再考虑该合并，合并，该拆分，拆分。另外最关键的就是搞清楚一对一还是一对多。

1.4、信息冗余

信息冗余分为高级冗余(派生性冗余)，反对低级冗余(重复性冗余)。

高级冗余——商品中的“单价、数量、金额”三个字段，“金额”就是由“单价”乘以“数量”派生出来的，它就是高级冗余(派生性冗余)。高级冗余可以提升处理速度。
低级冗余——A表商品中的“单价”字段，B表中也出现了“单价”。B表一个单价就是低级冗余(重复性冗余)，其会浪费资源等等。
正确使用冗余——必需的数据冗余，可以用在数据恢复、数据核查、减少数据通讯开销等等。

1.5、运行效率

1.5.1、提升效率的常规方法

在系统硬件和系统软件条件确定的情况下，提高数据库系统的运行效率的办法是：

在数据库物理设计时，降低范式，增加冗余，少用触发器, 多用存储过程。
当计算非常复杂、而且记录条数非常巨大时(例如一千万条)，复杂计算要先在数据库外面，以文件系统方式用C++语言计算处理完成之后，最后才入库追加到表中去。这是电信计费系统设计的经验。
发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。
对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。
在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。
如果进行更新表的数据量较大，例如，更新的字段的值，需要重新从子表查询，且是全表查询，
如：UPDATE TABLE_A A SET COLUMN_A = (SELECT COLUMN_A FROM TABLE_A_B B WHERE A.COLUMN_B = B.COLUMN_B);其中每更新一行的字段，都会把括号内的sql执行一次，对于数据量大的表，其IO流会非常大，更新时间会较长，建议使用plsql过程语言，或使用merge into 语句进行更新。merge into 语句使用的是多块读，优点就是可以批量提交，缺点就是不能并行更新。

1.5.2、数据表与数据结构

数据结构的设计，一般采用不同的算法，比如二叉树算法等等。

正面教程——请参考

https://blog.****.net/xhyxxx/article/details/65937427

https://blog.****.net/jh19900712/article/details/24786159(转载)

反面教程——请参考

https://www.cnblogs.com/guokun/p/10492197.html

以上几个截图，详细的请查看链接。

好了，现在我要介绍树形结构来存储图片(医学上的Dicom图)，存储路径的形式如下所示：

若是我每天生成了1800W张图片，然后我要当天找到某张名字为ImageName1的图片。

一个文件夹存储——若是我把1800W张图片图片放到一个文件夹，那么需要遍历N次 = 1800W次。这种状态，会卡死你电脑。

多个问价夹存储——每个文件夹，作为一个节点，对应一张数据表，可以大大减少遍历次数。若是用完全二叉树遍历，找到ImageName1的图片，则需要M次 = log2(1800W) 次。效率大大提高。

第三章、表的主键与外键设计规则

3.1、主键

主键保证了数据的唯一性，外键保证了数据的完整性。
主键是能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。
主键中，自命名字段名过长的话，占用空间大，而且速度也慢。尽量采用自动增1的方式。

3.1.1、自增ID优点

增加记录时，可以不用指定id字段，不用担心主键重复问题。
数据库自动编号，速度快，而且是增量增长，按顺序存放，对于检索非常有利；
数字型，占用空间小，易排序，在程序中传递也方便；

3.1.2、自增ID缺点

不具有连续性，表中auto_increment最大值被删除，将不会被重用。就是说会跳号(如果设定的auto_increment_increment是1，那么下一次插入的id值将会从被删除的最大值算起，也就是被删除的最大值+1)。
历史数据表的主键id会与数据表的id重复，两张自增id做主键的表合并时，id会有冲突，但如果各自的id还关联了其他表，这就很不好操作。
很难处理分布式存储的数据表，尤其是需要合并表的情况下。
在系统集成或割接时，如果新旧系统主键不同是数字型就会导致修改主键数据类型，这也会导致其它有外键关联的表的修改，后果同样很严重。