Hi Fabio,

Thanks for the link. As always it is nice to read articles from Joe. He indeed has found an alternative way of solving hierarchical problems in SQL. Hierarchical problems in are always difficult to solve in SQL.

I´ve read some articles about YUKON that describe the new recursive SQL solutions to solve these kind of problems. Of course there is no real world experience yet to show how the performance relates to Joe´s solution.

However I found a statement of Joe´s I highly disagree with:

Another problem with the adjacency list model (table 1) is that the boss and employee columns are the same kind ( names of personnel) and therefore should be shown in only one column in a normalized table. To prove that this is not normalized, assume that "Chuck" changes his name to "Charles;" you have to change his name in both columns and several places. The defining characteristic of a normalized table is that you have one fact in one place at one time.

The thing that Joe does not seem to realize it that he uses "intelligent keys" in his example which is great for the purpose of the example. In his example the emp column actually is the primary key and the boss is a foreign key column. As always within a good database design the RI between foreign keys and primary keys should be set appropriate (in this case to cascading update). This makes the multiple updates issue a non argument as they are handled by the RI mechanism. IOW the table1 IS fully normalized (normalisation does not say anything about "intelligent" versus "surrogate keys").

And of course it would be way smarter to use surrogate keys in here; a integer PK and FK defining the relations between boss and employee and having another candidate key on the name of the employee. Then when you change the name of the employee does not cascade throughout the database.

Now as for his nested set model (which indeed is an interesting approach); It has its own set of problems. If for example Nodes are inserted into the tree (for example Bert becomes a boss for a new position), the whole numbering needs to be redone, for each and every record in the emp table.

However, the advantages if the nested model as retrieving information from it through SQL is clear. It is rather easy and very fast to get info out of the tree.

However I´ve got some doubts about changing the datamodel in this way solely because of technical limitations of SQL (Set oriented approach). However you´ve got to be practical (which of course is the point of this article) sometimes and choose for that. In that respect we are better off with VFP where you just could use a record oriented approach use recursive mechanisms and directusage of indexes (SEEK) to browse through the tree.

Walter,






>Hi David,
>
>Yes! This is an amazing topic! Joe Celko's technique has saved one big project we run here at the company. Basically, his technique (called adjacency list) stores the tree nodes hierarchies as a Graph and frees you from having to store the parent/child foreing-key references into the relational tables, which, BTW, is very annoying, given the need of several INNER JOIN constructs we have to build in order to retrieve the ancestors/descendents sets.
>
>Here is an article you may find useful until Celko's new book doesn't become available:
>
>http://www.intelligententerprise.com/001020/celko1_1.jhtml
>
>HTH.