多类型实体的标签数据库设计

Question

我正在设计一个用于存储食谱的数据库模式。在这个数据库中，我想要能够标记不同类型的实体（配料、食谱发布者、食谱等）。因此，一个标记具有多个n:m关系。如果我使用“三表设计”，那么对于每种实体类型（食谱、配料、发布者），都会产生一个交叉表。换句话说，每次引入一个实体，我就必须添加一个交叉表。

我考虑创建一个具有唯一ID的表，所有实体都引用该ID，并在标记表和“唯一ID”表之间建立n:m关系。这样，“唯一ID”表和标记表之间只有一个交叉表。

以防有些人认为已经提出了这个问题。我已经阅读了Database Design for Tagging，那里提到了三表设计。

- Saab

Answer 1

我认为使用一个表格来记录所有标签分配并没有什么问题（而不是为每个可标记实体创建多个表格）。

然而，在你的设计中，有一个重要的细节让我感到困惑：如果你打算采用类似以下的方式

- - - - - - - - - -
Tag
    ID           // PK
    Name
    ...

- - - - - - - - - -
Taggable
    ID           // PK
    ...

- - - - - - - - - -
TagAssignment
    Tag_ID       // FK -> Tag.ID
    Taggable_ID  // FK -> Taggable.ID
    ...

- - - - - - - - - -
EntityOne
    Taggable_ID  // FK -> Taggable.ID
    ...

- - - - - - - - - -
EntityTwo
    Taggable_ID  // FK -> Taggable.ID
    ...

那么你的实体类将拥有自己的主键，还是将使用EntityOne.TaggableID和EntityTwo.TaggableID作为EntityOne和EntityTwo的事实上的主键？

在大多数情况下，我会谨慎起见，让实体拥有自己的ID：

- - - - - - - - - -
EntityOne
    ID           // PK
    Taggable_ID  // FK -> Taggable.ID (Nullable)
    ...

- - - - - - - - - -
EntityTwo
    ID           // PK
    Taggable_ID  // FK -> Taggable.ID (Nullable)
    ...

这种设计不需要每个实体都有一个对应的Taggable实例，因此也不需要与实体相关的所有代码都了解标签。但是，如果在系统中标记将变得非常普遍，并且您确定不需要为实体使用任何其他“公共祖先”（即除Taggable之外的实体），那么您可能可以不使用实体的“内在”ID。

注意：我从未尝试过实现这样的内容，因此我所有的建议都是纯理论的。因此，请不要因为我没有看到一些明显的缺陷而攻击我。 :-)

回应Bill Karwin的评论：

你是对的：上述设计不会防止多个实体引用同一个Taggable。但是：

1.如我所说，一切都取决于需求。如果我们确信Taggable将是实体的唯一“公共祖先”，那么使用Taggable_ID FK作为实体的PK就可以了。但是，例如，如果某些实体恰好也是“可观察的”（考虑通知、通知计划等），或者“可做任何事情”:-)，我们是否可以通过将任何实体硬绑定到Taggable来削减所有这些“能力”？

2.如果您真的想在数据库级别执行一个标记对一个实体的约束...据我所知，至少有一种常见的方法可以在不使FK作为PK的情况下实现：通过引入“可标记”类型（对于某些其他功能可能很有用）。

沿着这样的线路，我们可以同时拥有蛋糕和吃掉它：

- - - - - - - - - -
Taggable
    ID           // PK
    Type        
    ... 
    - - - - - - - -
    Constraint: (ID, Type) is unique


- - - - - - - - - -
EntityOne
    ID
    Taggable_ID   
    Taggable_Type // Constraint: always = 'EntityOne'
    ...
    - - - - - - - -
    FK: (Taggable_ID, Taggable_Type) -> (Taggable.ID, Taggable.Type)

当然，所有这些都比只将实体绑定到可标记的标签要复杂得多。但是我只是试图讨论除原始问题提供的狭窄视角之外应该考虑的内容，这是我的个人意见。

- Yarik

---

