one other thing that's been sticking me is categories. i'd like my category system to work the same as ebay's - items can have many categories, and when displaying an item the category tree (or several trees) leading to it is displayed in a clickable manner. does this mean a recursive query for every page hit? should i worry about the performance of that?

requisite category table desc:
- category_id (0 denotes root category)
- parent_id
- name

also, is there any merit in forcing all recipes to live in a leaf node of the category tree? such that you'd have recipes -> desserts -> cakes -> sponge cakes, and then "other cakes" in the cakes category to shove everything else into. for some reason i just dislike the idea of having a category contain both sub-categories and actual items, but i can't articulate the reason for wanting to avoid it so much.

one problem i just thought of - what if i want people to be able to search for a random dessert or all desserts in a list. that would require building a list of every category that's a child, grandchild etc. of the desserts category_id, then finding recipe relationships for those categories.

first off, how would i build that list of categories? perform a select to find children of the category i want, then spawn selects for every child to find its children? then more selects? the categories can be arbitrarily deep, but now i'm having trouble visualizing how to terminate the select statement to parse the tree. i guess each category could contain an is_leaf_node value, and some hackery could terminate the query if the average of all the returned category's is_leaf_node values equals 1, but i'm just not liking this category tree the more i think about it.

Google around using the SQL TREES & HIERARCHIES