Database design of article classification and labeling

In almost all Web projects, the article classification and label design, it should be said that this is a more common, typical case. Stationmaster does not guarantee my train of thought is the best, just shares out everybody exchanges together, mutually promotes and enhances.

We assume that the development project is a blog system, the core part is related to the article, then we today discuss how to design the blog system article classification and tags.

1, first, the classification and labeling are to be related to specific articles, of course, there may be some articles are neither classified nor tagged, this is the time we write the query is easy to neglect the place. Because our first feeling is that when querying the list of articles related to the classification table, to find out all the articles and categories, the corresponding relationship is generally the classification ID of the article table corresponding to the ID of the classification table, using the WHERE clause to qualify. There is a problem here, because the use of the WHERE clause, then can only query the article is classified, and no classification of the article can not be queried. What happens now? You should use connection queries, left joins, which do not have classified articles, which will show null in the article Category ID column. Usually we use only the left join and seldom use the right join.

2, generally, an article best only corresponds to a classification, of course, if you want to correspond to multiple categories can also. But the webmaster does not advocate, the article in a number of categories repeat will give people very unprofessional feeling, even if some articles may indeed design to many aspects of the content, then you should focus on the classification. and the label is different, an article may have multiple tags. This means that we cannot rely on an SQL statement to find out the classification and labeling of all the articles, and to make the article IDs in the query results non-repeatable. Usually we need to directly loop the results of the query, then this result is usually a two-dimensional array, the second dimension is the only one that stores the relevant information of an article. However, the label and the article is a many-to-one relationship, multiple tags corresponding to an article, if you only use a SQL statement, then we query the results, of course, is more than the line, which does not meet the requirements of our target data. In this, you need to query the end of the article and classification, on the basis of the previous results to query the article label, the results of two times together, there are arrays, this is the corresponding article List page Query method. For the specific article page, can be divided into two times query.

Well, has not given the specific database design, first said how to query the results, I believe we also look bored, the following examples illustrate:

First, the article table: Post, the fields are as follows:

ID "unique ID", aid "author id", title "title", Content "contents", CID "category ID"

Second, the classification table, category, the fields are as follows:

ID "Unique identifier, associated with the CID of the Post table", name "category name"

Three, tag table, tag, the fields are as follows:

ID "unique ID", name "label name"

Four, the label and the article Correspondence relation table, tag_relationship, the field is as follows:

ID "Unique ID", PostID "article ID, associated with ID of post table", TagID "label id,tag Table ID Association"

A friend may ask: Why do you want to use a single table to store the relationship between the article and the label, why not directly in the tag table to add an article ID field, such as:

Tag table:

Id,postid,name

This is not a good thing to do, however, because an article corresponds to multiple tags, so the value of the Name field is duplicated, such as an article, assuming that the article ID is 1, there are 2 tags, php and mysql, then the tag table is stored like this:

id:1,postid:1,name:php

Id2,postid:1,name:mysql

Another article, assuming ID 2, has 2 tags, is also PHP and MySQL, then it will be stored in the tag table:

id:3,postid:2,name:php

Id4,postid:2,name:mysql

People soon discovered the problem, so the design name field, the name of the label, may be duplicated in the same table. But the advantage of this design is that if you want to query how many articles under a tag, as long as you check the table alone, such as to query the number of articles containing PHP tags, only the select count (name) from the tag where name= ' php ', We can find out. The downside is that if you want to query a collection of all the labels, using this design requires the group by name statement to remove the duplicate rows. If you use the previous type, only select * from tag is required. In the moment, it seems that the choice is not good. Both designs will have data graves, the first type of tag_relationship table, there is the repetition of the TagID field, and both of these designs have their own advantages. So how do we choose? Stationmaster also said not good, therefore cannot draw a conclusion for everybody. But the webmaster in the study of WordPress data structure, found that WP is a separate table used to store the relationship between the article and the way the label.

In addition, how to design sometimes depends on the needs of specific functions, so this question is left to discuss it with you ~

Database design of article classification and labeling