database-design - 如何在列上存储元数据

Question

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

表一

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

表二

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

虽然它很容易捕获我们想要的元数据，但它使查询变得更加困难。简单地获取一部电影的所有基本数据需要更多的时间。更具体地说，您必须在此处处理四行才能获得有关绿灯侠的四个重要信息，而在表 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 列枚举的表，这可能会有所帮助。当然，DBA 将负责维护此枚举表以匹配表 1 的实际列。并且实际上，通过使用 SQL Server 中包含表 1 中列名称的系统 View ，而不是手动创建枚举表，实际上可能会进一步改进这一点。虽然我不确定你是否可以拥有涉及系统 View 。

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

如果它只有一个元数据列(只有“来源”，没有“记者”)——是否仍然需要走 EAV 路线？你可以有“Director”、“Director_Source”、“Leading Male”、“Leading Male_Source”等列，但这很快就会变得丑陋。有没有更好的解决方案我没有想到？

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

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

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

更新

使用表 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

Answer 1

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

Movie | FactType | FactValue | FactSource | FactJournalist

以下表结构应该支持您想要的数据模型，并且可以相对容易地被索引和连接。您还可以创建一个仅以事实值和事实类型为中心的 View ，以便您可以创建以下透视图:

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

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

建议的数据模型的好处是:

它是规范化的(尽管您可能应该将 FactType 字段规范化为引用表以确保完整性)

可以创建一个 View ，将事实类型有效地转换为表格结构

它相对可扩展，允许数据库强制引用完整性和(如果需要)基数约束

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