Msg#: 4493937 posted 5:13 am on Sep 13, 2012 (gmt 0)
You only need two fields - each record has an ID # and a parent record #. Any record can be a child of any other record, totally flexible, and you can easily traverse it backwards or forwards.
To build a drop down of all cars you start with the car record and query all it's children that claim "cars" as a parent ID#, so on and so forth down the chain as deep as you want.
To get a drop list of all top level categories, like cars, bikes, trucks, etc. you query for parent ID = 0 since they have no parents. Then query for everything that has the ID for cars, bikes, etc., so on and so forth.
I would cache the dropdown in a file that's included at run-time to save on those queries.