Help with Tags database design.

[arrival123]

Hello,

I'm currently trying to decide on a database design for tags in my web 2.0 application. The problem I'm facing is that I have 3 separate tables i.e. cars, planes, and schools. All three tables need to interact with the tags, so there will only be one universal set of tags for the three tables.

I read a lot about tags and the best articles I found were:

Road to Web 2.0 ( http://wyome.com/docs/Road_to_Web_2.0:_The_Database_Design )
tags: database schema ( http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html )

and a forum discussion on tags with a very similar problem:
http://www.webmasterworld.com/forum112/502.htm
But I don't like the solution, would like to stick with serial integer for Cars, Planes and Schools tables.

Currently, this is my DB design:

Cars (carid, carname, text)
Planes (planeid, planename, text)
Schools (schoolname, text) <------ School does not take int as primary key but a varchar.

Tags (tagid, tagname)
--- Now here is where I have the question. I have to link up three separate tables to use Tags
--- So when a new car is created in the Cars table, should I insert that carID into the TagsItems table
--- as itemID? So something like this?

TagsItems
(
tagid INT NOT NULL REFERENCES Tags.TagID,
itemid INT NULL, <---- really references Cars.carID and Planes.planeID
schoolname varchar NULL <---- Saves the Schools.schoolname
itemid + tagId as Unique
)

I also have a question on the schoolname field, because it accepts varchar not integer. There seems to be some design that would better fit my needs. I'm asking you guys for a little assistance.

[arrival123]

Hi,

I found this website ( http://forge.mysql.com/wiki/TagSchema ) that took Philipp Keller's article ( http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html ) on tags and rewrote it will more examples. I still have a question on the best way to connect three tables using this method. But I hope my research can help those who have the same question. Here is the bit I like the most:


Schema

The schema involves three tables which represent the many to many relationship between the item and the tag. Here, we deviate from the bookmark paradigm and use a general "item" semantics to indicate that the type of item is irrelevant to the schema design.

CREATE TABLE Items (
item_id INT UNSIGNED NOT NULL AUTO_INCREMENT
, item_name VARCHAR(255) NOT NULL
/* Many more attributes of the item... */
, PRIMARY KEY (item_id)
) ENGINE=InnoDB;

CREATE TABLE Item2Tag (
item_id INT UNSIGNED NOT NULL
, tag_id INT UNSIGNED NOT NULL
, PRIMARY KEY (item_id, tag_id)
, INDEX (tag_id)
, FOREIGN KEY fk_Item (item_id) REFERENCES Items (item_id)
, FOREIGN KEY fk_Tag (tag_id) REFERENCES Tags (tag_id)
) ENGINE=InnoDB;

CREATE TABLE Tags (
tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT
, tag_text TEXT NOT NULL
, PRIMARY KEY (tag_id)
, UNIQUE INDEX (tag_text)
) ENGINE=InnoDB;

[edit] Sample Schema with User Dimension

Additionally, when the application has a need for a user dimension (folksonomy), the schema can be expanded like so:

CREATE TABLE Users (
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT
, user_name VARCHAR(255) NOT NULL
/* Many more attributes of the user... */
, PRIMARY KEY user_id)
) ENGINE=InnoDB;

CREATE TABLE Items (
item_id INT UNSIGNED NOT NULL AUTO_INCREMENT
, item_name VARCHAR(255) NOT NULL
/* Many more attributes of the item... */
, PRIMARY KEY (item_id)
) ENGINE=InnoDB;

CREATE TABLE Tags (
tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT
, tag_text TEXT NOT NULL
, PRIMARY KEY (tag_id)
, UNIQUE INDEX (tag_text)
) ENGINE=InnoDB;

CREATE TABLE UserItemTag (
user_id INT UNSIGNED NOT NULL
, item_id INT UNSIGNED NOT NULL
, tag_id INT UNSIGNED NOT NULL
, PRIMARY KEY (user_id, item_id, tag_id)
, INDEX (item_id)
, INDEX (tag_id)
, FOREIGN KEY fk_User (user_id) REFERENCES Users (user_id)
, FOREIGN KEY fk_Item (item_id) REFERENCES Items (item_id)
, FOREIGN KEY fk_Tag (tag_id) REFERENCES Tags (tag_id)
) ENGINE=InnoDB;

[edit] Sample Schema with Summary Tables

If an application requires aggregate data access patterns -- such as COUNTing the number of tags per item, per user/item combination, etc -- then we recommend denormalizing the schema to use aggregate or summary tables which store specific counters. This technique is used in order to bypass the current InnoDB-specific limitation of poorly performing aggregate queries. Below, we use the MEMORY storage engine for the statistic tables since this engine provides excellent speed for primary key access due to its hash-based indexing. The data for MEMORY tables is lost upon system shutdown; however since all the information in the summary tables can be rebuilt from the data in the main tables, there is no need for concern. For systems where memory requirements are an issue, simply change the MEMORY storage engine to InnoDB.

CREATE TABLE Items (
item_id INT UNSIGNED NOT NULL AUTO_INCREMENT
, item_name VARCHAR(255) NOT NULL
/* Many more attributes of the item... */
, PRIMARY KEY (item_id)
) ENGINE=InnoDB;

CREATE TABLE Item2Tag (
item_id INT UNSIGNED NOT NULL
, tag_id INT UNSIGNED NOT NULL
, PRIMARY KEY (item_id, tag_id)
, INDEX (tag_id)
, FOREIGN KEY fk_Item (item_id) REFERENCES Items (item_id)
, FOREIGN KEY fk_Tag (tag_id) REFERENCES Tags (tag_id)
) ENGINE=InnoDB;

CREATE TABLE Tags (
tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT
, tag_text TEXT NOT NULL
, PRIMARY KEY (tag_id)
, UNIQUE INDEX (tag_text)
) ENGINE=InnoDB;

CREATE TABLE TagStat (
tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT
, num_items INT UNSIGNED NOT NULL DEFAULT 1
/* For each distinct type of item tracked with tags, add a field... */
, PRIMARY KEY (tag_id)
, FOREIGN KEY fk_Tag (tag_id) REFERENCES Tags (tag_id)
) ENGINE=MEMORY;

--------------