RE: How to represent a tree-structure in a relational database

> 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.