sql反模式:乱穿马路
程序员通常使用逗号分隔的列表在多对多的关系中创建交叉表。这种设计模式定义为一种反模式,成为乱穿马路(Jaywalking),乱穿马路是避免过十字路口的一种方式。
1.目标:存储多属性值
2.反模式:格式化的逗号分隔程序
案例:
- 将某个用户指定为产品的主要联系人 这时产品和用户是一对一的关系,把用户的account_id直接存入到产品表中
- 产品可能有多个联系人 这时产品与用户是一对多的关系,不改变表设计,直接通过逗号分隔把用户account_id列表存入到表的一个account_id中
- 每个人可能有多个产品
使用逗号分隔的列表在场景将碰到问题
1)查询指定的账号的产品
不能使用等号,不得不使用某类模式
2)查询指定产品的账号的信息可能毁掉使用任何索引的可能
3) 聚合查询
4) 更新指定产品的账号
要从列表中删除一个条目,必须执行两条SQL查询:1.提取老的列表2.存储更新后的列表
5)验证产品ID
如果用户的ID设定时不全是数字,数据库就会很乱。
6)选择合适的分隔符
不合适的选择分隔符甚至会导致存储的信息中就包含分隔符
7)列表长度限制
且因为起初设定的account_id属性的类型长度有限,很可能导致无法存储下多个account_id。
3. 这种反模式怎么识别
团队成员说的话
- 列表最多支持存放多少数据
- 你知道在SQL中如何做分词查找吗
- 哪些字符不会出现在任何一个列表条目中
解决方法:交叉表
CREATE TABLE Contacts{ product_id BIGINT UNSIGNED NOT NULL, account_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY(product_id ,account_id ), FOREIGN KEY(product_id) REFERENES Products(product_id ), FOREIGN KEY(account_id ) REFERENES Accounts(account_id) }
总结:每个值都应该存储在各自的行和列中。