Entity-Attribute-Value Model
对象 - 属性 - 值模型（Entity-Attribute-Value Model, EAV Model），也叫开放架构（Open Schema）、垂直数据库模型（Vertical Database Model），是一种针对属性、值分布稀疏（sparse）或特别的数据模型，专为空间高效存储而优化的，运行时数据模型是任意的、受用户变化影响。
该模型适用于不同实体的属性、值数量庞大且未知，但每一个实体的属性、值较少（也就是稀疏属性 [1]）。
因为表字段数量一定，相同的字段可能塞入不同含义的值，所以叫开放架构；而这种架构往往造成数据库列很少，但行很多，所以也叫垂直数据库模型。
例子
下面展示一个使用 EAV 模型 的临床数据库（Clinical database）表例子：
| Entity | Atrribute | Value |
|---|---|---|
| ⟨Patient XYZ, 1998-05-01 09:30⟩ | ⟨Temperature in degrees Celsius⟩ | "38.9" |
| ⟨Patient XYZ, 1998-05-01 09:30⟩ | ⟨Presence of cough⟩ | "True" |
| ⟨Patient ABC, 1998-05-01 09:30⟩ | ⟨Type of Cough⟩ | "With phlegm, yellowish, streaks of blood" |
| ⟨Patient ABC, 1998-05-01 09:30⟩ | ⟨Heart rate in beats per minute⟩ | "98" |
- 实体就是病患事件，包含病人的 id（外键，指向病人表）、事件时间
- 属性可能包含一系列临床发现，一般而言会使用临床发现表的外键，这里简化直接使用字符串
- 值就是对应属性的发现结果
EAV 模型的优点
EAV 模型的缺点
- 复杂，难以上手
- 数据质量难以保证，需要查询时松散验证
- 性能差，但采用大宽表可以优化
- 无法对相关实体分组
- 不同实体子类型之间没有联系
- 无法使用 ORM 框架
An Entity–attribute–value model (EAV) is a data model optimized for the space-efficient storage of sparse—or ad-hoc—property or data values, intended for situations where runtime usage patterns are arbitrary, subject to user variation, or otherwise unforseeable using a fixed design. The use-case targets applications which offer a large or rich system of defined property types, which are in turn appropriate to a wide set of entities, but where typically only a small, specific selection of these are instantated (or persisted) for a given entity. Therefore, this type of data model relates to the mathematical notion of a sparse matrix.[2]
EAV database model, as well known as ‘open schema’ or ‘vertical model’, describe entities where the number of all attributes in unknown and potentially significant, however number of attributes for a particular entity is small.[3]
EAV Advantages:
- Flexible mechanism, supports multiple projects, broad community.
- Little consideration of DB structure at the initial design stage.
- Database schema does not change, when model changes, allowing updates through for example web UI.[3:1]
EAV Disadvantages:
- Complexity - a high learning curve for new developers.
- Loose data validation that needs to be implemented later in the code.
- Performance - multiple join queries (though good cache system can improve it).
- Does not provide a way for grouping of related entity types.
- Does not provide a mechanism to create relationships between entities of different subtypes.
- Developers have to recreate relational database technology (graphical system tools, data security, incremental back-up and restore, exception handling, etc.).
- Often you cannot just use ORM, but write complex SQL queries.[3:2]