Hierachical structures - an overview

I have an app I need a hierachical structure for. There seem to be 3 ways of implementing this, as far as I can see:

1. Adjacency list. Pros - intuitive and relatively simple Cons - not easily accesible via standard SQL, needs recusive queries to crawl up or down the structure. I'll be doing this in Jet or perhaps MS SQL Server, so Oracle's Connect by is out.
2. Nested Sets a la Joe Celko's BOM. Pros - easy to access the structure via standard SQL Cons - expensive/complex when the structure changes frequently.
3. Materialised Paths. Pros - easy to access the structure via standSQL, the hierachy is obvious and stored in a single field. Cons - None? The value in the 'path' field doesn't _appear_ to be atomic, that might well be a debatable point.

1a. Adjacency list + as per:
http://fungus.teststation.com/~jon/treehandling/TreeHandling.htm Not sure about this. It claims to give the path upwards. It looks like it merely gives the ancestors, which isn't the same thing.

Any other techniques?

Any other pros and cons?

Yours, Mike MacSween Received on Sun Jan 04 2004 - 04:08:58 CST