将每个实体的Taggable_ID列同时设置为 FOREIGN KEY 和 NOT NULL PRIMARY KEY，要简单得多。这样，您可以确保每个实体都映射到Taggables表中的一行。您的设计允许多个食谱映射到同一个Taggable行。 - Bill Karwin

不，多态关联是指向两个不同父表的外键。选择哪个父表用于特定行取决于子表中的另一个属性。当然，您不能为多态关联使用传统的外键约束。 - Bill Karwin

在传统的、规范化的数据库设计中，每个属性都以单独的列表示。在这种情况下的问题是，如果你试图让可标记的对象引用多个实体，并且让实体引用多个“-能力”，那么你就会陷入困境。我需要考虑一个解决方案。 - Bill Karwin

好的，没关系，我把它想得比必要的更复杂了。你只需要在每个“-ability”和Taggables表之间再加一个交叉表 - 这实际上与可标记性无关，它只是所有其他实体的ID表。 - Bill Karwin

我会稍微不同地表达：我建议为每个横跨多个实体的“-ability”单独设置一个“交集”表：[实体1/实体2 -> 标签化 <-> 标签]，[实体1/实体3 -> 可拥抱 <-> 拥抱]等等。:-) - Yarik

Answer 2

我认为这取决于你如何使用标签。如果你只搜索一种类型的实体，我想你可以为每个实体类型创建一个额外的交集表来打标签。换句话说，通常会说“显示带有标签‘美味’的食材”，但是如果说“显示既有食材又有菜谱发布者的标签‘美味’”就不太清楚了。在这种情况下，每个实体都有一个单独的交集表是可以的。

但是，如果您需要搜索具有给定标签的所有实体类型，则使用单个“ID”表更容易。使所有实体表都指向它，并使用您定义为主键和外键的列即可。

CREATE TABLE Recipes (
  recipe_id INT NOT NULL PRIMARY KEY, -- not auto-generated
  FOREIGN KEY (recipe_id) REFERENCES Taggables(id)
);

这个计划唯一的弱点是无法阻止在Recipes和Ingredients中的行指向Taggables中同一行的情况。

INSERT INTO Taggables (id) VALUES (327);
INSERT INTO Recipes (recipe_id, name) VALUES (327, 'Hollandaise sauce');
INSERT INTO Ingredients (ingr_id, name) VALUES (327, 'eggs');

您希望所有与鸡蛋相关的标签也适用于荷兰酱吗？

我只是指出单表设计的这个方面。考虑到其他要求，它仍然可能是建模标记的最佳方式。但是您应该警惕依赖表中id冲突的潜力。

- Bill Karwin

---

嗯...我以为两个对象（例如两个食谱；或者一个食谱和一个配料）可以共享一个标签，但不能是同一个“可标记”的实例。你觉得让实体之间可以共享“可标记”（而不是标签）有任何实际意义吗？ - Yarik

没错，我的意思是，我认为多个实体引用taggables中的同一行记录很少见，或者根本就没有意义。理想情况下，数据库约束可以防止无效条目，但在这种设计中，它们无法起到作用。 - Bill Karwin

Answer 3

我认为你走在了正确的道路上。你描述得非常好，你有几个不同的实体。你可以创建一个名为“entities”的表，其中包含所有共同的属性（如果有的话）。例如：

实体

• 实体ID
• 名称

成分

• 实体ID
• 数量

食谱发布者

• 实体ID
• 其他一些信息

现在你可以有一个标记实体的表。

- Tjofras

---

这正是我现在想要的。我唯一担心的是实体表可能会导致潜在的死锁。但我认为我可以通过不将关键数据存储在实体表中来避免这种情况。 - Saab

Answer 4

这个怎么样？

类型（主键：类型，集合ID[，类型描述]）

属性（主键：（集合ID，外键：类型），值）

附注：加粗/斜体真的很糟糕

- thAAAnos

---

如果我理解正确的话，这将是一种实现带有属性类型的通用方式。这意味着我需要在此之上再添加一个层级，以便在代码中方便使用。 - Saab

