I think the canonical way would be to have one table for your items, one table
for your tags, and one table for your tag assignments.
CREATE TABLE items (
item_id INT(11) AUTO-INCREMENT PRIMARY KEY,
item_name VARCHAR(100) NOT NULL KEY,
...
);
CREATE TABLE tags (
tag_id INT(11) AUTO-INCREMENT PRIMARY KEY,
tag_name VARCHAR(100) NOT NULL KEY,
...
);
CREATE TABLE item_tags (
item_id INT(11) NOT NULL KEY,
tag_id INT(11) NOT NULL KEY
);
This way you could do
SELECT item_id, item_name FROM
tags JOIN item_tags ON tags.tag_id = item_tags.tag_id
JOIN items ON item_tags.item_id = items.item_id
WHERE ...
;
to get all of the items with a particular tag, or
SELECT tag_id, tag_name FROM
items JOIN item_tags ON items.item_id = item_tags.item_id
JOIN tags ON item_tags.tag_id = tags.tag_id
WHERE ...
;
with equal ease and efficiency.
Using an ever-lengthening bitmap for the tag assignments is a trap for the
unwary. The path to perdition is lined with the bodies of those who believed
"We'll never need more than x..."
As for setting up a hierarchy, that's trickier. One way to handle that is to
work like libraries do: 10 is "fiction", 10.05 is "crime novels", 10.05.07 is
"British authors", and so forth. Your `tags` table then looks like
CREATE TABLE tags (
tag_id INT(11) AUTO-INCREMENT PRIMARY KEY,
tag_name VARCHAR(100) NOT NULL KEY,
tag_number VARCHAR(100) NOT NULL KEY,
...
);
Then you can search for tags by
tag_number LIKE ('10.%') or
tag_number LIKE ('10.05%')
and so forth. This scheme is infinitely extendable. To get the entire
hierarchy, you simply
SELECT tag_number, tag_name FROM tags ORDER BY tag_number;
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
>-----Original Message-----
>From: Dotan Cohen [mailto:dotanco...@gmail.com]
>Sent: Thursday, January 20, 2011 9:32 AM
>To: mysql.; php-general.
>Subject: Organisational question: surely someone has implemented many Boolean
>values (tags) and a solution exist
>
>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!
>
>--
>Dotan Cohen
>
>http://gibberish.co.il
>http://what-is-what.com
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=je...@gii.co.jp
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
