对数据库中的可标记项目进行建模

发布于 2024-12-04 07:58:33 字数 592 浏览 5 评论 0原文

我将标签存储为多对多关系，如这篇文章。

我现在想扩展标签以便能够标记帖子以外的实体。我在名为帖子、链接、文章等的表中拥有所有这些内容。我应该选择：

tags_items

tag_id | item_id | item_type
-----------------------------
1        2         post
1        42        link
3        7         article

还是创建多个表

tags_posts
tag_id | post_id 

tags_links
tag_id | link_id 

tags_article
tag_id | article_id

这迫使我为我想要标记的每个实体创建一个新表，但它使我更容易强制执行引用正直。

每种方法的优点和缺点是什么？

原文

I am storing tags to posts as a many-to-many relationship, like in this post.

I now want to extend tags to be able to tag entities other than posts. I have all these in tables named posts, links, articles, etc. Should I opt for:

tags_items

tag_id | item_id | item_type
-----------------------------
1        2         post
1        42        link
3        7         article

Or create multiple tables

tags_posts
tag_id | post_id 

tags_links
tag_id | link_id 

tags_article
tag_id | article_id

This forces me to create a new table for every entity I want to tag, but it makes it easier for me to enforce referential integrity.

What are advantages and disadvantages to each approach?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

许你一世情深 2024-12-11 07:58:34

在这两个选择中，我更喜欢第二个。正如你所说，它使 RI 变得更容易。另一种选择是对可标记项目进行超级键入。他们需要共享一个 ID 方案，但假设 ID 都是完全内部的（任何好的代理键都应该如此），那么这应该不是问题。

Taggable_Items
    tag_item_id INT (PK)

Posts
    post_id    INT (PK, FK to tag_item_id in Taggable_Items)
    posted_by  INT (FK to your Users table or whatever)
    post_text  VARCHAR(MAX)
    ...

Links
    link_id      INT (PK, FK to tag_item_id in Taggable_Items)
    url          VARCHAR(1000)
    description  VARCHAR(MAX)
    ...

Tags
    tag_id  INT (PK)
    name    VARCHAR(20)

Tagged_Items
    tag_item_id (PK, FK to tag_item_id in Taggable_Items)
    tag_id      (PK, FK to tag_id in Tags)

我希望这已经足够清楚了。如果没有意义，请发表评论。

Of the two choices I would prefer the second. As you say, it makes R.I. much easier. Another option is to super-type your taggable items. They would need to share an ID scheme, but assuming that the IDs are all completely internal (as any good surrogate key should be) that shouldn't be an issue.

Taggable_Items
    tag_item_id INT (PK)

Posts
    post_id    INT (PK, FK to tag_item_id in Taggable_Items)
    posted_by  INT (FK to your Users table or whatever)
    post_text  VARCHAR(MAX)
    ...

Links
    link_id      INT (PK, FK to tag_item_id in Taggable_Items)
    url          VARCHAR(1000)
    description  VARCHAR(MAX)
    ...

Tags
    tag_id  INT (PK)
    name    VARCHAR(20)

Tagged_Items
    tag_item_id (PK, FK to tag_item_id in Taggable_Items)
    tag_id      (PK, FK to tag_id in Tags)

I hope that's clear enough. Please post a comment if it doesn't make sense.

回复收藏 0 原文