代码之家  ›  专栏  ›  技术社区  ›  JoeCool

如何在列上存储元数据

entity-attribute-value normalization database-design
  •  11
  • JoeCool  · 技术社区  · 17 年前

    假设您正在收集即将上映的超级英雄电影的内幕信息,您的主电影表如下所示:

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

    虽然它很容易捕获我们想要的元数据,但却使查询更加困难。简单地获取一部电影的所有基本数据需要更多的时间。更具体地说,您必须在这里处理四行,以获得关于绿灯的四个重要信息,而在表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”,因此相关数据将不幸地分离。如果“attribute”列链接到另一个用作表1列枚举的表,那么这将有所帮助。当然,DBA将负责维护这个枚举表以匹配表1的实际列。实际上,通过在SQL Server中使用包含表1中列名称的系统视图,可以进一步改进这一点,而不是手工创建枚举表。尽管我不确定您是否可以拥有涉及系统视图的关系。

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

    如果它仅仅是一个元数据列(只是“源”,没有“记者”),那它还需要走EAV路线吗?你可以有“导演”、“导演来源”、“男主角”、“男主角来源”等栏目,但这很快就会变得难看。有没有比我想的更好的解决办法?

    如果我没有澄清任何问题,请评论,我会根据需要添加更多内容。哦,是的,我使用的电影数据是伪造的:)

    编辑:为了简洁地重述我的主要问题,我希望得到表1的简单性和真实的RDBMS设计,它确实很好地描述了一个电影条目,同时仍然以安全和可访问的方式存储属性上的元数据。这有可能吗?或者EAV是唯一的方法?

    编辑2:在做了更多的Web研究之后,我还没有找到一个关于EAV的讨论,它围绕着在列中存储元数据的愿望展开。实现EAV的主要原因几乎总是动态的和不可预测的列,在我的示例中并不是这样。在我的例子中,总是有相同的四列:导演,男主角,女主角,恶棍。但是,我想为每一行存储关于每一列的某些事实(来源和记者)。EAV将有助于实现这一目标,但我希望避免诉诸于此。

    更新

    除了将列“movie”重命名为“name”并调用整个表“movie”之外,使用表2设计,以下是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
    9 回复  |  直到 17 年前
        1
  •  6
  •   LBushkin    17 年前

    你可以改变你在设计中所认为的事实价值 …您的数据模型中的一个事实似乎可以表示为以下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
...
        2
  •  1
  •   Mark Canlas    17 年前

    有趣的场景。你可以通过把你的实体当作第一类对象来绕过EAV贫民区;让我们称之为事实。在这种情况下,你是非常正交的,因为每部电影都有完全相同的四个事实。您的EAV表可以是原始的/正确的表,然后您可以有一个外部进程来挖掘该表并将数据复制到适当规范化的表单(即第一个表)。通过这种方式,您可以获得所需的数据及其元数据,并且可以轻松地查询电影信息,精确到挖掘过程的运行频率。

    我认为您肯定需要一些“数据库外”的力量来确保数据仍然有效,因为似乎没有任何数据库方法来维护常规表和EAV表之间的完整性。我猜,通过一系列复杂的触发器,您几乎可以完成任何事情,但是一个“得到”您的问题的人工管理员可能更容易处理。

        3
  •  1
  •   devuxer    17 年前

    这是另一个想法……请随意在其中打孔:) 

    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

    我想的是电影桌上的栏目 能够 有不同的类型(在您的示例中,它们都是字符串/varchar,但也可以是数字或日期信息,也可以是源代码)。

    但是,源数据的列类型可能不会随电影数据的列类型的函数而变化,因此您可以为源使用更多EAV系统,而不会丢失数据的完整性。

    movierole表允许您显式枚举角色,以便在电影表的源和给定单元格之间创建可靠的链接。

    -丹

        4
  •  1
  •   too much php    17 年前

    由于您只有源数据的两个字段(source和reporter),因此我建议使用如下元数据表: 

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

    这将使不太重要的源数据远离主表,但查询不会变得复杂,代码的可读性也会更高。

    我只是建议 EAV 如果…

    • 您有3个以上的源元数据字段
    • 你 需要 以便轻松添加或更改电影字段。(像“恶棍”到“主要恶棍”这样的变化每天都要做几次)
        5
  •  0
  •   Walter Mitty    17 年前

    我的回答可能有点过于哲学化了。容忍我。

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

    EAV之所以会造成这样的问题,原因之一是它将数据和元数据混合在一行中。有时我自己也会故意这样做,作为一个中间步骤,我想要得到一个结果。但我从未尝试在我的可交付成果中混合数据和元数据。

    我知道为什么我从来没有这样做过,但我不能简明扼要地解释。

        6
  •  0
  •   JoeCool    17 年前

    因为没有人真正尝试过,我要回答我自己的问题。我很确定像EAV这样的桌子确实是唯一的出路。为了在每一列上存储元数据(在本例中是关于源和记者的),您实际上是将每一列本身作为一个实体来处理,EAV允许这样做。

    你 能够 走其他路线,比如为每个原始列添加第二列和第三列来存储数据,但这肯定会破坏一些基本的规范化规则,并且可能只会导致以后的痛苦。

        7
  •  0
  •   Michael Bray    17 年前

    隐马尔可夫模型。。。。我没有用过这个,所以我不是根据经验(也就是说,如果它不起作用的话不要怪我),但从表面上看,您似乎可以存储“公共”数据,您知道这些数据将一直存在于正常表中,而“元数据”可能会更改为XML。接下来的问题是如何很好地查询它,我认为您可能能够按照描述的那样做。 HERE .

        8
  •  0
  •   Community Mohan Dere    9 年前

    另一个需要考虑的方法是类表继承。Bill Karwin在 this SO answer 以及很多好的背景。

        9
  •  0
  •   Joel Goodwin    17 年前

    我会根据我需要编码的内容做出决定。

    如果src/journo只是附加信息,那么我将进入下一列。但是,如果我知道我将最终构建复杂的SRC/Journo查询,我将使用EAV,因为在元表中搜索记者的引用比在元表中搜索更容易。 首席女记者 和 恶棍记者 等。

    就个人而言,我倾向于将src/journo元数据转储到另一个表EAV样式中,但使用fk定义属性定义表。拥有一个自由格式的属性文本字段是灾难的秘诀——总是通过一个约束来控制您的属性。如果需要,可以实现触发器来提高引用完整性。

    对我来说,这就是我的观点。你是否认为新闻来源和记者本身就是关系问题,或者他们只是补充 电影 ?下一个改进级别将是为 电影电视剧 和 电影数据记者 它允许您将FK映射到定义有效的表 来源 和 记者 (关于这些消息来源/记者的进一步信息可以充实)。你在这里要做的是建立一个多对多的关系 电影 实体与 来源 (还有) 记者 实体。

    推荐文章
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号