3 kinds of efficient tags tag system database design scheme sharing

3 kinds of efficient tags tag system database design scheme sharing

Requirements background

At present, the mainstream blog system, CMS will have a tag tag system, not only can make the content link structure enhancement, and can let the article according to tag to differentiate. Compared to the traditional old-fashioned keyword model, this tag mode can be designed separately to map the mapping table to increase the system load and query efficiency.

Database Design Scenario 1

This scenario consists of 2 tables:

1.Tag table
2. Article table

TAG table structure:

The code is as follows:
ID of the TagID # tag tag
TagName #tag内容
Num #当前Tag的引用个数
Article Table structure:
The code is as follows:
ID #文章ID
Title #文章标题
Tags #tags列表, multiple to, split
TagID #tags的ID multiple, split
...

This way the main content of tag tag is saved in the article table, for the tag table pressure is small, just add the time to update the tag reference number, but when the query is inefficient, not a good way

Database Design Scenario 2

The second scenario uses 2 tag tables, one of which holds the tag information and the other holds the mapping information:

Tag table:

The code is as follows:
ID of the TagID # tag tag
TagName #tag内容
Num #当前Tag的引用个数
Tagmap table
The code is as follows:
TagID
Aid
Article table
The code is as follows:
ID #文章ID
Title #文章标题
Tags #tags列表, multiple to, split
...

In this form, the tag table and the Tagmap table are updated each time the content is published and the content is modified.

When querying, you need to find the article ID of the response from the Tagmap table, and then use the article ID to query the specific article information, because each query is indexed, so it is more efficient.

Database Design Scenario 3

The first two scenarios are designed using pure MySQL, and the third will be designed using the charm of nosql.

The basic structure is the same as scenario 2, which uses a NoSQL database server such as Mongo/redis in the tag table and the Tagmap table, which makes it possible to use the robust linear query capability of a NoSQL database.

1) The first way of table structure design and Scenario 2 is exactly the same, but the database server changed.

2) Other options, of course, are to play NoSQL's linear ability to design the stored key, especially when using Redis, the key structure can improve the query efficiency perfectly.

3 kinds of efficient tags tag system database design scheme sharing