Article list take the latest 3 reviews of each article, how do you design?
article表{article_id,title,content}comment表{article_id,comment,created(时间戳)}
Reply content:
Article list take the latest 3 reviews of each article, how do you design?
article表{article_id,title,content}comment表{article_id,comment,created(时间戳)}
If the single-argument database table design, you have already given out, this is the most intuitive design!
- If the present design can meet your needs well, the work is very good, there is no need for the so-called easy to expand, performance and other reasons to refactor. Simple and intuitive and can meet the current needs, do not blindly to over-design, each time the comment is displayed, the honest relevance of the query, and then
order by created
take the first 3 records;
- If you can't meet the requirements (mostly performance requirements), then you need to analyze the performance bottlenecks there. Individuals first guess: the general bottleneck is the amount of data in a very large time, the latest 3 reviews of the acquisition. For this performance hotspot, simply give a personal view, welcome to shoot Bricks:
1, the simplest way, plus index (article_id, created)
, can be good to increase the efficiency of SQL query, the basic can solve the problem;
2, in addition, you can add a column in the article table, storage of the latest 3 comments, this information can be based on the (comment_id, created)
combination, so as to facilitate the insertion of new comments when the update, but also based on the primary key ID directly query the latest 3 comments, but this way, increase the cost of inserting comments, need to weigh;
3, in addition, you can use the cache, the latest 3 comments into the cache, of course, it is best to be able to put in memory cache, if the data is large, you can consider a similar memcache distributed cache, which can also greatly reduce the database query pressure;
4, finally, a little bit, directly on the NoSQL database, like MongoDB, Redis and other NoSQL database can easily meet such a business scenario, but this brings new learning costs.
The above is only a personal opinion, for most of these business scenarios, when the amount of data is not particularly large, like the landlord of the table structure, plus a valid index, fully able to handle.