这是一种称为实体-属性-值的设计。它存在许多问题，并且对于Saab仅具有少量不同实体的情况来说，完全是过度设计。 - Bill Karwin

Answer 5

正常情况下，为食谱、配料等制作表格。

然后您的标签表格应该如下所示：Id、Type、Tag。

我建议在代码中使用枚举来区分不同的“类型”（实体）。

- Kyle West

---

我不喜欢的是有些东西在数据库中，有些则在代码中。我希望能够让数据库发挥其擅长的功能：存储关系数据。 - Saab

@Kyle：这个设计被称为“多态关联”。它是一个有缺陷的设计，因为它混淆了数据和元数据。例如，你如何确保Type存储了一个有效的表名？ - Bill Karwin

即使您的代码有一个枚举，也没有数据库约束来确保该列中命名的表实际存在。 - Bill Karwin

此外，多态关联会阻止你声明一个实际的外键。也就是说，tag.id 引用了多个实体表中的任一一个，但外键必须指定它引用的一个父表。因此，你必须在应用程序代码中执行完整性约束。 - Bill Karwin

Answer 6

我手头也有一个类似的“问题”。我正在开发一个小型产品数据库，其中涉及标记并为标记赋值（例如标记名称：颜色，值：绿色）。

主要的两个表是物品（I）和文章（A）。物品是实际的物品，而文章则是从物品中派生出来的。文章是可以在网站上显示的内容，而物品则是存储在仓库中的物品。 这种关系的一个小例子可能是汽车零件。具有已知尺寸和其他数据的散热器实际上可以适合许多不同的型号和制造商，这就是用于表示散热器的物品与指示散热器适合哪些车型的多个文章相关联的原因。 另一方面，我们可能会为一个型号提供两种不同的散热器，一种是全新的工厂版本，另一种是重新制造的版本。在这种情况下，存在两个与同一篇文章相关联的物品。

因此，我和A之间存在N:M的关系。

物品和文章都有一些属性。例如，散热器可能具有诸如状态、材料、重量、高度、宽度和厚度等数据。

文章还有一些基本信息，例如制造商、型号、年份、发动机等，但也可能需要一些特殊数据，例如底盘型号、变速器类型或者是在同一型号上使用的两种不同配件类型之类的东西。

因为两个物品可以链接到一个文章，这意味着我不能只标记文章。用两个条件值标记文章是愚蠢的，另一方面，将一个物品标记为多个模型、制造商或某些特殊要求的实例也不是一个好主意。有两种属性类型，第一种表示某些东西的样子，第二种表示它适合什么。

标签不一定要有值，它们可以只作为分配给实体的传统标签。

散热器只是一个简单产品的例子。我们也可以在数据库中放置一些计算机零件或服装。这意味着我需要能够在两个不同的实体I和A上放置不同的“标签”。

我需要能够在网店中实现文章搜索功能。假设我正在使用基于树的导航，其中有一个名为“二手日产散热器”的类别。 搜索将涉及搜索文章和物品，文章具有标记Model：Nissan，物品具有标记Condition：Used。 当然，当用户查看文章时，他确实会看到与文章相关联的所有物品。

我正在考虑的解决方案之一是三角形数据库设计，其中有一个名为tags的通用表格，用于所有属性和标记。

我们有项目（I），文章（A）和标记（T）表格 它们通过N：M关系连接： I2A将项目连接到文章。 T2I将标记连接到物品，并可能存储标记或属性的值。 T2A将标记连接到文章，并可能为标记存储值。

在纸上，这个用于解决此问题的6个表格设计看起来非常不错，但我正在努力形成一个体面的查询，以选择匹配一组不同标记及其值的文章，例如：Condition=Remanufactured，Make=Nissan

我想做的是像www.summitracing.com一样。在“Shop”下面左侧选择“部门”，然后选择任何类别，您将看到他们如何成功地为项目添加属性。他们对大多数应用程序都有引擎大小，但是在寻找轮毂时，他们还有一个宽度属性。

非常感谢您对此的任何反馈，我正在尝试设计此功能。

- Willem van Schevikhoven