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

在上一篇文章中，介绍了EAV模式存在的问题，文章链接如下：

https://blog.youkuaiyun.com/dh858115/article/details/104203110

那么针对EAV的问题有什么解决方案呢？

解决方案1.单表法

   将所有的属性编程列的方式存储起来，这样可以通过列的类型限制不合法的数据，如创建以下表

CREATE TABLE Issues1 (
issue_id int PRIMARY KEY,
reported_by BIGINT NOT NULL,
product_id BIGINT,
priority VARCHAR(20),
version_resolved VARCHAR(20),
status VARCHAR(20),
issue_type VARCHAR(10), -- BUG or FEATURE
severity VARCHAR(20), -- only for bugs
version_affected VARCHAR(20), -- only for bugs
sponsor VARCHAR(50), -- only for feature requests
);

Issue1表分别存储了bug和feature两种类型，部分列进适用于bug或者feature，通过Issue_type来区分。

解决方案2.混合表

方案1中存在不同类型使用不同列，这样的设计有时候看起来不是很好，那么可以将他们拆分成2个表，具体如下：

CREATE TABLE Bugs (
issue_id int PRIMARY KEY,
reported_by BIGINT NOT NULL,
product_id BIGINT ,
priority VARCHAR(20),
version_resolved VARCHAR(20),
status VARCHAR(20),
severity VARCHAR(20), -- only for bugs
version_affected VARCHAR(20) -- only for bugs
);
CREATE TABLE FeatureRequests (
issue_id int PRIMARY KEY,
reported_by BIGINT NOT NULL,
product_id BIGINT ,
priority VARCHAR(20),
version_resolved VARCHAR(20),
status VARCHAR(20),
sponsor VARCHAR(50) -- only for feature requests
);

上面拆分成2个表的好处在于，不会将属于bug列的内容不小心插入到feature所属的列。而且不需要维护Issue_type列，以区分bug或者feature。但是如果想查询bug和feature时，就需要使用union来联合结果了。

解决方案3.继承表

为了解决上面使用union来做组合查询的问题，我们可以设计一个issue表，然后bug和feature继承自issue，具体SQL如下：

CREATE TABLE Issues (
issue_id int PRIMARY KEY,
reported_by BIGINT NOT NULL,
product_id BIGINT,
priority VARCHAR(20),
version_resolved VARCHAR(20),
status VARCHAR(20)
);
CREATE TABLE Bugs (
issue_id BIGINT PRIMARY KEY,
severity VARCHAR(20),
version_affected VARCHAR(20)
);
CREATE TABLE FeatureRequests (
issue_id BIGINT PRIMARY KEY,
sponsor VARCHAR(50)
);

通过issue_id可以关联到父类或者子类，这时，如果需要查询bug和feature，只要使用如下语句即可，比union优雅

SELECT i.*, b.*, f.*
FROM Issues AS i
LEFT OUTER JOIN Bugs AS b on i.issue_id = b.issue_id
LEFT OUTER JOIN FeatureRequests AS f on i.issue_id = f.issue_id

解决方案4.半结构化数据

如果在你的设计中，需要很多动态的attribute，那么建议你使用xml，JSON或者大对象等格式，这些数据格式允许你做动态的扩展，同时其他列的数据校验依然有效，同时兼具灵活扩展性。