Search code examples
database-designrdbms

How to Implement a Tagging System for multiple tables in an RDBMS?

Pretend you have an arbitrary number tables that you wish to allow users to attach a tag to. For example, Stackoverflow lets us tag a question, but perhaps it could also let us tag an answer, a job, our user profiles, and etc.

What is the best way to implement it, and what information is required to make this decision?

Create a M:N between each table to tag and the tag table

 +----<tag_foo>----foo
 |
tag
 |
 +----<tag_bar>----bar

Find all Foos with a tag = 'rdbms'

SELECT foo.*
FROM tag JOIN tag_foo ON (tag.id = tag_foo.tag_id)
         JOIN foo ON (tag_foo.foo_id = foo.id)
WHERE tag.name = 'rdbms';

Use Class Table Inheritance

In this method, we make a taggable table, and each table to tag has a FK back to taggable and is 1:1 with taggable. The tag table has a M:N with `taggable.

                            +-------foo
                            |
tag----<tag_taggable>----taggable
                            |
                            +-------bar

Find all Foos with a tag = 'rbdms'

SELECT foo.*
FROM tag JOIN tag_taggable ON (tag.id = tag_taggable.id)
         JOIN taggable ON (tag_taggable.taggable_id = taggable.id)
         JOIN foo ON (taggable.id = foo.id)
WHERE tag = 'rdbms';

Here is the CREATE TABLE for taggable:

CREATE TABLE taggable (
    id INTEGER PRIMARY KEY,
    type VARCHAR NOT NULL CHECK (type IN ('foo', 'bar'))
)
Solution

  • I would go with the first option. The second option will force you to use the same column as a primary key for all taggable tables, and will force you to insert a record to the taggable table for each record inserted into foo or bar. This will make the insert more cumbersome and have no real benefit over the many to many tables. Sure, it's less tables in the system, but more work for each record.

    A 1:1 relationship is useful when you need to store muliple items with different attributes, but all share some common attributes. for example, Animals related to cats, dogs, lions and zebras.

    It's less useful in a tagging system, since the only thing all taggable entities must have in common is that they are taggable.