I would like to ask everyone, when writing an article in the forum, there will be a function to add tags to the article. How should this database be designed?

I have thought that there is an article table, and the tags entered by the user cannot be controlled. In this case, there is no way to manage the tags. For example, if I want to use tags to make a conditional query, it will not work.

Reply content:

I have thought that there is an article table, and the tags entered by the user cannot be controlled. In this case, there is no way to manage the tags. For example, if I want to use tags to make a conditional query, it will not work.

Create a tag table. The tag table contains tag ID, Name, etc. Create a correlation table, then save ArticleID, TagID, and then extract the tag when entering or crawling the article. First determine whether the tag table has already obtained tags, update if not, then store the tag ID in the association table, and then add the article ID. When the time comes to update, it can be taken out directly through the association table, so that it can be displayed successfully.

There are three major paradigms in database design,
1. The fields in the database are atomic and cannot be split. The tags seem to be consistent, but the tags also have separate attributes for the tags. It is inconvenient if the list needs to be filtered out separately based on the tags later.

CREATE TABLE `user_label` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '类型',
  `lable_name` varchar(255) NOT NULL COMMENT '标签名称',
  `user_id` int(11) NOT NULL COMMENT '创建者',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='标签表';

The

tags are placed in a separate data table, and the post data table is placed in a tags field, separated by |. The ‘|’ character is not allowed in the tags.

Create a table to store tags. Add a field to the article table to store the tag id. When the user adds a tag, check whether the tag is duplicated and store it in the tag table. Then update the tag field in the article table

Two tables

• Create a tag table

• An article tag association table, the fields are article ID and tag ID
  In this way, when querying, you only need to query the article ID corresponding to the tag in the association table.

This should be a many-to-many relationship in database design. Design three tables: article table tags - article association table tag table. Set dual primary keys in the tag article table, which are the IDs of the tag table and the article table. You can query them Use the join statement to directly filter out the desired results. There is another way to use json to store tag data in the new mysql5.7. This can avoid setting up multiple tables. If the answer is wrong, please point it out. If you are satisfied, please give me a thumbs up. Thank you

I think @ivanilla is right, this table design is almost usable. There is no need to query tag fields associated with multiple tables, and it is simple to use.

I happened to have paper on hand, so I drew a simple E-R diagram

I don’t know if it’s better not to step on it randomly. Although my answer didn’t directly help him solve it, it is still a very good idea. Idiot, I took a detour, thank you