Re: A new approach to storing ordered hierarchical data in RDBs.

From: goran <>
Date: 22 Nov 2006 08:16:20 -0800
Message-ID: <>

I guess a better title for my post would have been »I've rediscovered the wheel«. I'm curious as to why 90% of articles I've read on the subject only consider adjacency list, materialized path and nested sets as a viable solution to storing hierarchical data in RDBs? I think maintaining transitive closure tables to be as complex (or less complex) than maintaining materialized paths while avoiding the limitation of the size of an indexed field.

The "new" idea I had was to actually use a helper (width) table to store the information on the size of each branch of the tree. For my case it was important to preserve not only the hierarchical relations between nodes but also their relative order. TC table alone cannot tell me if Marie is the first or the last child node of Allen.

Using this approach I get a model that is far less volatile then nested sets. I have a much faster read then using adjacency list (using CTE or temp stack tables). The way I'm storing and retrieving global order enables me to avoid the volatility of using global order information or the limitation of a fixed mapping scheme (as in nested intervals model).

If this approach has been used before, I would appreciate a link or two as I don't wish to rediscover things I can educate myself on by reading. Anyways - sorry for causing such turbulence.


vc wrote:
> ...
> The TC appoach to SQL queries has been rather well known since 1970s
> but alas TC maintenance is costly.
> More interestingly, why bother ? SQL server implements the SQL-99
> ...
Received on Wed Nov 22 2006 - 17:16:20 CET

Original text of this message