Ways to implement tags - pros and cons of each

Question

Related

Using SO as an example, what is the most sensible way to manage tags if you anticipate they will change often?

Way 1: Seriously denormalized (comma delimited)

table posts
+--------+-----------------+ 
| postId | tags            |
+--------+-----------------+
|   1    | c++,search,code |

Here tags are comma delimited.

Pros: Tags are retrieved at once with a single select query. Updating tags is simple. Easy and cheap to update.

Cons: Extra parsing on tag retrieval, difficult to count how many posts use which tags.

(alternatively, if limited to something like 5 tags)

table posts
+--------+-------+-------+-------+-------+-------+
| postId | tag_1 | tag_2 | tag_3 | tag_4 | tag_5 |
+--------+-------+-------+-------+-------+-------+
|   1    | c++   |search | code  |       |       | 

Way 2: "Slightly normalized" (separate table, no intersection)

table posts
+--------+-------------------+
| postId | title             |
+--------+-------------------+
|   1    | How do u tag?     |

table taggings
+--------+---------+
| postId | tagName |
+--------+---------+
|   1    | C++     |
|   1    | search  |

Pros: Easy to see tag counts (count(*) from taggings where tagName='C++').

Cons: tagName will likely be repeated many, many times.

Way 3: The cool kid's (normalized with intersection table)

table posts
+--------+---------------------------------------+
| postId | title                                 |
+--------+---------------------------------------+
|   1    | Why is a raven like a writing desk?   |

table tags
+--------+---------+
| tagId  | tagName |
+--------+---------+
|   1    | C++     |
|   2    | search  |
|   3    | foofle  |

table taggings
+--------+---------+
| postId | tagId   |
+--------+---------+
|   1    | 1       |
|   1    | 2       |
|   1    | 3       |

Pros:

• No repeating tag names.
• More girls will like you.

Cons: More expensive to change tags than way #1.

Answer 1

These solutions are called mysqlicious, scuttle and toxi.

This article compares benefits and drawbacks of each.

Answer 2

I personally favour solution #3.

I don't agree that solution #1 is easier to mantain. Think of the situation where you have to change the name of a tag.

Solution #1:

UPDATE posts SET tag = REPLACE(tag, "oldname", "newname") WHERE tag LIKE("%oldname%")

Solution #3:

UPDATE tags SET tag = "newname" WHERE tag = "oldname" 

The first one is way heavier.

Also you have to deal with the commas when deleting tags (OK, it's easily done but still, more difficult that just deleting one line in the taggings table)

As for solution #2... is neither fish nor fowl

Answer 3

I would argue that there is a fourth solution which is a variation on your third solution:

Create Table Posts
(
    id ...
    , title ...
)
Create Table Tags
(
    name varchar(30) not null primary key
    , ...
)

Create Table PostTags
(
    PostId ...
    , TagName varchar(30) not null
    , Constraint FK_PostTags_Posts
        Foreign Key ( PostId )
        References Posts( Id )
    , Constraint FK_PostTags_Tags
        Foreign Key ( TagName )
        References Tags( Name )
        On Update Cascade
        On Delete Cascade
)

Notice that I'm using the tag name as the primary key of the Tags table. In this way, you can filter on certain tags without the extra join to the Tags table itself. In addition, if you change a tag name, it will update the names in the PostTags table. If changing a tag name is a rare occurrence, then this shouldn't be a problem. If changing a tag name is a common occurrence, then I would go with your third solution where you use a surrogate key to reference the tag.

Answer 4

I think that SO uses solution #1. I'd go with either #1 or #3.

One thing to consider is if you have several thing that you can tag (e.g. adding tags to both post and products, for example). This may affect database solution.