Store row count in database or get dynamically

Question

Say I have a table full of comments, each from different users, and I want to count how many comments each user has. Should I have a separate table with the count, and update that on creation/deletion of comments, or should I query the count every time?

I feel like the latter is better, but I want some more experienced input on the matter. Thanks.

Answer 1

Following the good old YAGNI principle, I would suggest you go with the simplest solution for now, which is just counting the number of comments as needed. This is just pragmatic coding.

If, down the line, you find this is causing even small performance problems, then you should replace it with a cached value using a stored procedure or similar, but chances are it will serve you just fine.

So, I realise this probably isn't the clear answer you want, but: if you're making something small, go with the easy solution (counting); if you're making something bigger, go with the easy solution (counting) then upgrade to the harder solution (storing a value) if you find you need it. If you know what you're making is guaranteed to be big (lucky you!) then fine, go straight for the harder solution.

Note: I've said "harder solution" but as you probably know it's only fractionally harder than the easy solution.

Answer 2

After a commnent is created or deleted you should count right in the hour.

If you count every time you will make some desnecessary querys.