主页 / dba / 问题 / 20759
Accepted
prototype
Asked: 2012-07-12 18:41:17 +0800 CST

这个键值数据库模式有名称吗？

  • 772

我们处理来自客户的例行数据馈送，该客户刚刚将其数据库从看起来熟悉的形式（每个实体一行，每个属性一列）重构为我不熟悉的形式（每个实体每个属性一行）：

之前：每个属性一列

ID   Ht_cm   wt_kg   Age_yr  ... 
1      190      82     43    ...
2      170      60     22    ...
3      205      90     51    ...

之后：所有属性的一列

ID    Metric   Value
 1     Ht_cm     190
 1     Wt_kg     82
 1     Age_yr    43
 1      ...
 2     Ht_cm     170
 2     Wt_kg     60
 2     Age_yr    22
 2     ...
 3     Ht_cm     205
 3     Wt_kg     90
 3     Age_yr    51
 3     ...

这个数据库结构有名字吗？相对优势是什么？旧方法似乎更容易对特定属性（非空、非负等）设置有效性约束，并且更容易计算平均值。但是我可以看到在不重构数据库的情况下添加新属性可能会更容易。这是结构化数据的标准/首选方式吗？

database-design schema

5 个回答

  1. Best Answer

    它被称为实体-属性-值（有时也称为“名称-值对”），当人们在关系数据库中使用 EAV 模式时，它是“方孔中的圆钉”的经典案例。

    以下是您不应该使用 EAV 的原因列表：

    • 您不能使用数据类型。值是日期、数字还是货币（十进制）都没有关系。它总是会被强制转换为 varchar。这可能是从轻微的性能问题到严重的肠痛（曾经不得不在每月汇总报告中追踪一美分的变化？）。
    • 您不能（轻松）强制执行约束。它需要大量的代码来强制执行“每个人的身高都必须在 0 到 3 米之间”或“年龄不得为空且 >= 0”，而不是每个约束都需要 1-2 行在适当建模的系统中。
    • 与上述相关，您不能轻易保证您获得每个客户所需的信息（其中一个可能缺少年龄，然后下一个可能缺少他们的身高等）。你可以做到，但它比SELECT height, weight, age FROM Client where height is null or weight is null.
    • 再次相关，重复数据更难检测（如果他们给你一个客户的两个年龄会发生什么？如下所示，如果你有一个属性加倍，则对数据进行去 EAV 会给你两行结果。如果一个客户对于两个属性有两个单独的条目，您将从下面的查询中获得四行）。
    • 您甚至不能保证属性名称是一致的。“Age_yr”可能会变成“AGE_IN_YEARS”或“age”。（诚​​然，当您收到数据提取与人们插入数据时相比，这不是问题，但仍然如此。）
    • 任何类型的非平凡查询都是一场彻底的灾难。要将三属性 EAV 系统关系化，以便您可以以合理的方式查询它，需要 EAV 表的三个连接。

    相比：

    SELECT cID.ID AS [ID], cH.Value AS [Height], cW.Value AS [Weight], cA.Value AS [Age]
FROM (SELECT DISTINCT ID FROM Client) cID 
      LEFT OUTER JOIN 
    Client cW ON cID.ID = cW.ID AND cW.Metric = "Wt_kg" 
      LEFT OUTER JOIN 
    Client cH ON cID.ID = cH.ID AND cW.Metric = "Ht_cm" 
      LEFT OUTER JOIN 
    Client cA ON cID.ID = cA.ID AND cW.Metric = "Age_yr"

    至：

    SELECT c.ID, c.Ht_cm, c.Wt_kg, c.Age_yr
