用于标记多种类型实体的数据库设计

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.

我目前正在设计一个用于存储食谱的数据库模式。在这个数据库中,我希望能够标记不同类型的实体(成分,配方发布者,食谱等)。所以标签有多个n:m关系。如果我使用“三表设计”,这将导致我拥有的每个实体类型(配方,成分,发行人)的表(交叉表)。换句话说,每次我介绍一个实体时,我都要为它添加一个交叉表。

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.

我正在考虑创建一个具有唯一ID的表,所有实体都引用该表,并且在tags表和“unique id”-table之间建立n:m关系。这样,“unique id”-table和tag表之间只有一个交叉表。

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.

以防万一有人会认为这个问题已被提出。我已经阅读了数据库设计标记。并且提到了三种表格设计。

6 个解决方案

#1

I would say it depends on how you want to use the tags.

我会说这取决于你想如何使用标签。

I would imagine you could create an additional intersection table for each entity type you want to tag, if you only search one type of entity at a time. In other words, it would be normal to say, "show me the ingredients with tag 'yummy'" but it's not clear what it would mean to say, "show me both ingredients and recipe issuers with tag 'yummy.'" In this case, having a separate intersection table per entity is fine.

我想你可以为你想要标记的每个实体类型创建一个额外的交集表,如果你一次只搜索一种类型的实体。换句话说,通常会说“向我展示标签'美味'的成分”,但不清楚它的含义是什么,“向我展示标签'美味的两种成分和食谱发布者'。”在此例如,每个实体有一个单独的交叉表是好的。

But if you do need to search for all entities of all types with a given tag, then using the single "ID" table is easier. Make all the entity tables point to it with a column that you define as both a primary key and a foreign key:

但是,如果您确实需要使用给定标记搜索所有类型的所有实体,则使用单个“ID”表更容易。使用您定义为主键和外键的列使所有实体表指向它:

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

The only weakness of this plan is that you can't prevent a row in both Recipes and Ingredients from pointing to the same row in Taggables.

这个计划唯一的缺点是你不能阻止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');

Do you want every tag associated with eggs to also apply to Hollandaise sauce?

你想要与蛋相关的每个标签也适用于荷兰酱吗?

I'm just pointing out this aspect of the single-table design. It may still be the best way to model your tagging, given other requirements. But you should be watchful of the potential for collision of id's in the dependent tables.

我只是指出单表设计的这个方面。鉴于其他要求,它可能仍然是建模标记的最佳方式。但是你应该注意依赖表中id的冲突可能性。

#2

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

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?

那么你的实体类是否有自己的主键,或者你将使用EntityOne.TaggableID和EntityTwo.TaggableID作为EntityOne和EntityTwo的事实上的主键?

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

在大多数情况下,我会谨慎,让实体拥有自己的ID:

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

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

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.

这不需要每个实体都具有Taggable的相应实例,因此这不需要与实体有关的每一段代码都知道标签。但是,如果标记在系统中真的无处不在,并且如果你确定你不需要任何其他“共同祖先”的实体(也就是Taggable除外),那么你可能会在没有“内在”的情况下逃脱“实体的ID。

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

注意:我从来没有尝试过这样的事情,所以我的所有建议都纯粹是理论上的。所以如果我没有看到一些明显的缺陷,请不要开枪。 :-)

In response to Bill Karwin's comment:

回应Bill Karwin的评论:

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

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

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?

就像我说的,一切都取决于要求。如果我们确定Taggable将成为实体的唯一“共同祖先”,那么可以使用Taggable_ID FK作为实体的PK。但是,例如,如果碰巧“可标记”的某些实体也必须“可观察”(思考通知,通知时间表等)或“无论如何”:-)?我们可以通过将任何实体与Taggable联系起来来削减所有这些“能力”吗?
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).

如果你真的想让DB-level强制执行one-taggable-one-entity约束...... AFAIK,至少有一种常见的方法可以做到这一点,而不会让FK充当PK:通过引入taggables的“类型”(无论如何可能对某些其他功能有用)。

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.

当然,所有这些都比将实体绑定到taggables更复杂。但我只是试图讨论除了原始问题提供的狭隘图片之外,还应该考虑什么。

#3

I think you're on the right track. You have described it really good, you have a couple of different entities. You could create a table called entities witch contains all the common attributes (if there is any). So for example

我认为你走在正确的轨道上。你已经描述它非常好,你有几个不同的实体。您可以创建一个名为entities的表,其中包含所有常用属性(如果有的话)。所以举个例子

Entity

EntityId
Name

Ingredient

EntityId
Amount

RecipeIssuer

EntityId
SomeOtherInformation

Now you can have a table to tag entities.

现在,您可以拥有一个表来标记实体。

#4

make tables as normal for recipies, ingredients, etc.

使表格成为正常的收件人,成分等

then your tag table should look this like this: Id, Type, Tag

那么你的标签表应该是这样的:Id,Type,Tag

I'd recommend using an enum in code to distinguish the different "Types" (entities).

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

#5

Howabout this?

Types( PK:Type,set_id[,TypeDesc])

Attributes( PK:(set_id,FK:Type),Value)

PS: Bold/Italics Realy Suck

PS:Bold / Italics Realy Suck

#6

