如何在列上存储元数据

假设您正在收集有关即将上映的超级英雄电影的内部信息，并且您的主电影表如下所示：

Table 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像这样的表：

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

虽然它可以轻松捕获我们想要的元数据，但会使查询变得更加困难。简单地获取一部电影的所有基本数据需要更多的时间。更具体地说，您必须在这里处理四行才能获取有关 Green Lantern 的四个重要信息，而在表 1 中它是一个单独的、封装良好的行。

所以我的问题是，鉴于我刚才描述的复杂性，并且因为我知道通常要避免 EAV 表，EAV 仍然是最佳解决方案吗？这似乎是表示这些数据的唯一合理的方式。我看到的唯一的其他替代方案是将表 1 与另一个仅包含如下元数据的表结合使用：

Table 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 的列的枚举，这可能会有所帮助。当然，DBA 将负责维护此枚举表以匹配表 1 的实际列。实际上，可以通过使用 SQL Server 中包含表 1 中列名的系统视图，而不是手动创建枚举表来进一步改进这一点。虽然我不确定您是否可以拥有涉及以下的关系：系统视图。

你有什么建议？ EAV 是唯一的出路吗？

如果只有一个元数据列（只是“来源”，没有“记者”）怎么办？是否还有必要走 EAV 路线？您可以有“Director”、“Director_Source”、“Leading Male”、“Leading Male_Source”等列，但这很快就会变得丑陋。还有我没有想到的更好的解决方案吗？

如果我没有澄清任何一点，请发表评论，我会根据需要添加更多内容。哦，是的，我使用的电影数据是捏造的:)

编辑：为了简明地重申我的主要问题，我希望表 1 具有简单性和真正的 RDBMS 设计，它真正很好地描述了电影条目，同时仍然以安全且可访问的方式存储属性上的元数据。这可能吗？或者EAV是唯一的方法吗？

编辑 2：在做了更多的网络研究之后，我还没有找到关于 EAV 的讨论，该讨论集中在将元数据存储在列上的愿望上。实现 EAV 的主要原因几乎总是动态且不可预测的列，但我的示例中并非如此。在我的例子中，总是有相同的四栏：导演、男主角、女主角、反派。但是，我想存储有关每行每列的某些事实（来源和记者）。 EAV 可以促进这一点，但我想避免这样做。

Update

使用表 2 的设计，除了将列“Movie”重命名为“Name”并将整个表命名为“Movie”之外，下面是 SQL Server 2008 中返回表 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

您可以更改设计中您认为的事实值...您的数据模型中的事实似乎可以表示为以下 N 元组：

Movie | FactType | FactValue | FactSource | FactJournalist

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

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

有趣的是，您可以认为这是将 EAV 模型完全应用于数据并将单个电影（具有导演、主角、反派等的直观属性）分解为一个枢轴结构（其中属性集中于源）的逻辑扩展的信息来代替。

所提出的数据模型的优点是：

• 它是良好标准化的（尽管为了完整性，您可能应该将 FactType 字段标准化为参考表）
• 可以创建一个视图，将事实类型有效地转变成表格结构
• 它相对可扩展，允许数据库强制执行引用完整性和（如果需要）基数约束
• 电影事实表可以进行子类化以支持不同类型的电影事实，而不仅仅是那些简单的文本字段
• 对数据的简单查询相对有效

数据模型的一些缺点是：

• 复合条件查询更难（但并非不可能）编写（例如，查找导演为 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
...