Is storing counts of database record redundant?

To answer the title question. Yes it is redundant, but whether you should do it depends on your situation.

Unless you have known performance problems, calculate the counts and totals on the fly in your application and don't store them. That is, don't store calculated values unless you have no other choice.

In most situations, you wont have to resort to this and shouldn't.

If you must store calculated values, do the following:

• Don't keep it up-to date by incrementing it. Recalculate the count/total from all the data each time you update it.
• If you don't have a lot of updates,put the code in an update trigger tokeep the count/totals up to date.
• The trouble with redundancy indatabases is that when the numbersdisagree, you are unsure of which isauthoritative. Add to thedocumentation a note that the sourcedata is the authoritative source ifthey disagree and can be overwritten.

While it depends on the size of your database, these are the kinds of operations that databases specialize in, so they should be fast. It's probably a case of premature optimization here - you should start by not storing the totals, thus making it simpler - and optimize later if necessary.

Remember the maxim "A man with one watch always knows the time. A man with two watches is never sure." I would only store the derived number if:

Performance issues stop you from getting the derived numbers when you need them (which should not be a problem in this case since the answer is likely to be available from the indexes)

or

You have reason to believe that you are losing records from the main table through programmer error or deliberate or accidental user action. In that case, you can use your the derived number to audit the currently calculated number.