如何在列上存储元数据

Question

假设你正在收集有关即将上映的超级英雄电影的内幕信息，你的主要电影表看起来像这样：

表1

Title              Director   Leading Male      Leading Female    Villain
--------------------------------------------------------------------------
Green Lantern      Kubrick    Robert Redford     Miley Cyrus     Hugh Grant  
The Tick          Mel Gibson  Kevin Sorbo        Linda Hunt    Anthony Hopkins

这通常可以很好地运行，并且允许非常容易的查询以及行之间的比较。

但是，您希望跟踪每个数据事实的来源，以及发现该事实的记者的姓名。这似乎建议使用类似于EAV表格：

表2

Movie             Attribute            Value          Source          Journalist
----------------------------------------------------------------------------------
Green Lantern      Director           Kubrick         CHUD              Sarah
Green Lantern    Leading Male      Robert Redford     CHUD              James
Green Lantern   Leading Female      Miley Cyrus    Dark Horizons        James
Green Lantern      Villain           Hugh Grant       CHUD              Sarah
The Tick           Director          Mel Gibson       Yahoo            Cameron
...

虽然EAV表格轻松地捕获了我们想要的元数据，但使查询变得更加困难。要获取单个电影的所有基本数据需要更多的工作。具体来说，您必须在这里处理四行内容，以获取绿灯侠的四个重要信��片段；而在表1中，这是一行整齐封装的数据。

因此，我的问题是，在我刚才描述的复杂性的背景下，并且因为我知道通常应避免使用EAV表格，EAV是否仍然是最佳解决方案？看起来它似乎是唯一合理的表示此数据的方式。我看到的唯一其他替代方法是结合另一个只包含此类元数据的表1： 表3

Movie             Attribute            Source          Journalist
----------------------------------------------------------------------------------
Green Lantern      Director             CHUD              Sarah
Green Lantern    Leading Male           CHUD              James
Green Lantern   Leading Female      Dark Horizons         James
Green Lantern      Villain              CHUD              Sarah
The Tick           Director             Yahoo            Cameron
...

但这样做非常危险，因为如果有人更改了表1中的列名，比如将“Villain”更改为“Primary Villain”，则表3中的行仍然只会显示“Villain”，因此相关数据将不幸分离。如果“属性”列与另一个表链接，则可以帮助解决此问题，该表作为表1的列的枚举。当然，数据库管理员将负责维护此枚举表以匹配表1的实际列。甚至可能通过使用SQL Server中的系统视图而不是手动创建枚举表来进一步改善情况。虽然我不确定是否可以涉及系统视图的关系。

你有什么建议？ EAV是唯一的方法吗？

如果只有一个元数据列（只有“来源”没有“记者”），那怎么办？是否仍然需要采用EAV路线？您可以拥有“导演”、“导演_来源”、“男主角”、“男主角_来源”等列，但这很快变得混乱。是否有一些更好的解决方案我没有想到？

如果我没有澄清任何问题，请在评论中提出，我会根据需要添加更多信息。哦，对了，我使用的电影数据是虚构的：）

编辑：为了简明起见，我想拥有表1的简单性和真正的RDBMS设计，这真正描述了电影条目，同时仍以安全且可访问的方式存储属性的元数据。这可能吗？还是EAV是唯一的方法？

编辑2：在进行了更多网络研究之后，我尚未找到关于EAV的讨论集中在希望在列上存储元数据的愿望的情况。实施EAV的主要原因几乎总是动态和不可预测的列，而这在我的示例中并不是这种情况。在我的示例中，始终存在相同的四个列：导演、男主角、女主角和反派。但是，我想存储每个行的每个列的某些事实（来源和记者）。 EAV会促进这一点，但我想避免采用这种方法。

更新

在保持Table 2设计的情况下，只需将“Movie”列重命名为“Name”，并将整个表称为“Movie”，这里是在SQL Server 2008中执行透视操作以恢复Table 1：

SELECT Name, [Director], [Leading Male], [Leading Female], [Villain]
FROM (Select Name, Attribute, Value FROM Movie) as src
PIVOT
(
Max(Value)
FOR Attribute IN ([Director], [Leading Male], [Leading Female], [Villain])
)  AS PivotTable

- JoeCool

---

关于“有争议的”事实或随着时间变化的事实，您需要记住Joe认为反派可能是Able，但Sam认为可能是Baker吗？还是最终电影上映，真正的恶棍是Clarice？ - RBerteig

不要牵扯任何历史或日志记录到方程式中。系统只需要为每个电影属性存储源和记者的元数据的单个副本。 - JoeCool

Answer 1

您可以更改设计中认为是事实值的内容… 在您的数据模型中，似乎可以将事实表示为以下 N 元组：

Movie | FactType | FactValue | FactSource | FactJournalist

