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

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?

Answers


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

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


Well, usually tags are implemented with many-to-many relationship (m:n relationship, if you want). There are three tables:

tags
    id INT NOT NULL AUTO INCREMENT
    name VARCHAR NOT NULL
    .
    .
    .
    possibly other fields
    .
    .
    .
    PRIMARY KEY (id)

items_you_want_to_tag
    id INT NOT NULL AUTO INCREMENT PRIMARY KEY
    name VARCHAR NOT NULL
    .
    .
    .
    possibly other fields
    .
    .
    .
    PRIMARY KEY (id)

xref
    tag_id INT NOT NULL
    item_id INT NOT NULL
    FOREIGN KEY (tag_id) REFERENCES tags(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items_you_want_to_tag(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (tag_id, item_id)

The above schema is in pseudocode, of course.

So your schema looks just about right to me with one exception. If you want to tag two tables, I would create separate tags tables for each product type (Trackable product 1 and Trackable product 2 tables in your case) and also create two intersection tables. So you would have six tables.

Make sure to use correct indexes though, or else it won't scale that well :)

UPDATE:

Or, if you want to be able to tag both product types with the same tag, add another field to the intersection table containing a product group and add it to the multi primary key (as already pointed out by mjv ;)).


Need Your Help

How to add optgroup to dropdown loop?

php mysql select loops optgroup

I have a loop that displays a list of variables in a dropdown box (based on the filtered category). I'd like to add in option groups (using subcategories) from my table. How can I add this to par...

apply two functions to the two halves of a numpy array

python arrays numpy

I am trying to find how to apply two functions to a numpy array each one only on half the values.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.