Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: about hierarchical query

Re: about hierarchical query

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Mon, 22 Nov 2004 07:03:37 -0500
Message-ID: <>

"--CELKO--" <> wrote in message
| >> how can I write an hierarchical query to view all level of my tree,
| but only the branches end with leaves that satisfy some conditions? <<
| There are many ways to represent a tree or hierarchy in SQL. This is
| called an adjacency list model and it looks like this:
| boss CHAR(10) DEFAULT NULL REFERENCES OrgChart(emp),
| salary DECIMAL(6,2) NOT NULL DEFAULT 100.00);
| OrgChart
| emp boss salary
| ===========================
| 'Albert' NULL 1000.00
| 'Bert' 'Albert' 900.00
| 'Chuck' 'Albert' 900.00
| 'Donna' 'Chuck' 800.00
| 'Eddie' 'Chuck' 700.00
| 'Fred' 'Chuck' 600.00

| The adjacency list table is denormalized in several ways. We are
| modeling both the Personnel and the organizational chart in one table.

i don't see this as a problem of denormalization in and of itself -- this is simply a recursive relationship, which is totally legitimate and in no way implies denormalization

however, if the table included job positions (your sample does not) and there was a directy dependency of the recursive FK based directly on the job position of the source or target record, then it would be denormalized and should be split into personnel and organization tables

| But for the sake of saving space, pretend that the names are job
| titles and that we have another table which describes the Personnel
| that hold those positions.
| Another problem with the adjacency list model is that the boss and
| employee columns are the same kind of thing (i.e. 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, one place, one time.

i would say the the problem here is definitely not normalization, but a poor choice of PK -- PK values should identify, not describe, and should not be updatable. by using an appropriate value for the PK this self-imposed problem is eliviated (really, you've just described normal UPDATE-CASCADE processing). if, however, the designer chooses to use an identifying value as the PK (poor choice), the existence of the PK value in an FK column is not denormalization -- it's referential integrity

| The final problem is that the adjacency list model does not model
| subordination. Authority flows downhill in a hierarchy, but If I fire
| Chuck, I disconnect all of his subordinates from Albert. There are
| situations (i.e. water pipes) where this is true, but that is not the
| expected situation in this case.

not a problem -- if the assumption/rule is that if a node is disconnected, all directly dependent nodes should be re-assigned to the deleted node's parent. this is a simple CASCADE-UPDATE that has to be addressed for any FK relationship

this is not to say that there are no benefits from the nested sets approach -- i'll take a closer look at that later and post any comments. but the problems presented for the hierarchical model are not accurate

| I have a book on TREES & HIERARCHIES IN SQL which you can get at
| right now.

perhaps more appropriate in c.d.o.marketplace?

| The Oracle proprietary CONNECT BY clause is a non-relational hidden
| cursor which is both slow and weak.

and some support for 'slow' and elaboration on 'weak' would be helpful; i would think 'proprietary' is the main issue -- but then again Received on Mon Nov 22 2004 - 06:03:37 CST

Original text of this message