sql反模式(一)--乱穿马路

【个人博客：http://www.80soho.com/?p=328】

场景

功能开发中肯定遇到过下列情况：
如下表，最初的设计产品（product_id）与联系人（account_id）是一对一关系，后期产品经理过来找到你说，产品可以对应多个联系人（我会拎砖头砸过去，违法勿学），一个似乎简单且合理的解决方案可以快速解决需求变更：account_id,由int改成使用逗号分隔的字符串（曾经这么干过的肯定不在少数...）。



通常使用逗号分隔的列表来避免在多对多的关系中创建交叉表，这种设计方案定义为一种反模式，暂叫乱穿马路

问题

这样的设计似乎可行，因为没有创建额外的表或者列，仅仅改变了一个字段的数据类型。我们来看一下这样的设计所必须承受的各方面问题：

• 字段类型长度不能保证不修改

当保存的长度超出varchar(100)时,肯定会出问题，varchar(100)不得不被修改，下次修改只是时间问题，少不了；

• 查询制定联系人的产品

查询不能再使用等号，无法享受索引带来的性能优势，变得异常困难，eg:

• 查询制定产品的联系人

联合两张表并使用如上的一句表达式将毁掉任何使用索引的可能，这样的查询必须扫描两张表，创建一个交叉结果集，然后使用正则表达式遍历每一行联合后的数据进行匹配。

• 执行聚合查询

聚合查询使用内置的聚合函数，这些函数是针对分组行而设计的，并不是为了逗号分隔的列表。下图方法看上去很高明，单不清晰，且需要很长的时间来开发和调试，何况有些聚合查询根本无法使用这些技巧来完成。

• 更新指定产品的联系人繁琐
• 无法验证产品ID
• 选择合适的分隔符
• 列表长度限制

解决方案

创建一张交叉表：将account_id存储在一张单独的表中，而不是存储在产品表(jw_products)中，从而每个独立的account值都可以占据一行，来实现产品和联系人的多对多关系。

---

---

---

以上内容是tonglei参考《SQL反模式》于2017-10-10写下！