How do you all implement dynamic sub-categories of items, e.g. products in a shop?
For example you may have an item belonging in:
computer -> software -> games -> shoot em up (1)
computer -> misc (2)
Example (1) is in a 'third-level' subcategory whereas item (2) is just at a 'first-level'. How can these be implemented in a database where you may have variable sub-category depths?
Obviously a variable amount of tables (sub-category, sub-sub-category, sub-sub-sub-category) is inefficient.
So what I thought of doing was just having one table: categories
ID - name
1 - computer
2 - software
3 - misc
4 - games
5 - shoot em up
And then having another table showing the relationships between categories:
Where both id's would be foreign keys to the 'categories' table defining which is a sub-category of a given category (or sub-category) i.e. one such record might be:
Code PHP:Array( [cat_id] => 4 [sub_id] => 5 )
Showing that 'shoot em up' is a sub-category of 'games'.
Then the product table would have a foreign key to whichever category or sub-category that product belonged to.
My problem is how to get the 'tree' of categories, i.e. if I know a product belongs in the 'shoot em up' category, how do I recursively fetch each preceeding sub-category?
I.e. how would I write a query which would fetch all the categories in between up to the root category: games, software, computer.
Obviously I could make a simple recursive function server-side to fetch the previous category until there were no more categories (we'd reached the 'root') but this involves multiple queries. Is there anyway to do this in less queries, or more efficiently? With this table structure, or any other!
Any help is apprecated!