SQL反模式：实体-属性-值（EAV)问题（一）

    但以下关系存在时，可能会将数据设置成为EAV模式，比如一个问题（Issue），可能有继承它的bug和特性（FeatureRequest)，他们的关系如下，引用AntiPatterns

针对上面的关系，如果考虑表格的列可能扩展的情况，设计成EVA（Entity-Atrribute-Value)如下：

CREATE TABLE Issues (issue_id int PRIMARY KEY ); INSERT INTO Issues (issue_id) VALUES (1234); CREATE TABLE IssueAttributes ( issue_id BIGINT NOT NULL, attr_name VARCHAR(100) NOT NULL, attr_value VARCHAR(100), );

通过IssueAttributes表，描述和报告日期通过attribute-value对的方式存储起来，如下：

INSERT INTO IssueAttributes (issue_id, attr_name, attr_value) VALUES (1234, 'product', '1'), (1234, 'date_reported', '2009-06-01'), (1234, 'status', 'NEW'), (1234, 'description', 'Saving does not work'), (1234, 'reported_by', 'Bill'), (1234, 'version_affected', '1.0'), (1234, 'severity', 'loss of functionality'), (1234, 'priority', 'high');

上面的这种设计，优点在于，如果我想增加一个版本的列，可以直接插入一条记录即可，不需要增加一列。但是EAV模式，存在如下一些缺点。

1.不能精确定义数据类型，比如date_reported，报告日期，由于各个国家和地区使用日期格式不尽相同，有些是2020-02-02，有些是2/6/2020等等，甚至有些格式无法转成日期。这样显示和统计都会出错，比如，如下SQL也能插入成功

INSERT INTO IssueAttributes (issue_id, attr_name, attr_value) VALUES (1234, 'date_reported', 'haha'),

2.不能定义约束，使用EVA不能定义外键约束；

3.EAV模式中的attribute名称可能不能统一，比如有些插入的是'date_reported'，而有些是'report_date'，这样无法使用SQL查询；

3.重构查询结果问题

当我们使用 SELECT * FROM IssueAttributes WHERE issue_id =1234;这样语句查询出结果以后，出现了多行，其实我们期望的结果是一行，然后把其他行转化到列上去，尽管有些SQL已经支持了行转列了，但是这样的SQL抬复杂，在不考虑行转列的情况下，需要如下写SQL才可以达到一行，查询多少列就需要关联自身表多少次，降低了查询效率。

SELECT i.issue_id, i1.attr_value AS "date_reported", i2.attr_value AS "status", i3.attr_value AS "priority", i4.attr_value AS "description" FROM Issues AS i LEFT OUTER JOIN IssueAttributes AS i1 ON i.issue_id = i1.issue_id AND i1.attr_name = 'date_reported' LEFT OUTER JOIN IssueAttributes AS i2 ON i.issue_id = i2.issue_id AND i2.attr_name = 'status' LEFT OUTER JOIN IssueAttributes AS i3 ON i.issue_id = i3.issue_id AND i3.attr_name = 'priority' LEFT OUTER JOIN IssueAttributes AS i4 ON i.issue_id = i4.issue_id AND i4.attr_name = 'description' WHERE i.issue_id = 1234;

针对以上这些问题，一般设计成EAV模式是想要数据库更加灵活，但是这种模式更使用NoSQL数据库，比如使用MongoDB，下一篇文章，我们将介绍针对这种如何设计关系型的解决方案。

 

 

总结

以上是生活随笔为你收集整理的SQL反模式：实体-属性-值（EAV)问题（一）的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。