Preferred approach to render number of tags associated with a certain entity?

Question

We have a requirement to tag internal assets (code artifacts ...) which are found within the intranet, so that it can be searched with ease later on.

We would like to show the count of assets tagged by a certain tag name (much similar to http://stackoverflow.com/tags). I would like to understand if this count is measured dynamically when a user visits this page or is it rendered from a tag_count table which contains information about the tag and the number of entries associated with it (Note: the assumption is that this tag_count table is updated on a periodic basis with the number of tags associated with an asset asynchronously).

What would be the preferred approach to solve the above problem, if the number of assets is in the range of 10's of thousands?

Answer 1

There is another option here...instead of a tag count table, a simple query that gets the tags/count and whatever application you're using to display the data caches that result for some period of time before re-fetching counts. Is this an option? In a web scenario this is the far more common approach.

Answer 2

I'd say: calculating it at the time of the request is the prefered way, because it is the exact/correct number.

BUT

It is likely that you will hit performance constraints with this approach. In this case using a seperate table is fine.

Depending on the rdbms in use you might have the option of materialized views, which might be able to give you the excact/correct result with acceptable performance.