[thelist] PHP, MySQL - category counter puzzle

[rudy]

>... 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.
>- 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.

hi quinn

it's a good idea

as you have described, this requires that you update the counters all the
way up the category tree whenever any single product changes

you balance the performance hit of this update process against the
performance hit of querying down the tree when deciding whether to print a
category

most databases share the characteristic that they are queried far more
often than updated, so overall, your idea makes sense

in fact, as discussed recently on "thesite" (one of evolt's other lists),
the evolt design has exactly this structure on the database record that
holds articles -- there's a column which contains the number of comments
that have been made on the article, and this column is updated when another
comment is made, but it is queried way more often, e.g. on the evolt home
page, so it is a good tradeoff


an alternative to a counter might be a simple flag -- yes/no this category
has products (somewhere) under it, and if yes, you will count them as you
go down exploding the tree on display anyway

this will make updating a bit faster, and another way to do that is to also
keep an extra column "topcategory" in each category record which points to
the top category in that branch, so that rather than recursively going up
the tree to update the flag, you can do them all in one update staement,
e.g.

    update category
       set productflag=1
         where topcategory=[thisbranch]


helps?

rudy