3 efficient Tags tag system database design solutions, tags design solutions

3 efficient Tags tag system database design solutions, tags design solutions

Background

Currently, mainstream blog systems and CMS all have a TAG Tag system, which not only enhances the structure of content links, but also allows articles to be distinguished by tags. Compared with the traditional Keyword mode, this Tag mode can independently design a Map ing table to increase the system load and query efficiency.

Database Design Scheme 1

This solution is divided into two tables:

1. Tag table
2. Document Table

Tag table structure:
Copy codeThe Code is as follows:
Tagid # ID of the tag
Tagname # tag content
Num # number of references to the current Tag

Article table structure:
Copy codeThe Code is as follows:
ID # Article ID
Title # article title
Tags # tags list, separated by commas
Tagid # The tags IDs are separated by commas (,).
...

In this way, the main content of Tag tags is stored in the article table. The pressure on the Tag table is small, but the number of Tag references is updated when the Tag is added, but the query efficiency is insufficient, not a good solution

Database Design Scheme 2

The second solution uses two Tag tables, one of which stores Tag information and the other stores ing information:

Tag table:
Copy codeThe Code is as follows:
Tagid # ID of the tag
Tagname # tag content
Num # number of references to the current Tag

Tagmap table
Copy codeThe Code is as follows:
Tagid
Aid

Article table
Copy codeThe Code is as follows:
ID # Article ID
Title # article title
Tags # tags list, separated by commas
...

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

When querying, You need to search for the response Article ID from the Tagmap table, and then use the Article ID to query the specific article information. Because each query uses an index, the efficiency is high.

Database Design Scheme 3

The first two solutions are designed with pure Mysql. The third solution will be designed with the charm of Nosql.

The basic structure is the same as solution 2. Only nosql database servers such as mongo/redis are used in the Tag table and Tagmap table, so that the powerful linear query capability of nosql databases can be used.

1) The first method of table structure design is exactly the same as solution 2, but the database server has changed.

2) Other solutions, of course, use Nosql's linear capabilities to design the storage Key. Especially when using redis, the Key structure can perfectly improve the query efficiency.

MSSQL2005: How is the design of Tags data tables more reasonable and efficient?

The Design of table structure is nothing bad
However, you can consider the index design.
For example
1. The clustered index of the t_Tags_SpaceArticle table can be deleted to retain the unique constraint.
When data is inserted and updated, the data is adjusted in the order of clustered indexes, resulting in low efficiency.
2. Create an index for the ArticleId field. Because you need to search its Tag by log, you need this index.
3. Create an index for the TagId field, because you need to search for its related logs by Tag 2, so this index is required.

Basically, the above three steps can solve your problem. If you want to improve efficiency, You need to partition the t_Tags_SpaceArticle table. partitions can be calculated by TagId and then partitioned.

Label table design and database design

Are you sure you want to clarify the problem?