数据库常见错误分析(一)

从最开始在大学学习<<数据库原理>>接触数据库，到现在也有七八年的时间了，一直没有好好的梳理和归纳过，最近读了一本SQL方面的书--<<SQL反模式>>。这是一本从常见的错误思路出发，来介绍SQL使用和开发中遇到的一些误区，以及如何有效避免这些误区，避免“踩坑”。
结合平常遇到的案例及书中的思路，做一个小小的总结。

逻辑数据库设计

一. 当对照关系不是唯一的情况下，不管是一对多还是多对多的情况下，不要用一个栏位或者一个表结构去存储，无论从扩展性还是从性能上考虑，都应该要分开的。
（对应章节：第二章：乱穿马路/第八章：多列属性）
举两个简单的例子说明一下
1. 最原始的需求是建立一个产品详细资料明细表，要求每一个产品有对应的联系人

create table products{
         product_id int ;              //产品ID
         product_name varchar(20);     //产品名称
         acount_id int                 //联系人ID
         }

当需求变更为一个产品可能出现多个联系人的时候，可能出现的处理方案就有好多了：
1.1 将acount_id的数据类型更改为varchar类型，每个联系人的ID用分隔符隔开；
    这貌似是一种非常简单并且合理的方案，数据库的改动非常小。但实际上这是一种非常失败的方案，而且在后期开发会遇到诸多问题：
    1）存储资料的长度收到限制，受限于栏位和存储数据的长度，栏位长度越短或数据长度越长都会导致能够存储的资料量小；
    2）在查询时无法使用索引；
    3）更新花费代价高，每次更新必须读取出所有数据，然后经过额外的处理调整数据，再重新写入；
    4）资料的关联性高，当某一个产品的资料异常时，所有的联系人资料可能都会同时出现丢失。
1.2 新建立一个产品与联系人的对照关系表
    这个方案从调整的工作量来看，初期可能会高于前一个方案，涉及到这个表的所有SQL和逻辑都需要重新调整，但从整体来看，是非常值得的，不仅避免的第一个方案出现的问题，扩展性也更好，比如对每个联系人添加具体加入到产品的日期或者在产品的是处于第几等级的联系人等需求时，我们对采取第一种方案的人就只能说“呵呵”了。

2. 仍然以前面一段的案例来说，针对每一个产品，都会有不同的标签，而且根据分类不同，同一个产品也有不同的标签，比如说《SQL反模式》这本书，可以归类到数据库类，也可以从作者角度归类到外国书籍。
针对这个问题，同样有两种方案：
2.1 根据上面的案例我们知道每个栏位只存储一个值是比较好的方案，所以针对每一种不同的分类建立一个栏位。有多少中分类就有多少个栏位。
这个方案完全符合上面所说的要求，每一列都只存储一个标签，按理说应该是比较好的方案，但是实际上不是这样子的，在实际的使用过程中会出现以下问题：
1）查询数据时，如果需要得到一个产品的每一个属性，那么需要将所有列都进行一次判断；
2）更新数据时，遇到了和之前类似的问题，资料的关联性高；
3）无法实现预知所有的分类情况，所以会陷入不断调整表结构的情况，尤其是当这个表的资料量足够大时，每一次调整都将是一次灾难；
2.2 新建立一个产品与标签的对照关系表和一个标签的基本资料表，针对上述问题，无论怎么增加标签类别，也只是资料层面的事情，而不是设计到结构性的变化。

当然，并不是说在任何情况下都必须建立对照关系表才是最有方案，如果能找到两个结构之间一定存在唯一的对照关系，那么直接用一个栏位标记就会更简单明了。比如说要增加一个关于产品的作者（不考虑多个人合力完成的情况，或者是最要的贡献者）。那么直接现在到产品基本资料档就是一个比较合适的方案，既可以使查询变得简单，在性能上也有不错的体现。