以下表结构应支持您想要的数据模型，并且可以相对容易地进行索引和连接。您还可以创建一个视图，将事实值和事实类型旋转出来，以便创建以下视角：

MovieID | Movie Name | Director | LeadingMale | LeadingFemale | PrimaryVillain | etc

有趣的是，您可以将它视为完全将EAV模型应用于数据并将单个电影（具有直观的导演、主角、反派等属性）分解为一个以信息来源为重点的透视结构的逻辑扩展。

所提议的数据模型的好处包括：

- 它已经规范化（尽管为了完整性，您可能应该将FactType字段规范化为引用表） - 可以创建一个将事实类型有效地转换为表格结构的视图 - 它相对可扩展，并允许数据库强制执行引用完整性和（如果需要）基数约束 - MovieFact表可以被子类化以支持不仅仅是简单文本字段的不同种类的电影事实 - 对数据的简单查询相对高效

一些数据模型的缺点包括：

- 组合的条件查询更难编写（但不是不可能）（例如查找所有导演是A且领衔男主角是B等等的电影） - 该模型比传统方法或涉及EAV结构的更明显 - 插入和更新有点棘手，因为更新多个事实需要更新多行，而不是多个列

我将电影数据升级到规范化结构，并为了一致性将电影名称推向MovieFact结构中（因为对于某些电影，我可以想象名称本身就是您可能要跟踪源信息的内容）。

Table Movie
========================
MovieID   NUMBER, PrimaryKey
MovieName VARCHAR

Table MovieFact
========================
MovieID          NUMBER,  PrimaryKeyCol1
FactType         VARCHAR, PrimaryKeyCol2
FactValue        VARCHAR
FactSource       VARCHAR
FactJournalist   VARCHAR

您的虚构电影数据将如下所示：

：

Movie Table
====================================================================================
MovieID  MovieName
====================================================================================
1        Green Lantern
2        The Tick

MovieFact Table
====================================================================================
MovieID  FactType       FactValue         FactSource       FactJournalist
====================================================================================
1        Director       Kubrick           CHUD             Sarah
1        Leading Male   Robert Redford    CHUD             James
1        Leading Female Miley Cyrus       Dark Horizons    James
1        Villain        Hugh Grant        CHUD             Sarah
2        Director       Mel Gibson        Yahoo            Cameron
2        Leading Male   John Lambert      Yahoo            Erica
...

- LBushkin

---

+1 - 恰好是我所思考的。在这种情况下最好不要妥协于标准化。值得强调的是，视图可以使得查询变得容易。此外，当列名需要更改时，可以创建新视图，这意味着您不会破坏现有的接口。 - Chris Simpson

非常感谢您提供如此深入的回复。这确实是我公司决定采用的形式（至少在他们改变主意之前哈哈）。我们之所以决定采用它，是因为最终我们将一部电影的事实视为一种一级实体，因为有记者和来源，所以我们应该在数据库中以这种方式对其进行建模。虽然失去了更明显的电影表示方式（尽管正如您所说，我们可以旋转它），但这样做是最好的。 - JoeCool

我知道这是一个相当老的答案，但我想知道如果factvalue不是字符串而是其他类型的数据，该怎么扩展。比如说，发行日期、总收益或首周末影院数量。 - AlvaroFG

Answer 2

这里有另一个想法...请随意提出质疑 :)

Table: Movie
Columns: MovieId|Movie|Director|LeadMale|LeadFemale|Villain

Table: MovieSource
Columns: MovieSourceId|MovieId|MovieRoleId|Source|Journalist

Table: MovieRole
Columns: MovieRoleId|MovieRole
Values: 1|Director, 2|LeadMale, 3|LeadFemale, 4|Villain

我认为电影表中的列可以是不同类型的（例如，它们都是字符串/变字符，但它们也可以是数字或日期信息，也有一个来源）。

然而，源数据的列类型可能不会因为电影数据的列类型而有所不同，因此您可以在不失去数据完整性的情况下使用更多的EAV系统来处理源数据。

MovieRole表允许您明确枚举角色，以便您可以创建源和电影表中给定单元格之间的确定链接。

-丹

- devuxer

---

额外的想法：如果您将“Villain”列重命名为“PrimaryVillain”，它不会损害链接（因为它们是通过id而不是名称链接的）。如果您向电影表添加一列，则需要向MovieRole添加记录，但不会破坏任何现有数据（我认为很快就会注意到）。如果您从电影表中删除列，则当然会丢失该列的数据，但除非您删除MovieRole（并级联删除），否则不会删除该数据的来源。 - devuxer

我喜欢这个解决方案，甚至在你发布之前我就一直在考虑它。如果你将“MovieRole”简单地称为“MovieAttributes”，那么你可以泛化你的方法，现在表格只是电影表的所有列（除了标识列）的枚举。 - JoeCool

Answer 3

鉴于您只有源数据（来源和记者）的两个字段，我建议使用类似于以下元数据表：

