Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Possible Duplicate:
Storing vs calculating aggregate values

Say I have a table full of photos that each have an albumId that is a foreign key to an album table. To get the amount of photos in each album, I could simply do a Count() on the photo table where the albumId = the id from the album table. However, to accomplish this same task, I could keep a photoCount column in the album table. Is it worth keeping that extra column in the album table instead of using the Count() function?

share|improve this question
2  
See this: dba.stackexchange.com/q/239/2660 –  Nick Chammas Dec 21 '11 at 22:30
1  
And this: dba.stackexchange.com/a/5502/2660 –  Nick Chammas Dec 21 '11 at 22:31
    
The rule of thumb is "how accurate do you want the counts to be?" If you want them to be mostly right, storing is fine, and update via massive recalc once a week or so. If you want them to be exactly right, then calculate on the fly, every time. –  jcolebrand Dec 22 '11 at 2:41
add comment

marked as duplicate by jcolebrand Dec 22 '11 at 2:37

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers

You can also consider materialized views (not all RDMS support this feature though)

share|improve this answer
add comment

It depends on your application. If you use the number of your photos a lot, its better to keep the count so you don't have to run a query every time. But another things is if you add new photos with a high frequency, by storing the number of photos, you will have to update it every time.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.