best design?

[jonniejoejonson]

This is a simplified idea of what im trying to do...

I would like to know which is better design for a large database driven website...

 

I have a table: media_folders

 

I have another table: media_in_folders

 

If i want to select all the media folders and the number of media in each of the folder...

would it be more sensible to..

 

a: Have a field 'number_of_media' in the media_folders table, that would need updating every time media was added or removed to a folder.

 

b: Simply do a join on the tables and COUNT the number of media in media_in_folder on the mediaId.

 

Query 'a' would obvioulsy execute quicker, but it requires a lot more effort, to update the 'media_folders'table when you added or deleted media...

Is it simply a matter of preference? or would you go for option 'a' if you were doing a large database driven website?

 

kind regards to any responders.

[gizmola]

The preferred answer is to do a count(*) query, using an in-memory cache like memcache to store the cache.  You can then decide how you want to invalidate the cache.  You could invalidate based on a period of time or invalidate every time there's an addition or deletion from the media_in_folders table for a particular media_folders id.  If this is an extremely high-traffic high change velocity site, it is probably better to invalidate based on a time period.  In other words, you only want to spend the time to invalidate once every half an hour or whatever makes sense for your site.  If you want more accuracy, then your routines that add or delete from media_in_folders should invalidate the cache, which will cause it to be repopulate with a new count(*) query.