设计可以标记多个数据库表的标记系统 [英] Designing tag system that can tag multiple db tables

问题描述

我想允许用户标记项目，以便他们可以使用标记搜索它们。什么是实现这个干净的最好的方法？到目前为止，我想出的解决方案只涉及为我当前的数据库系统添加两个额外的表。

I am wanting to allow users to tag items so that they can search for them using tags. What is the best way of achieving this cleanly? So far the solution I have come up with only involves adding two extra tables to my current db system.

<db Trackable product 1>
int id;
info etc
</>

<db Trackable product 2>
int id;
info etc
</>

//defines the M:M relationship between Tag and various types of Trackable products
<db TagLink>
int trackableProd1Id
int trackableProd2Id
int tagId
</>

<db Tag>
int tagId
tag name etc
</>

这是一个很好的方法吗？这种方法的一个好处是，它应该扩展好，它也允许我通过简单地添加一个列到TagLink表中添加更多可跟踪的产品。这显然不是一个好主意，如果我计划跟踪10的表，但最多3-4个表，它应该证明是很好的应该不是吗？

Is this a good way to go about it? A benefit of this approach is that it should scale well and it also allows me to add more trackable products in the future by simply adding a column to TagLink table. This is obviously not a good idea if I planned to track 10's of tables but for up to 3-4 tables it should prove to work well shouldn't it?

推荐答案

我建议您引入多列外键，而不是在TagLink表中使用多个TrackableProd_N_id列，例如

Rather than multiple "TrackableProd_N_id" columns in TagLink table, I suggest you introduce a multi-column foreign key, such as

   TagLink table
      int ProdGroup    -- "points" to table 1 vs. table 2 etc.
      int ProductId
      int TagId

这种方式，当产品的其他来源出现，你只需要发明一个新的ProdGroup号码，并使用ProductId来自所述表的其他主键）。

in this fashion when additional sources of product come up you merely need to "invent" a new ProdGroup number for them, and use the ProductId (or other primary key from said table).

这篇关于设计可以标记多个数据库表的标记系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！