Movie    DirectorSource  DirectorJournalist  LeadingMaleSource  LeadingMaleJournalist ...
---------------------------------------------------------------------------------------
The Tick   Yahoo           Cameron           ...                ...

这将使不太重要的源数据保持在主表之外，但查询不会变得复杂，您的代码将更易读。

我只会建议使用EAV如果...

• 您有超过3个源元数据字段
• 您需要能够轻松添加或更改电影字段。(例如，“反派”更改为“主要反派”每天进行多次)

- too much php

Answer 4

有趣的情境。您可以将实体视为一级对象来避免 EAV 的复杂性；让我们称之为“事实”。在这种情况下，每个电影都有完全相同的四个事实，因此您在这方面非常正交。您的 EAV 表格可以是您的原始/正确表格，然后您可以有一个外部进程来挖掘该表格并将数据复制到适当归一化的形式中（即您的第一个表格）。这样，您就拥有所需的数据及其元数据，并且可以轻松查询电影信息，准确到挖掘过程运行的频率。

我认为您肯定需要一些“数据库外”的支持来确保数据保持有效，因为似乎没有任何在数据库中维护常规和 EAV 表之间完整性的方法。我想通过一系列复杂的触发器，您几乎可以实现任何事情，但是一个理解您问题的人类管理员可能更容易处理。

- Mark Canlas

Answer 5

我的回答可能对于SO来说有点过于哲学化。请耐心等待。

我认为“源”列不是主题数据，而是元数据。它实际上是关于我们如何了解其他数据的数据。这使它成为关于数据的数据，也就是元数据。

EAV引起问题的原因之一是它将数据和元数据混合在单个行中。有时我会故意这样做，作为达到想要的结果的中间步骤。但我尽量不在我的交付成果中混合数据和元数据。

我知道我从未这样做的原因，但我无法简洁地解释它。

- Walter Mitty

Answer 6

由于没有其他人真正尝试过，我将回答自己的问题。我非常确定类似EAV的表确实是唯一可行的方法。为了在每个列上存储元数据（关于来源和记者），您实际上是将每个列视为一个实体，这就是EAV允许的。

您可以选择其他路线，例如为每个原始列添加第二列和第三列以存储数据，但这绝对违反了一些基本的规范化规则，并且可能只会在以后给您带来痛苦。

- JoeCool

Answer 7

嗯……我没用过这个，所以我说的不是基于经验的（也就是说如果它不能工作，请不要责怪我），但从外表看来，你可以像在普通表中一样存储你知道始终存在的“常见”数据，并将可能会更改的“元数据”存储为 XML。然后问题就是如何漂亮地查询它，我认为你可以按照此处描述的方法来完成。

- Michael Bray

Answer 8

另一个需要考虑的方法是类表继承。Bill Karwin在this SO answer中对EAV选项进行了很好的评估，并提供了许多有用的背景信息。

- Antony

---

我很感谢提供的链接，但我不确定如何在这里实现类表继承。你提供的问题示例涉及处理通用属性与特定属性以及每个属性所属的位置，而类表继承是其中一种解决方案。然而，我的问题涉及通用属性与描述这些属性的属性（元数据）。因此，基于我在原始问题中提到的原因，我认为我的情况非常不同。 - JoeCool

抱歉 - 第一次应该留下更长的评论。我认为继承表将处理元数据。例如，t_MetaData具有指向主表的FK，一个metaType列（领先男性，领先女性等），一个来源和一个记者列。 - Antony

是的，你可以那样做。我认为你所描述的是我的“表3”方法，但这种方法会引入一个新的问题。metaType列的值需要受到Movie中描述符列名称的限制。因此，现在你必须确保如果Movie中的描述符列名称或数量发生任何变化，这种变化会传播到t_MetaData中的行。你可以通过一些代码来确保这种效果，但它让我感到有些不安 :) - JoeCool

Answer 9

我会根据我需要编写的代码来做出决定。

如果src/journo只是额外信息，我会选择更多的列。但如果我知道我最终要构建复杂的src/journo查询，我会选择EAV，因为在元表中搜索记者的引用比进入LeadingFemaleJournalist和VillainJournalist等更容易。

个人而言，我倾向于将src/journo元数据以EAV方式转储到另一个表中，但使用FK来定义属性定义表。自由格式的属性文本字段是灾难的源头 - 通过约束始终控制您的属性。如果需要，可以实现触发器以改善引用完整性。

对我来说，这取决于观点。你是否认为来源和记者本身就是关系问题，还是只是补充电影的额外数据？下一级的细化将是创建不同的表格用于MovieDataSource和MovieDataJournalist，这可以让你将FK映射到定义有效来源和记者（并且可以进一步详细说明这些来源/记者的信息）的表格中。你所做的是在电影实体和来源（以及记者）实体之间建立多对多的关系。

- Joel Goodwin