I'd exclude (1) because new tags require restructuring the table, (2)
and (3) because they break a cardinal rule of design and will be a mess
to query, leaving ...
4) Standard many-many bridge table:
mysql> CREATE TABLE items_tags (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
itemID int,
tagID INT
);
Will not require a major overhaul if you later turn categories into a tree.
PB
-----
On 1/20/2011 8:32 AM, Dotan Cohen wrote:
I am designing an application that make heavy usage of one-to-many
tags for items. That is, each item can have multiple tags, and there
are tens of tags (likely to grow to hundreds). Most operation on the
database are expected to be searches for the items that have a
particular tag. That is, users will search per tags, not per items.
These are the ways that I've thought about storing the tags, some bad
and some worse. If there is a better way I'd love to know.
1) Each item will get a row in a "tags" table, with a column for each tag.
mysql> CREATE TABLE tags (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
item VARCHAR(100),
tag1 bool,
tag2 bool,
....
tagN bool
);
With this approach I would be adding a new column every time a new
category is added. This looks to me a good way given that users will
be searching per tag and a simple "SELECT item FROM tags WHERE
tag1=true;" is an easy, inexpensive query. This table will get very
large, there will likely be literally thousands of items (there will
exist more items than tags).
2) Store the applicable tags one per line in a text field in the items table.
mysql> CREATE TABLE items (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
item VARCHAR(100),
tags text,
);
This looks like a bad idea, searching by tag will be a mess.
3) Store the tags in a table and add items to a text field. For instance:
mysql> CREATE TABLE tags (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
tagName VARCHAR(100),
items text,
);
This looks to be the best way from a MySQL data retrieval perspective,
but I do not know how expensive it will be to then split the items in
PHP. Furthermore, adding items to tags could get real expensive.
Caveat: at some point in the future there may be added the ability to
have a tag hierarchy. For instance, there could exist a tag
"restaurant" that will get the subtags "italian" and "french". I could
fake this with any approach by having a table of existing tags with a
"parentTag" field, so if I plan on having this table anyway would
method 3 above be preferable?
Note: this message is cross-posted to the MySQL and the PHP lists as I
am really not sure where is the best place to do the logic. My
apologies to those who receive the message twice.
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
