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

[Peter Brawley]

I'd exclude (1) because new tags require restructuring the table, (2)
and (3) because they break a cardinal rule of design and will be a mess
to query, leaving ...

4) Standard many-many bridge table:
mysql>  CREATE TABLE items_tags (
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   itemID int,
   tagID INT
);

Will not require a major overhaul if you later turn categories into a tree.

PB

-----

On 1/20/2011 8:32 AM, Dotan Cohen 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!
>