Re: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

On 20 January 2011 14:32, Dotan Cohen <dotancohen@gmail.com> 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! > > -- > Dotan Cohen > > http://gibberish.co.il > http://what-is-what.com > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > I'd have my items table, my tags table and a join table for the two. My join table is really simple. UniqueID, ItemID, TagID. I'd recommend using a nested set approach for the tags (http://dev.mysql.com/tech-resources/articles/hierarchical-data.html gives a good explanation on the issues and methodology of nested sets). -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY