Re: Hierachical structures - an overview

From: Lyle Fairfield <MissingAddress_at_Invalid.Com>
Date: 4 Jan 2004 11:31:18 GMT
Message-ID: <Xns9466425916D4CFFDBA_at_130.133.1.4>


"Mike MacSween" <mike.macsween.nospam_at_btinternet.com> wrote in news:3ff7e63a$0$52881$5a6aecb4_at_news.aaisp.net.uk:

> 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

A Google Search for "Hierarchical Databases" brings up several thousand (3860) references. The first twenty are somewhat similar, but scarcely definitive in any helpful (to me) way. Generally they are described as being like trees, or directory structures, or "Information Management Systems" of the past. One points out a common problem: a child with more than one parent.

I think you have been descriptive of your needs in another thread. But perhaps, in this one, you could delineate a particular problem or situation dealing effectively with which requires specific design consideration.

I would find this helpful.

-- 
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Received on Sun Jan 04 2004 - 12:31:18 CET

Original text of this message