SoundSoftware Code Site

Some notes on tag representation in the database:

http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html
http://www.pui.ch/phred/archives/2005/06/tagsystems-performance-tests.html
http://stackoverflow.com/questions/334183/what-is-the-most-efficient-way-to-store-tags-in-a-database
http://stackoverflow.com/questions/20856/how-do-you-recommend-implementing-tags-or-tagging/20871#20871

The third representation mentioned in the article at http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html (referred to as the "Toxi" schema) seems probably the most appropriate.

Note that this schema, which uses a separate tag-labels table and a join table to connect tags to objects, allows you to use the same tag vocabulary with more than one sort of object (e.g. with projects and issues) if you want to.

This suggests that we should take another look at the plugins that already exist for tagging, because even if a plugin has only been designed to permit tagging issues (say), it might still turn out to have a schema suitable for tagging other objects.

And indeed, https://github.com/ixti/redmine_tags/ appears to provide in the basic design for tags to be applied to objects of any model.

... using http://agilewebdevelopment.com/plugins/acts_as_taggable_on

Hm, one problem with a completely generic tagging implementation like this -- it has to record the taggable class in the db for every taggable-tag relation added.

e.g. I install the redmine_tags plugin and add two tags to one issue ("thin", "thick") and one tag to another ("thin"). The relevant db tables subsequently contain:

mysql> select * from tags;
+----+-------+
| id | name  |
+----+-------+
|  1 | thick |
|  2 | thin  |
+----+-------+
2 rows in set (0.00 sec)

mysql> select * from taggings;
+----+--------+-------------+-----------+-------------+---------------+---------+---------------------+
| id | tag_id | taggable_id | tagger_id | tagger_type | taggable_type | context | created_at          |
+----+--------+-------------+-----------+-------------+---------------+---------+---------------------+
|  1 |      1 |           4 |      NULL | NULL        | Issue         | tags    | 2011-08-15 15:10:28 |
|  2 |      2 |           4 |      NULL | NULL        | Issue         | tags    | 2011-08-15 15:10:28 |
|  3 |      2 |           3 |      NULL | NULL        | Issue         | tags    | 2011-08-15 15:16:22 |
+----+--------+-------------+-----------+-------------+---------------+---------+---------------------+
3 rows in set (0.00 sec)

That's a hell of a lot of data for three tags, far more than the alternative model in which we have a dedicated join table for each taggable type. That model would give us e.g. an issue_tags table containing something like

+----+----------+--------+
| id | issue_id | tag_id |
+----+----------+--------+
|  1 |        4 |      1 |
|  2 |        4 |      2 |
|  3 |        3 |      2 |
+----+----------+--------+

which is far smaller than the above, but which would require a separate table for each taggable type (i.e. statically limited set of taggables).