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