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