List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:January 20 2011 3:22pm
Subject:RE: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
View as plain text  
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: jerry@stripped
Web site: www.the-infoshop.com


>-----Original Message-----
>From: Dotan Cohen [mailto:dotancohen@stripped]
>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=1




Thread
Organisational question: surely someone has implemented many Booleanvalues (tags) and a solution existDotan Cohen20 Jan
  • Re: Organisational question: surely someone has implemented manyBoolean values (tags) and a solution existPeter Brawley20 Jan
    • Re: Organisational question: surely someone has implemented manyBoolean values (tags) and a solution existDotan Cohen20 Jan
  • RE: Organisational question: surely someone has implemented many Boolean values (tags) and a solution existJerry Schwartz20 Jan
    • Re: Organisational question: surely someone has implemented manyBoolean values (tags) and a solution existDotan Cohen20 Jan
      • RE: Organisational question: surely someone has implemented many Boolean values (tags) and a solution existJerry Schwartz20 Jan
        • Re: [PHP] RE: Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDavid Hutto20 Jan
        • Re: [PHP] RE: Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDotan Cohen20 Jan
          • RE: [PHP] RE: Organisational question: surely someone has implemented many Boolean values (tags) and a solution existJerry Schwartz20 Jan
  • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existRichard Quadling20 Jan
    • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDotan Cohen20 Jan
      • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDotan Cohen20 Jan
      • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existRichard Quadling20 Jan
        • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDavid Harkness20 Jan
          • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDavid Hutto20 Jan
            • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDotan Cohen20 Jan
              • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDavid Hutto20 Jan
                • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDotan Cohen20 Jan
                  • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDavid Hutto20 Jan
                    • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDotan Cohen21 Jan
        • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDotan Cohen20 Jan
          • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existRichard Quadling21 Jan
            • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDotan Cohen21 Jan
    • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDavid Harkness20 Jan
      • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDotan Cohen20 Jan
        • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDavid Harkness20 Jan
          • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDotan Cohen21 Jan
            • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDavid Harkness21 Jan
        • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existPeter Brawley20 Jan
          • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDotan Cohen21 Jan
    • Re: [PHP] Organisational question: surely someone has implementedmany Boolean values (tags) and a solution existDavid Harkness20 Jan