数据库设计：EAV 选择？
这只是一个数据库概念问题：以下 EAV 模型的优缺点是什么？
模型 1：
TABLE: attribute_value
======================================
| id | fk_id | attribute | value |
======================================
| 1 | 10 | FName | John |
| 2 | 10 | Lname | Doe |
| 3 | 55 | FName | Bob |
| 4 | 55 | Lname | Smith |
--------------------------------------
模型 2：
TABLE: attribute
==================
| id | attribute |
==================
| 1 | FName |
| 2 | Lname |
------------------
TABLE: value
=====================================
| id | attribute_id | fk_id | value |
=====================================
| 1 | 1 | 10 | John |
| 2 | 2 | 10 | Doe |
| 3 | 1 | 55 | Bob |
| 4 | 2 | 55 | Smith |
-------------------------------------
我认为模型 2 的一个好处是属性不包含重复项。
This is just a database concept question: what are the pros and cons of the following model for EAV?
Model 1:
TABLE: attribute_value
======================================
| id | fk_id | attribute | value |
======================================
| 1 | 10 | FName | John |
| 2 | 10 | Lname | Doe |
| 3 | 55 | FName | Bob |
| 4 | 55 | Lname | Smith |
--------------------------------------
Model 2:
TABLE: attribute
==================
| id | attribute |
==================
| 1 | FName |
| 2 | Lname |
------------------
TABLE: value
=====================================
| id | attribute_id | fk_id | value |
=====================================
| 1 | 1 | 10 | John |
| 2 | 2 | 10 | Doe |
| 3 | 1 | 55 | Bob |
| 4 | 2 | 55 | Smith |
-------------------------------------
One benefit I see with Model 2 is that the attribute does not contain duplicates.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
尽管如图所示是简约的，但 Model2 的属性表将元数据的概念引入到组合中，并带来了它带来的所有好处。 Model2 还有其他优点，例如与较小的行大小（值表）相关的性能提升，但我想重点关注元数据概念。
即使模型 2 的属性表也构成了所有有效属性的存储库（对于模型 1，需要运行各种聚合查询才能获得这样的列表）。此外，按原样，存储库足以引入外键约束，以帮助维护数据集的完整性（使用模型 1，需要对数据集进行外部形式的验证） 通过一些简单的添加
，属性表可以成为可用于各种目的的通用存储库，例如该表可能包含以下一些
简而言之，属性表成为一种资源，使应用程序能够真正由数据驱动（或更准确地说，元数据驱动）。事实上，您可能还喜欢一个实体表，即收集与各种实体类型相关的元数据的表：哪些是不同的实体类型，哪些属性允许哪些实体类型等。
现在...请注意来自的评论zerkms，位于问题本身下方。尽管有这么多优点，EAV 模型也有其缺点和挑战，正如所暗示的查询的复杂性以及性能问题。然而，这些问题不应先验地取消 EAV 的资格：在许多用例中，EAV 是更好的方法。
假设 EAV 是模型 2 的选择，甚至稍微复杂一点的东西也绝对优于模型 1。
Although minimalist as shown, the attribute table of Model2 introduces the concept of meta-data into the mix, with all the good that comes from it. There are other advantages to Model2, for example the performance gains associated with smaller row size (of the Value table), but I'd like to focus on the meta-data concept.
Even as-is Model2's attribute table constitute a repository of all valid attributes (with model1 one would need to run an aggregate query of sorts to get such a list). Also, and as-is, the repository is sufficient to introduce foreign key constraints to help maintaining the integrity of the dataset (with Model 1 one would need external forms of validation of the values stored in attribute column.
With a few simple additions, the attribute table can become a versatile repository which can be used for various purposes. For example the table may include some of the following
In a nutshell, the attribute table becomes a resource which allows the application to be truly data-driven (or more precisely, meta data driven). Indeed you may also like an entity table i.e. one where the metadata pertaining to the various entities types are gathered: which are the different entity types, which attributes are allowed for which entity type etc.
Now... do pay heed to the comment from zerkms, below the question itself. For all its benefits, the EAV model also comes with its share of drawbacks and challenges, as hinted the complexity of the queries come to mind, and also performance issues. These concerns should however not disqualify, a priori, EAV: there are many use cases where EAV is a better approach.
Assuming EAV is the choice then Model2, or even something slighly more sophisticated is definitively superior to model1.
在概念层面上，这两个模型实际上是相同的。您刚刚将字符串替换为 ID 号。就这样。
就外键而言，如果您愿意，可以对模型 1 中的“属性”施加外键约束。
就优缺点而言，EAV 的这两种实现之间实际上没有区别。所有Bill Karwin 的观点都适用于两者。
At the conceptual level, these two models are virtually identical. You've just replaced strings with ID numbers. That's all.
As far as foreign keys go, you could impose a foreign key constraint on "attribute" in Model 1 if you wanted to.
As far as pros and cons go, there's really no difference between these two implementations of EAV. All Bill Karwin's points apply to both.
对于模型 2，您可以在 attribute_id 上施加外键，并确保只有定义的属性才能进入表。
同样对于模型 2，您可以更快地查询来获取具有某些属性 id 的值，因为如果您创建外键（索引），查询将会更快。
For Model 2, you can impose a Foreign-Key on attribute_id and make sure that the only defined attributes can enter the table.
Also for Model 2, you can have faster queries to get values with certain attributes ids since if you make a foreign-key (index), querying will be faster.