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 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.