[thelist] PHP, MySQL - category counter puzzle

[Quinn Comendant]

Hi guys,

This is a PHP and MySQL question. I'm working on a catalog for a web 
store with products organized in categories. I'm trying to find the best 
way of listing the total number of items within a category. This will 
also allow me to hide a category link if it contains no products or 
subcategories with products. One way would be to recursively drill 
through the category tree counting all the products found, but this 
massive recursion, occurring as many as 15 times per page, is 
unacceptable.

I would like your opinion on using the following method.

- A column in the categories table that would be responsible for 
containing a total count of all products contained in all the categories 
under it.

- When a product is added in its various categories, each of those 
category's counters would be incremented, as would their parent category, 
and their parent's parent category, etc.

- When a product is modified (the categories to which it is associated 
are changed), all the previous category counters are decremented, the old 
categories are dereferenced, then the new categories are referenced and 
their counters incremented.

- When a product is deleted, all the category counters are decremented 
and the categories are dereferenced.

- To find how many items a category contains, I need only query the 
counter for that category.

Does this make sense? Can anybody think of a better way?

I have the tables set up in 3rd normal form with: categories, products, 
and products_categories.

Thanks,
Quinn