Shulou(Shulou.com)06/02 Report--

This article mainly explains "database design to achieve comment reply function," interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let Xiaobian take you to learn "database design to realize comment reply function"!

1. Overview

Comments have become a must-have feature in APP and website development. This paper mainly introduces the database design of comment function.

The main comment function is to post comments and reply to comments (delete function in the background). The expansion function of the comment function reflects the following aspects:

(1) the number of comments and information display of a single article;

(2) Display dynamic user comment information from time dimension according to time flashback;

(3) Different columns, different modules and different time dimensions of comment ranking display;

(4) Individual recommendation and aggregate display of elite comments;

(5) Comments are shared directly to bound third-party platforms;

(6) Ranking of dimensions such as number of likes and number of replies.

Background management of comments:

(1) deletion;

(2) recommendation;

(3) essence;

(4) shielding, sensitive keyword library perfect, automatic shielding or replacement function.

This article focuses on several client review data table designs.

2. Data sheet design

2.1 Question and answer mode

(1)demand analysis

Most apps can be designed with simple comments, that is, a question-and-answer mode, such as the design of the comment function of WeChat circle of friends. For example:

A: What a beautiful day! B @ A : The weather is really good today!

This design is simple and direct, and also meets the basic requirements of user comments and replies, which is sufficient for APP needs without a large number of user comments.

(2) Database design

In this scenario, there are generally few comments and comments are not active. Comments and replies can be regarded as comments without distinction. The difference is that some comments are direct comments on the topic, while some are @ other users, using a table can achieve the effect, the comment table design is as follows:

topic_type: In order to reuse the comment module, we introduce this field to distinguish the category of the topic.

from_uid: ID of the reviewer, through which we can retrieve information about the reviewer.

to_uid is the id of the comment target, if there is no target, this field is empty

For performance reasons, we tend to redundantly add relevant information to the comment table, such as the reviewer's nick and avatar, as well as the target user. In this way, we can achieve the display effect only by querying a single table.

Sometimes, there are multiple target users, so you can modify the to_uid field to_uids. When saving, use separators to divide the user id, and then query the cache or database for the information of the target user. You can also simply store multiple target users 'information together in json format, which can meet simple presentation requirements.

2.2 Comment-based mode

(1)demand analysis

If the comment based display mode is similar to the comment display mode of CSDN below:

Comments are divided into comments and replies, and all comments are hung below the comments, similar to a tree structure.

(2)database design

In the case of tree display based on comments, the design of the database is very flexible. You can use a single table and add a parent_id field to point to the parent comment, which requires nested queries.

Comments can also be split into a comment table and a reply table, with comments hanging under various topics and replies hanging under comments.

The comment sheet is designed as follows:

Response Form Design:

Since we split comments and replies, the comment table no longer needs the target user field, because comments are user comments on topics, and the design of the comment table is better and simpler.

A comment_id field is added to the reply table to indicate the root comment id of the reply. This design is also for performance considerations. We can directly find all the replies under the comment at one time by comment id, and then arrange the display structure of the reply through the program. Improving performance through appropriate redundancy is also one of the common optimization methods.

reply_type: indicates the type of reply, because the reply can be a reply to the comment (comment) or a reply to the reply (reply). This field distinguishes the two scenarios.

reply_id: ID of the reply target, if reply_type is comment, then reply_id=commit_id, if reply_type is reply, this indicates the parent reply of this reply.

2.3 Netease News Building Mode

(1) Demand analysis

In this scenario, comments and replies are displayed at the same level, and replies are not displayed on the display structure without hanging below a comment. The dual-table design is not appropriate here, because it involves mixing comments and replies, and using dual-tables will lead to too complicated query logic. Therefore, it is recommended to adopt a single-table design, not distinguishing between comments and replies will simplify the logic of the application layer. We treat them all as comments, and some comments can quote other comments.

(2) Database design

I recommend using closure table designs, such as:

parent_child table:

The comment table holds all comments, while the parent_children table records the parent-child relationship of each comment in the comment table.

Query is often sorted by time, we can directly by id or create time in descending order to query the comment table. If the user wants to query the complete reference of a comment, they can find the corresponding path through parent_children.

Closure tables are convenient for querying, but insertion performance is a bit worse, because in addition to inserting the comment table, you need to insert all the parent-child relationships of the comment into the parent-child table. Insertion performance decreases linearly as the comment hierarchy deepens.

3. Database optimization

If your system has thousands of comments every day, then the design of a single table is definitely not good. There are several ways to optimize it.

(1) Sub-warehouse and sub-table. Sub-library and sub-table is the most common and effective optimization method. It is recommended to sub-library and sub-table according to theme. In this way, comments under the same topic will fall into the same table, avoiding cross-table queries.

(2) Appropriate data redundancy. If you need to display information about the reviewer, put that information in the comment table when you insert comments to avoid multiple queries. In fact, if it is recorded data, the corresponding data information can be redundant, because the real-time line and consistency requirements of their data are not high.

(3) Additional power. Data only allows single operations. Because in terms of powerfulness, each like is a record. If the number of comments is counted from the likes table, then the performance overhead will be very large, and such a lightweight operation will definitely aggravate the competitive operation of the likes table. Therefore, it is recommended to add a counter like_count directly to the comment table. This field only increases and does not decrease. The client can set the cancellation effect.

(4) Hot comments plus cache. Similar to Netease news popular comments, read frequency is very high, you can specifically open the interface to do cache.

At this point, I believe that everyone has a deeper understanding of "database design to achieve comment reply function", so it is advisable to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.