I have a similar 'problem' on my hands too. I am developing a small product database which involves tagging and also giving tags a value (tagname: color, value: green for example).

我的手上也有类似的“问题”。我正在开发一个小型产品数据库,它涉及标记并为标记赋值(标记名:颜色,值:绿色例如)。

The two main tables are items (I) and articles (A). Items are actual physical items, and articles are derieved from items. Articles are something that can be displayed on a website, and items are the ones to be stored in a warehouse. A small example of this relationship could be car parts. A radiator with known dimensions and other data can actually fit many different models and makes, that's why the item used to respresent the radiator relates to multiple articles which indicate what the radiator can fit. On the other hand we might have two different radiators available for one model, one that is a factory-new version, and one that is just remanufactured. In such a case there are two items relating to the same article.

两个主要表格是项目(I)和文章(A)。物品是实际的物理物品,物品是从物品中取出的。文章可以在网站上显示,而项目是存储在仓库中的项目。这种关系的一个小例子可能是汽车零件。具有已知尺寸和其他数据的散热器实际上可以适合许多不同的模型和制造,这就是用于重新呈现散热器的物品涉及指示散热器可以适合什么的多个物品的原因。另一方面,我们可能为一个型号提供两种不同的散热器,一种是工厂新版本,另一种是再制造。在这种情况下,有两个项目涉及同一篇文章。

So, I and A have an N:M relationship.

所以,我和A有N:M的关系。

Items and articles have certain properties. For instance the radiator item might have data like condition, material, weight, height, width and thickness. The article also has some basic information like make, model, year, engine etc, but might also need some special data like chassis model, transmission type, or something else like two different fitting types that have been used on the same model. Because two items can link to one article, it means i cant just tag articles. Tagging an article with both condition values is just stupid, on the other hand tagging one item with multiple instances of a model, make or some special requirement is also not a good idea. _there are two types of properties, the first indicate what something is like, and the second type indicates what it will fit.

物品和物品具有某些属性。例如,散热器项目可能具有条件,材料,重量,高度,宽度和厚度等数据。文章还有一些基本的信息,如品牌,型号,年份,引擎等,但也可能需要一些特殊的数据,如底盘模型,传输类型,或其他类似于两种不同的配件类型,已在同一型号上使用。因为两个项目可以链接到一篇文章,这意味着我不能只标记文章。使用两个条件值标记文章只是愚蠢的,另一方面用一个模型的多个实例标记一个项目,make或某些特殊要求也不是一个好主意。 _有两种类型的属性,第一种表示什么是什么,第二种表示它适合什么。

Tags do not have to have a value, they can just simply act as a conventional tag that's assigned to an entity.

标签不必具有值,它们可以简单地充当分配给实体的传统标签。

Radiators are just an example of a simple product. We might aswell put some computer parts or clothing in our database. This means that i need to be able to put different 'tags' on two different entities, I and A.

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

I need to be able to implement a search for articles in a webshop. Lets say i am using a tree-based navigation where i have a category called "Used nissan radiators". The search would involve searching both the articles and items, articles have the tag Model:Nissan, and items have the tag Condition:Used. Ofcourse when the user looks at the article he will indeed see all the items associated with the article.

我需要能够在网上商店中搜索文章。假设我使用的是基于树的导航,我有一个名为“二手日产散热器”的类别。搜索将涉及搜索文章和项目,文章具有标签Model:Nissan,并且项目具有标签Condition:Used。当用户查看文章时,他确实会看到与文章相关的所有项目。

One of the solutions i am pondering with is a triangle database design where is a common table called tags for all the properties and tags.

我正在思考的解决方案之一是三角形数据库设计,其中是一个名为所有属性和标签的标签的公共表。

We have the tables items (I), articles (A) and tags (T) They are joined with N:M relationships: I2A joins the items to the articles. T2I joins the tags to the items and might store the value for the tag or property too. T2A joins the tags to the articles and might store a value for the tag too.

我们有表格项目(I),文章(A)和标签(T)它们与N:M关系连接:I2A将项目连接到文章。 T2I将标记加入到项目中,并且可能也存储标记或属性的值。 T2A将标签加入到文章中,并且可能也存储标签的值。

On paper, this 6-table design to tackle this problem looks quite nice, but i am getting a headache on forming a decent query where i can select the articles that match a set of different tags and their values, for example: Condition=Remanufactured, Make=Nissan

在纸面上,这个解决这个问题的6桌设计看起来相当不错,但是我在形成一个体面的查询时遇到了麻烦,我可以选择匹配一组不同标签及其值的文章,例如:Condition = Remanufactured ,Make =日产

What i want to be able to do, is something like www.summitracing.com. Select Departments from the left below "Shop", the select any category and you'll see how they have managed to give items some properties. They have engine size for most applications, but when looking for rims, they also have a property for the width.

我想要做的是像www.summitracing.com。从“商店”下方的左侧选择部门,选择任何类别,您将看到他们如何设置为项目提供一些属性。它们具有适用于大多数应用的引擎尺寸,但在寻找轮辋时,它们也具有宽度属性。

Any feedback on this would be greatly appreciated, im am starting to hit the dead trying to design this.

任何有关这方面的反馈将非常感激,我开始尝试设计这个。

#1