like stackoverflow questions tags,how to design it in databases?

Question

i has 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
1  tag2
1  tag3 

i think plan 3 is better,this's right?

any other good idea?

thanks for help :)

Answer 1

Depends on how normalized you want your data to be.

Firstly, I cringe when I see an "id" column in a table that isn't unique. At least rename the column to "question_id".

Secondly, it depends on whether you want a quick listing of all tags defined. In which, case, you'd want a separate tag table defining the set of possible tags, and then an intermediate table between questions and tags that provided a many-to-many association.

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)

Answer 3

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.

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 4

The correct approach is to create the one-many relations, that is you have one comment and multiple tags. From WIKI

In database technology, a one-to-many (also known as to-many) relationships occurs when one entity is related to many occurrences in another entity. For example, one club has many members.

And the main concept in the database design is the Database normalization.

So I'd do it like this.

comments
------------------------------------
id_comment title  content 
------------------------------------
12         aaa     bbb

tags
------------------------------------
id_tag comment_id tag 
------------------------------------
1      12         tag1
2      12         tag2
3      12         tag3