[Brug-talk] How to efficiently implement tags in Ruby on Rails + mysql

[Peter Vandenabeele]

Hi,

I would appreciate some hints or pointers on this problem.

I need to "tag" a number of items where I have defined
different groups of tags. From each group op tags, 0, 1
or more tags can be relevant and need to match in a
search.

So, I see this as "bitmap" tags, where each combination of tags
is possible and where upon search each combination of search
criteria for the tags is possible (and any combination matching on
at least one tag per group needs to returned by the query).

The cleanly normalized database design seems to be to add 2\
extra tables:
* 1 table: tags
* 1 association table: jobs-tags

But, the query then becomes a complex join of 3 tables with a
complex "where" clause testing for each individual tag that
needs to be in the search result. Maybe bitmap indexes can
help or even remove the performance problem ?

One solution that I see with MySQL is to use the Mysql SET
datatype (a bitmap datatype):

  http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html

(seems near perfect for my application ?). Is this really useful? Is it
supported in Rails?

I assume this is actually a quite generic problem (e.g. this article

  http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html

speculates on del.icio.us, but I did not exactly understand from it
how del.icio.us now resolves this problem ?)

This presentation about Flickr:

  http://www.niallkennedy.com/blog/uploads/flickr_php.pdf

seems to be a little more specific. Fun quote:
"normalised data is for sissies".

They refer to "full text indexing" in mySQL as a possible solution.

I am quite sure this problem of tagging has been resolved already
many times, maybe also with Ruby-on-Rails solutions?

I would be very glad to receive hints and pointers about solutions
for this problem, particularly in the combination of Rails and mySQL.

Thanks,

Peter