List:General Discussion« Previous MessageNext Message »
From:Dotan Cohen Date:January 20 2011 2:32pm
Subject:Organisational question: surely someone has implemented many Boolean
values (tags) and a solution exist
View as plain text  
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
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