> The way I'd do it if I had to do it again:
> Give each record a unique id, generated by the application.
> Denote levels with extra letters.
>
> So:
>
> AA - Automotive transport
> AAAA - Cars
> AAAB - Motorcycles
>
> The structures has the added bonus of making it very easy to
> determine all the
> sub-categories of a category, no matter how deep the tree is
> below the category
> you're looking at. With the first approach it is not possible
> to do this in a
> single SQL query. You could do this with a function, I guess.
The problem with this method is if you need to insert a category, or move a
category. You'll need to re-id a bunch of categories, and bubble those
changes out to every table that refers to this table.
Stuart Statman
Director of Software Development
Slam Media, Inc.