查看: 365|回复: 0

[SQLServer] 数据库逻辑设计反模式1-存储多值

发表于 2018-1-8 08:00:02

在SQL反模式这本书中以产品和产品联系人说明了此反模式。

开始的时候一个产品只有一个产品联系人,一个产品联系人需要负责多个产品,

product_id(产品id)和account_id(产品联系人id)是多对一的关系,表设计如下:

目标:变成多对多关系

随着业务的发展, 一个产品可能存在多个产品联系人,即产品和联系人需要是多对多的关系。

为了尽少的改动,即把account_id修改varchar类型,把所有的产品联系人Id用逗号分隔一起存储在account_id字段,即存储多值的做法,表设计如下

存储多值问题

但存储多值的设计会带来如下查询、更新和数据完整性的问题:

  1. 查询某个联系人负责的所有产品信息困难
  2. 查询某个产品对应的联系人详细信息困难
  3. 执行聚合查询困难,如COUNT(),SUM(),AVG(),比如统计各产品的联系人数量
  4. 更新产品的联系人困难
  5. 验证产品联系人id困难,varchar类型可以输入非整形等,没有外键约束
  6. 长度限制,当某个产品的所有联系人id连接起来超过100时,意味着需要字段长度,无法确定最长列表

所以,在设计多对多逻辑关系时,如果一个字段需要存储多值,需要避免此种反模式。

解决方案

创建一张交叉表,来保存多对多的关系,表设计如下:

这样前面的问题都可以比较简单的解决,采用Contacts.account_id做索引的查询效率比逗号分隔字符串高效,

还可以在Contacts表中增加一些其他字段,如添加联系人日期,再比如联系人的优先级等

反模式适用场景

这个反模式个人用过,是在设计一个配置表的时候,包含配置项和配置值两个字段,在配置值里存储了多值。

比如查询关键字配置,配置项字段值为查询关键字,配置值字段值就存储了很多关键字,因为多值的记录总共就一条,所以程序比较容易控制。

另外如果作为存储过程的参数,为了是实现in查询时,让参数存储多值时,如果输入值的长度超过参数长度,会出现截断问题导致程序结果异常,这个需要当心的。

所以在反模式书中也强调并不是说反模式不能使用,是有适应场景的。

存储多值的适应场景:

  1. 如果应用程序接收的输入就是带逗号分隔的
  2. 只需要存储和使用,不会进行部分修改
  3. 不需要对其做复杂的查询


回复

使用道具 举报