数据库设计：EAV选项？ [英] Database design: EAV options?

问题描述

这只是一个数据库概念问题：EAV的以下模型的优缺点是什么？

This is just a database concept question: what are the pros and cons of the following model for EAV?

模型1： / p>

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     |
--------------------------------------

模型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的一个好处是属性不包含重复。 / p>

One benefit I see with Model 2 is that the attribute does not contain duplicates.

推荐答案

虽然如图所示的简约，Model2的属性表将元数据混合，所有的好，来自它。 Model2还有其他优点，例如与较小的行大小（Value表）相关联的性能提升，但我想关注元数据概念。

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.

即使按原样 Model2的属性表构成所有有效属性的存储库（使用model1，需要运行一个聚合查询排序得到这样的列表）。此外，按照原样，存储库足以引入外键约束来帮助维护数据集的完整性（使用模型1，需要外部形式的验证

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

• 信息，例如每个属性的显示友好名称


• 指示字段类型（数字对字符串对日期等），用于区别处理/处理


• 存储基础属性的特定值表（模型只显示一个表，优化/缩放有时会提示拆分表）


• 该属性可以作为自己的列存储在值表中（同样是一种优化形式，两个世界：EAV模型的模式的灵活性，但是对于所有实体中最常用和/或最常见的属性的传统关系模型的性能。


• 重命名属性，而不干扰主表。仅限元数据级别的更改。


• 各种面向应用的语义。例如，应该将特定属性作为基本搜索字段或高级搜索字段之一提供的指示符。

• info such as the display-friendly name of each attribute
• some flags indicating the type of field (numeric vs. string vs. date etc.), for differentiated handling / processing
• the particular Value table where the underlying attribute is stored (Model only shows one table but optimization/scaling sometimes prompts splitting the tables)
• the fact that the attribute may be stored as its own column in the "Value" table (again a form of optimization, essentially getting the best of both worlds: the flexibility of the schema of the EAV model but the performance of traditional relational model for the attributes that are the most used and/or the most common to all entities.
• the ability to rename attributes, without disturbing the main table. Changes at meta-data level only.
• various application-oriented semantics. For example indicators that a particular attribute should be offered as one of the basic vs. advanced search fields.

简而言之，属性表变为允许应用程序真正地被数据驱动（或更精确地，元数据驱动）的资源。实际上，您可能还喜欢一个实体表，即其中收集与各种实体类型相关的元数据：这是不同的实体类型，允许哪些实体类型等属性。

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.

现在...请注意问题本身下方来自 zerkms 的评论。为了所有的好处，EAV模型也有它的缺点和挑战的份额，暗示了查询的复杂性，以及性能问题。然而，这些问题不应该取消资格，先验的EAV：有许多使用情况，EAV是一个更好的方法。
假设EAV是选择，那么Model2或者甚至更复杂的东西明确优于model1。

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.

这篇关于数据库设计：EAV选项？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！