O'Reilly logo

The Art of SQL by Peter Robson, Stephane Faroult

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Practical Implementation of Trees

The following subsections provide examples of each of the three hierarchy models. In each case, rows have been inserted into the example tables in the same order (ordered by commander) in an attempt to divorce the physical order of the rows from the expected result. Remember that the design is questionable, and that the purpose is to show in as simple a way as possible how to handle trees according to the model under discussion.

Adjacency Model

The following table describes the hierarchical organization of an army using the adjacency model . The table name I've chosen to use is, appropriately enough, ADJACENCY_MODEL. Each row in the table describes a military unit. The parent_id points upward in the tree to the enclosing unit:

     Name                            Null?    Type
     ------------------------------- -------- --------------
     ID                              NOT NULL NUMBER
     PARENT_ID                                NUMBER
     DESCRIPTION                     NOT NULL VARCHAR2(120)
     COMMANDER                                VARCHAR2(120)

Table ADJACENCY_MODEL has three indexes: a unique index on id (the primary key), an index on parent_id, and an index on commander. Here are a few sample lines from ADJACENCY_MODEL:

 ID PARENT_ID DESCRIPTION COMMANDER --- --------- ---------------------------- ----------------------------- 435 0 French Armée du Nord of 1815 Emperor Napoleon Bonaparte 619 435 III Corps Général de Division Dominique Vandamme 620 619 8th Infantry Division Général de Division Baron Etienne-Nicolas Lefol 621 620 1st Brigade Général de Brigade Billard (d.15th) 622 621 15th Rgmt Léger ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required