A while ago I read rudy's page on Categories and Subcategories, which was not only a big help with my photo gallery, but also with a few projects at work. But rudy's sample begged a question that I'm just now getting around to asking.

Consider the following:

id | cat_name | parent_id


Code SQL:
SELECT    parent.name AS parent
	, sub1.name AS sub1
	, sub2.name AS sub2
FROM categories
LEFT JOIN categories AS sub1
	ON sub1.parent_id = categories.id
LEFT JOIN categories AS sub2
	AS sub2.parent_id = sub1.id
WHERE parent.parent_id IS NULL
ORDER BY parent, sub1, sub3

This all works really well IF you know how many levels of subcategory you have. But what if this is an application where a user can create subcategories, and you have no idea how many levels deep they'll go?

Do you programatically limit how deep the user can go? Do you count the number of levels deep they've gone and alter the query to suit? Just curious!