If I have a user table and a post table, and I want to count how many posts a user has posted today, I can think of two ways.
The first is to send a SQL query to count how many post records that belong to a user is created today.
The second is to have a count table, that has three columns: user_id, num_posts, and date, so that when a user makes a post, num_posts is incremented if date is today, or simply set to 1, and when we need to count posts, we simple retrieve num_posts if date is today, or say 0.
It seems to me that both methods are practical, but I'm not a DB expert without much knowledge in how DB works in the backend, so I'm not sure which route to take (though it seems most people are implemented the first one?). Can anyone give me some idea about it?
The first one should be fine if the number of users and posts in your system are not too many. But if there are too many users and posts, you may want to consider for the second option, as it will be faster. The drawback is that it will require more space to store the data.