FROM Client c

    这是您应该使用 EAV 的时间的（非常短的）列表：

    • 当绝对没有办法解决它并且您必须在数据库中支持无模式数据时。
    • 当您只需要存储“东西”并且不希望以更结构化的形式需要它时。不过要小心，这个怪物叫做“不断变化的要求”。

    我知道我刚刚花了整篇文章详细说明了为什么在大多数情况下 EAV 是一个糟糕的主意——但在少数情况下它是必要的/不可避免的。然而，大多数时候（包括上面的例子），它会比它的价值要麻烦得多。如果您需要广泛支持 EAV 类型的数据输入，您应该考虑将它们存储在键值系统中，例如 Hadoop/HBase、CouchDB、MongoDB、Cassandra、BerkeleyDB。

    • 98

  2. 实体属性值(EAV)

    包括我在内的许多人都认为它是一种反模式。

    以下是您的替代方案：

    1. 使用数据库表继承

    2. 使用 XML 数据和SQLXML 函数

    3. 使用 nosql 数据库，例如 HBase

    • 19

  3. 在 PostgreSQL 中，处理 EAV 结构的一种非常好的方法是附加模块hstore，可用于 8.4 或更高版本。手册：

    该模块实现了hstore用于在单个 PostgreSQL 值中存储键/值对集的数据类型。这在各种场景中都很有用，例如具有许多很少检查的属性的行或半结构化数据。键和值只是文本字符串。

    需要额外的模块 hstore。看：

    从 Postgres 9.2 开始，它也有json类型和许多功能（其中大部分是在 9.3 中添加的）。

    Postgres 9.4 增加了（很大程度上优越的）“二进制 JSON”数据类型jsonb。具有高级索引选项。

    • 17

  4. 有趣的是，EAV db 模型是如何受到批评甚至被某些人视为“反模式”的。

    就我而言，主要缺点是：

    • 如果您从事一个不久前已经开始使用 EAV 的项目，学习曲线会更加陡峭。确实，查询很困难，因为您大大增加了连接（和表）的数量，因此您需要更多的时间来理解。看看 Magento 项目，看看项目外部的开发人员如何在数据库上工作困难重重，但文档得到了很好的维护。
    • 不适合报告，如果您需要获取姓名以“M”开头的人数等...

    但是，您绝对不应该放弃此解决方案，原因如下：

    • 西蒙谈到了被称为“不断变化的要求”的怪物。我喜欢这个表达:)。恕我直言，这正是 EAV 可能是一个很好的候选者的原因，因为这非常适合“更改”，因为您可以轻松地添加任意数量的属性。当然，这取决于我们正在改变的要求。如果我们谈论的是一个全新的业务，当然你必须检查你的数据模型，但是 EAV 提供了很大的灵活性。仅仅因为它要求更严格，并不意味着这不那么有趣。
    • 也有人说“你不能使用数据类型”。:这是错误的。您可能有多个值表，每个数据类型一个。然后，您必须在属性表中指定哪种数据类型是您的属性。事实上，经典关系/EAV 与类关系的混合在数据库设计中提供了很多有趣的潜力。
    • 11

  5. 如果您有一个使用 EAV 结构的数据库，则可以通过多种方式查询数据。

    @Simon 的回答已经展示了如何使用多个连接执行查询。

    使用的样本数据：

    CREATE TABLE yourtable ([ID] int, [Metric] varchar(6), [Value] int);

INSERT INTO yourtable ([ID], [Metric], [Value])
VALUES (1, 'Ht_cm', 190),
    (1, 'Wt_kg', 82),
    (1, 'Age_yr', 43),
    (2, 'Ht_cm', 170),
    (2, 'Wt_kg', 60),
    (2, 'Age_yr', 22),
    (3, 'Ht_cm', 205),
    (3, 'Wt_kg', 90),
    (3, 'Age_yr', 51);

    如果您使用的是具有PIVOT功能的 RDBMS（SQL Server 2005+ / Oracle 11g+），那么您可以通过以下方式查询数据：

    select id, Ht_cm, Wt_kg, Age_yr
from
(
  select id, metric, value
  from yourtable
) src
pivot
(
  max(value)
  for metric in (Ht_cm, Wt_kg, Age_yr)
) piv;

    请参阅带有演示的 SQL Fiddle

    如果您无权访问PIVOT函数，则可以使用带有CASE语句的聚合函数来返回数据：

    select id,
  max(case when metric ='Ht_cm' then value else null end) Ht_cm,
  max(case when metric ='Wt_kg' then value else null end) Wt_kg,
  max(case when metric ='Age_yr' then value else null end) Age_yr
from yourtable
group by id

    请参阅带有演示的 SQL Fiddle

    这两个查询都将在结果中返回数据：

    | ID | HT_CM | WT_KG | AGE_YR |
-------------------------------
|  1 |   190 |    82 |     43 |
|  2 |   170 |    60 |     22 |
|  3 |   205 |    90 |     51 |
    • 10

相关问题