Displaying a category hierarchy

[MarkB423]

I have two options to figure out the "hierarchy" of products categories in a shopping cart:

1) Store only the Category ID, Category Name, and Parent Category (ID) field in a table, and some how try to figure out the hierarchy with as few queries as possible, OR

2) Store Category ID, Category Name, Parent Category (ID), and Hierarchy (a comma delimited list of this category's Parent IDs).

I am learning towards option 2, but please fill me in with what method is more efficient, and also if you know of a better way. Thanks!

[nemonoman]

You should DEFINITELY go with option 1.

Option 2 is a maintenance nightmare and discouraged by most people familiar with relational database principles.

[MarkB423]

About how many queries is acceptable for Option 1?

[jweissig]

how many parent, categories, and sub categories are we talking about here.. can you give me an example please.

• Justin

[nemonoman]

You will have to use as many selects as necessary, i.e., until you come to a category which has no parent category.

If you KNOW the number of subcategories -- like it's always

category
subcat
subsubcat
product

then you can get the hierarchy by aliasing category

select a.categoryname, b.categoryname, c.categoryname, from category a, category b, category c

etc.

for as many sub categories as you have

If you don't know the number for subcategories, you 'll have to use a while loop that loops through selecting parent categories until it finds the top-level category. This should not take long if you've indexed the ids, which is likely you've done.

[MarkB423]

yeah, I was afraid of that (a select query inside a loop), but it appears to be the only way without selecting the entire category table, as there can be an unlimited number of subcategories.

Thanks (and yes, there will be an index on the IDs)