用于标记多种类型实体的数据库设计 [英] Database design for Tagging multiple types of entities

问题描述

我目前正在设计一个用于存储食谱的数据库模式。在这个数据库中有不同类型的实体，我想要能够标记（成分，食谱发行者，食谱等）。所以一个标签有多个n：m关系。如果我使用三表设计，这将导致我拥有的每个实体类型（食谱，配料，发行者）的表（交叉表）。换句话说，每当我介绍一个实体时，我必须为它添加一个交叉表。

I'm currently designing a database schema that's used to store recipes. In this database there are different types of entities that I want to be able to tag (ingredients, recipe issuers, recipes, etc). So a tag has multiple n:m relations. If I use the "three table design", this would result in tables (cross table) for every entity type (recipes, ingredients, issuers) that I have. In other words every time I introduce an entity I have to add a cross table for it.

我正在考虑创建一个具有唯一ID的表，实体是指标签表和unique id-table之间的关系。这样，唯一ID表和标签表之间只有一个十字表。

I was thinking of creating one table which has a unique id, that all the entities refer to, and a n:m relation between the tags table and the "unique id"-table. This way there is just one cross table between the "unique id"-table and the tag table.

只是有些人会考虑这个问题。我已经阅读了数据库设计标签。而且还提到了三个表格设计。

Just in case that some people will think this question already was asked. I already read Database Design for Tagging. And there the three table design is mentioned.

推荐答案

对于所有标签分配，单个表格没有任何错误（相对于多个表 - 每个可标记实体一个）。

I don't see anything wrong with having a single table for all tag assignments (as opposed to multiple tables - one for each taggable entity).

然而，您的设计中的一个重要细节对我来说仍然不明确：如果您要沿着这些线路进行某些操作

However, one important detail in your design remains ambiguous to me: if you are going to have something along these lines

- - - - - - - - - -
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 作为<$ c $的实际主键c> EntityOne 和 EntityTwo ？

then are your entity classes going to have their own primary keys or are you going to use EntityOne.TaggableID and EntityTwo.TaggableID as de facto primary keys for EntityOne and EntityTwo?

在大多数情况下，我会谨慎并且允许实体有自己的ID：

In most general case, I would be cautious and let entities have their own IDs:

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

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

这不需要每个实体拥有相应的 Taggable 实例，因此这不需要与实体有关的每一段代码也能识别标签。但是，如果标签在系统中真的无处不在，并且如果您确定您不需要任何其他实体的共同祖先（也就是说，除了 Taggable ），那么你可能没有实体的内在ID。

This would not require each entity to have a corresponding instance of Taggable and therefore this would not require every piece of code concerned with an entity to also be aware of tags. However, if tagging is going to be really ubiquitous in the system, and if you are sure that you won't need any other "common ancestors" for entities (that is, other than Taggable), then you might get away without "intrinsic" IDs for entities.

NB ：我从来没有尝试过这样实现所以我所有的建议都是纯理论的。所以请不要拍我，如果我没有看到一些明显的缺陷。 ： - ）

NB: I never tried to implement anything like this, so all my recommendations are purely theoretical. So please do not shoot me if I do not see some obvious flaws. :-)

回应Bill Karwin的评论：

In response to Bill Karwin's comment:

你是对的：上述设计并不能阻止多个实体引用相同的 Taggable 。但是：

You are right: the design described above does not prevent multiple entities to refer to same Taggable. But:

1. 像我说的一样，都取决于要求。如果我们确定 Taggable 将成为实体唯一的共同祖先，那么可以使用 Taggable_ID FK作为实体的PK。但是，例如，如果一些实体是可注册的，那么还需要注意（思考通知，通知时间表等）或无所不能:-)？我们可以通过将任何实体绑定到可标注来切断所有这些能力？

1. Like I said, all depends on requirements. If we are sure that Taggable is going to be the only "common ancestor" of entities, then it is okay to use Taggable_ID FKs as PKs for entities. But, for example, what if some entities that happen to be "taggable" also have to be "watchable" (think notifications, notification schedules, etc.) or "whatever-able" :-)? Can we cut all those "abilities" off by tying any entity hard to Taggable?

如果您真的想要实现一级注册单一约束的数据库级别AFAIK，至少有一种常见的方法这样做不会使FK作为PK：通过引入类型的标签（这可能对某些其他功能有用）。

If you really want to have DB-level enforcement of one-taggable-one-entity constraint... AFAIK, there is at least one common way to do that without making FKs serve as PKs: by introducing "types" of taggables (which may be useful for some other functionality anyway).

沿着这些方向的东西让我们有一个蛋糕，然后吃点东西：

Something along these lines would let us have a cake and eat it:

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

当然，这一切比只是实体更复杂绑在标签上。但是，我只是想讨论一下我谦虚的观点，除了原始问题提供的窄幅图外，还应该考虑一下。

Of course, all this is more complicated than just having entities tied to taggables. But I was just trying to discuss what, in my humble opinion, should be considered in addition to the narrow picture provided by the original question.

这篇关于用于标记多种类型实体的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！