实体-属性-值

目的

支持可变的属性

可扩展性是所有软件项目设计中最普遍的一个目标。我们都想设计出一个不需要过多的修改，甚至不需要修改就能适合将来需求变更的软件

反模式

使用泛型属性表

对于某些程序员来说，当他们需要支持可变属性时，首先想到的就是增加一个属性表，将属性当成行来存储。属性表中包含三列

实体：通常来说这就是一个指向父表的外键，父表的每条记录都表示一个实体对象
属性：在传统的表中，属性即每一列的名字，但在这个新的设计中，我们需要根据不同的记录来解析其标识的对象属性
值：对于每个实体的每个不同的属性，都有一个对应的值

比如：一个给定的bugs是一个实体对象，我们通过它的主键来标识它，它的主键为1234。这个对象有一个属性status ,bug1234的status的值为'NEW'

这样的设计叫做实体-属性-值，简称EAV。

EAV表ddl语句

CREATE TABLE Issues(
issue_id SERIAL PRIMARY  KEY ,
)

INSERT INTO Issues (issue_id) VALUES (1234)

CREATE TABLE IssuesAttributes(
issue_id BIGINT UNSIGNED NOT NULL,
attr_name VARCHAR(100) NOT NULL,
attr_value VARCHAR(100),
PRIMARY KEY(issue_id,attr_name),
FOREIGN KEY(issue_id) REFERENCES Issues(issue_id)
)

INSERT INTO  IssuesAttributes(issue_id,attr_name,attr_value)
VALUES 
(1234,'product','1'),
(1234,'data_reported','2022-01-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');

通过增加一张额外表好处有三点

两张表中的列都很少
新增的属性不会对现有的表结构造成影响，不需要新增列
避免了由于空值而造成的表内容混乱

这看上去是一个改良过的设计，然而，设计上的简单化并不足以弥补其造成的使用上的难度

查询属性难度

假设你的领导想要每天获取一份bug的报表，在传统的设计表中，我们只需要写如下SQL：

SELECT issue_id,date_reported FROM Issues

那么使用EAV的设计方式实现每天获取一份bug报表的SQL如下：

SELECT issue_id,attr_value AS 'date_reported' FROM IssueAttributes
WHERE attr_name = 'date_reported'

使用EAV的方式查询啰嗦，而且不够清晰

无法使用SQL的数据类型

在EAV设计中，IssueAttributes.attr_value列的数据类型就是一个单纯的字符串，从而才能仅用一列来适应任何可能的数据类型。因此，没有办法控制无效的数据类型录入如：

INSERT INTO IssueAttributes(issue_id,attr_name,attr_value) 
VALUES (1234,'date_reported','aaa') -- date_reported是日期 输入aaa也不会报错

有些人尝试扩展EAV的设计，为每一个SQL类型定义一个单独的attr_value列，不需要使用的列就直接留空。这可以让你使用SQL的数据类型，却使得查询变得更加恐怖。 SQL如下所示：

SELECT issue_id,
COALESCE(attr_value_date,attr_value_datetime,attr_value_integer,attr_value_numeric,
attr_value_float,attr_value_string,attr_value_text) AS 'date_reported'
FROM IssueAttributes
WHERE attr_name = 'date_reported'

你可能需要添加更多的列来支持用户自定义的数据类型

重组列问题

当数据是存储在一张传统的表中时，从 Issues 表中获取一整行记录，并得到一个议题的所有属性是个很平常的需求。

由于每个属性在 IssueAttributes 表里都存储在独立的行中，要想像上面那样按行获取所有这些属性就需要执行一个联结查询，并将结果合并成行。同时，必须在写查询语句的时候就知道所有的属性名称。下面的查询语句重组了上表展示的行：

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

你必须使用外联结来进行查询，因为如果在所查询的这些属性中有任何一个不在 IssueAttributes 表中出现，则内联结会导致整个查询返回空记录。随着属性的数量不断增多，联结的数量也不断增长，查询的开销也成指数级地增长。

如何识别反模式

如果你的项目团队中发出如下疑问，很有可能使用了EAV反模式

数据库不需要修改元数据就可以扩展。你还可以在运行时定义新的属性
查询时我能用的最大数量的联结是多少
使用EAV的设计来支持其强大的自定义的能力，而这使得很多普遍的报表查询变得极度复杂甚至不切实际

解决方案

模型化子类型

单表继承

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

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),
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
);

Issues表分别存储了bug和feature两种类型，部分列使用于bug或feature,通过issue_type来区分

实体表继承

单表继承这种方式存在不同类型使用不同列，这样设计有时候看起来不是很友好，那么就拆成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联合查询如下SQL所示

SELECT b.*,'bug' AS issue_type FROM Bugs AS b 
UNION ALL 
SELECT f.*,'feature' AS issue_type FROM FeatureRequests AS f

类表继承

第三个解决方案模拟了继承，把表当成面向对象里的类。创建一张基类表，包含所有子类型的公共属性。对于每一个子类型，创建一个独立的表，通过外键和基类表建立连接。这样做就是解决使用Union来做组合查询问题，

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,只要使用如下SQL语句

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

半结构化数据模型

如果你有很多子类型或者你必须经常地增加新的属性支持，那么可以使用一个 BLOB 列来存储数据，用 XML 或者 JSON 格式——同时包含了属性的名字和值。Martin Fowler 称这个模式为：序列化大对象块（Serialized LOB）。

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),
issue_type VARCHAR(10), -- BUG or FEATURE
attributes TEXT NOT NULL,
FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

这个设计的优势之处就在于其优异的扩展性。你可以在任何时候，将新的属性添加到 blob字段中。每行存储一个完整的属性集合，因此你可以有尽可能多的子类型，有多少行就可以有多少个。

相应地，该设计的缺点就是在这样的一个结构中，SQL 基本上没有办法获取某个指定的属性。你不能在一行 blob 字段中简单地选择一个独立的属性，并对其进行限制、聚合运算、排序等其他操作。你必须获取整个 blob 字段结构并通过程序去解码并且解释这些属性。

当你不能将需求和设计限制在一个有限的子类型集合中，或者当你需要绝对的灵活性以在任何时间调整属性时，这个方案就是最佳选择。

06.EAV