The article list is based on the latest 3 comments of each article. How can we design it?

The article list is based on the latest 3 comments of each article. How can we design it? The {code...} article list contains the latest 3 comments of each article. How can we design it?

Article table {article_id, title, content} comment table {article_id, comment, created (timestamp )}

Reply content:

The article list is based on the latest 3 comments of each article. How can we design it?

Article table {article_id, title, content} comment table {article_id, comment, created (timestamp )}

If you have already provided the design of a single database table, this is the most intuitive design!

• If the current design can well meet your needs and work well, there is no need to refactor for reasons such as easy scalability and performance. Simple and intuitive, and can meet the current needs, do not blindly over-design, each time a comment is displayed, it will be associated with the query, and thenorder by createdAnd then take the first three records;
• If you cannot meet your requirements (most of them are performance requirements), you need to analyze where the performance bottleneck is. I guess: Generally, the bottleneck is the acquisition of the latest three comments when the data volume is large. For this performance hotspot, I would like to give you some personal opinions. You are welcome to make a picture:
  1. Add an index in the simplest way.(article_id, created), Can greatly improve the SQL query efficiency and basically solve the problem;
  2. In addition, you can add a column to the article table to store the latest 3 comments.(comment_id, created)This method not only facilitates the update of new comments, but also directly queries the latest three comments based on the primary key id. However, this method increases the overhead during comments insertion and requires a balance;
  3. In addition, you can use the cache to put the latest three comments into the cache. Of course, it is best to put them in the memory cache. If there is a large amount of data, you can consider a distributed cache similar to memcache, this can also greatly reduce the database query pressure;
  4. In the end, simply using nosql databases, such as mongodb and redis, can easily meet such business scenarios, but this brings new learning costs.

The above is just a personal statement. For most such business scenarios, handle can be used when the data volume is not very large, similar to the table structure of the landlord, and an effective index.