Database Design for tags/tags

The tag/tag system can improve the intelligent matching degree of information for a system without talking about semantic networks. The network bookmarks delicious, and the tag system is doing well, through user tags and popular tags, people can easily find information of interest and organize information conveniently. Then how does the database system design such tag tags?

Phillips Keller proposed four solutions and tested MySQL performance:

Basically, these four solutions are:

1. A table contains a tags field, which stores tags separated by spaces or commas. The disadvantage is that the length is limited, the number of tags is limited, and the query like '% ABC %' is inefficient.

2. Same as above, full-text indexing is supported, or query using the CE Index

3. Two tables. The second is the tags table, which stores the foreign key and Tag Name of the first table. The number of instances in the first solution is limited.

4. three tables, the second tags table, and the third joined table, typical 3nf, the most common design. The tag supports a tree level. The disadvantage is that the joined table will be very large if the data volume is tens of millions!

There are also the fifth and sixth solutions, such as adding a cache layer, caching a query for "taga + tagb" for an hour, and adding redundant columns, such as taga, taganumber (number of content ), tagb, tagbnumber...