www.129028.com金沙数据库逻辑设计反模式1-存储多值

日期:2019-11-15编辑作者:数据库

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

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

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

  www.129028.com金沙 1

目标:变成多对多关系

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

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

www.129028.com金沙 2

存储多值问题

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

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

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

解决方案

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

www.129028.com金沙 3

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

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

反模式适用场景

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

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

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

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

存储多值的适应场景:

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

本文由www.129028.com金沙发布于数据库,转载请注明出处:www.129028.com金沙数据库逻辑设计反模式1-存储多值

关键词:

参数探测(Parameter Sniffing)影响存储过程执行效率解决方案

如果SQL query中有参数,SQL Server 会创建一个参数嗅探进程以提高执行性能。该计划通常是最好的并被保存以重复利用。...

详细>>

数据库系统概念》www.129028.com金沙:18-事务

事务是一个程序执行单元,它访问且可能更新不同的数据项。事务对于实现数据库中的数据更新是很关键的,只有这...

详细>>

SQL简单查询后续记录

--首先创建数据库TEST CREATE DATABASE TEST --创建表tb_user USE TEST CREATE TABLE [tb_user]( [name] [nvarchar] (50) NOT NULL, [age] [int] NOT N...

详细>>

Mac mysql安装失败解决方法

在mac终端通过命令安装mysql,提示错误,解决方法如下: 完整的MAC搭建私链,参考如下三篇博客即可,再次不再赘述...

详细>>