How to design the schema for something like StackOverflow questions tags?

Question

I have 3 plans:

1, in questions table:

question
------------------------------------
id title   content  ...       tags
------------------------------------
1  aaa     bbb      ...       tag1,tag2,tag3 (use , to split more tags)  

2, in tags table and split:

tags
------------------------------------
id tag 
------------------------------------
1  tag1,tag2,tag3  (use , to split more tags)

3, in tags table:

tags
------------------------------------
id tag 
------------------------------------
1  tag1
2  tag2
3  tag3 

I think that plan 3 is better, but what's your opinion?

Any other good ideas for this implementation?

Thanks for the help :)

Answer 1

These patterns are called mysqlicious, scuttle and toxi (from the least to the most normalized).

They all have their benefits and drawbacks. You can read quite a good analysis here:

http://forge.mysql.com/wiki/TagSchema (WayBackMachine Version)

Note that mysqlicious heavily depends on your database's ability to perform FULLTEXT searches efficiently.

This means that for MySQL with InnoDB and for some other systems it's very impractical.

Answer 2

The relationship between tags and content is many-to-many. What this means is that one tag can be associated with several units of content, and one unit of content can be associated with several tags.

To implement this in a database, you can use an auxiliary table called ContentTags. The relationship of Content to ContentTags is one-to-many; the relationship of Tags to ContentTags is one-to-many.

#Tags Table
Id   Text
 1  'Tag1'
 2  'Tag2'
 3  'Tag3'


#Content Table
Id   Content
 1   "some content"
 2   "other content"
 3   "more content"

#ContenTags Table
ContentId TagId
1         1
1         2
2         1
2         2
2         3
3         1

As you can see, the relationship is clearly reflected (content 1 is associated with tags 1 and 2; content 2 is associated with tags 1, 2, and 3; content 3 is only associated with tag 1)