René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Oracle SQL: Storing hiearchical data

One way to store hierarchical data is to create a self referencing table. It is called self referencing because the foreign key (column parent_id) references the same table as the table in which the foreign key is:
create table hierarchic_yahoo_tbl (
  id         number primary key,
  parent_id  references hierarchic_yahoo_tbl, -- references itself
  descr      varchar2(20),
  active     number(1) not null check (active in (0,1))
);
This table is now filled with some values:
-- 1st level:

insert into hierarchic_yahoo_tbl values ( 1, null, 'Yahoo'         , 1);

-- 2nd level:

insert into hierarchic_yahoo_tbl values ( 2,    1, 'Entertainment' , 1);
insert into hierarchic_yahoo_tbl values ( 3,    1, 'Science'       , 1);
insert into hierarchic_yahoo_tbl values ( 4,    1, 'Social Science', 1);

-- 3rd level (below Entertainment)

insert into hierarchic_yahoo_tbl values ( 5,    2, 'Awards'        , 1);
insert into hierarchic_yahoo_tbl values ( 6,    2, 'Comedy'        , 1);
insert into hierarchic_yahoo_tbl values ( 7,    2, 'Humor'         , 0);
insert into hierarchic_yahoo_tbl values ( 8,    2, 'Magic'         , 1);

-- 4th level (below Humor)

insert into hierarchic_yahoo_tbl values (19,    7, 'Jokes'         , 1);
insert into hierarchic_yahoo_tbl values (20,    7, 'Advice'        , 1);
insert into hierarchic_yahoo_tbl values (21,    7, 'Parody'        , 1);

-- 4th level (below Magic)

insert into hierarchic_yahoo_tbl values (16,    8, 'Card Tricks'   , 1);
insert into hierarchic_yahoo_tbl values (17,    8, 'Magazines'     , 1);
insert into hierarchic_yahoo_tbl values (18,    8, 'Organizations' , 1);

-- 3rd level (below Science)

insert into hierarchic_yahoo_tbl values ( 9,    3, 'Astronomy'     , 1);
insert into hierarchic_yahoo_tbl values (10,    3, 'Biology'       , 1);
insert into hierarchic_yahoo_tbl values (11,    3, 'Geography'     , 1);
insert into hierarchic_yahoo_tbl values (12,    3, 'Physics'       , 1);
insert into hierarchic_yahoo_tbl values (13,    3, 'Research'      , 1);

-- 4th level (below Astronomy)

insert into hierarchic_yahoo_tbl values (22,    9, 'Galaxies'     , 1);
insert into hierarchic_yahoo_tbl values (23,    9, 'Pictures'     , 1);
insert into hierarchic_yahoo_tbl values (24,    9, 'Stars'        , 1);

-- 4th level (below Physics)

insert into hierarchic_yahoo_tbl values (25,   12, 'Electricity'  , 1);
insert into hierarchic_yahoo_tbl values (26,   12, 'Mechanics'    , 1);

-- 3rd level (below Social Science)

insert into hierarchic_yahoo_tbl values (14,    4, 'Psychology'   , 1);
insert into hierarchic_yahoo_tbl values (15,    4, 'Sociology'    , 1);

-- 4th level (below Psychology)

insert into hierarchic_yahoo_tbl values (27,   14, 'Conferences'  , 1);
insert into hierarchic_yahoo_tbl values (28,   14, 'Intelligence' , 1);
Showing the tree with start with .. connect by:
select
  lpad(' ', (level-1)*2, ' ') || descr
from
  hierarchic_yahoo_tbl
start with parent_id is null
connect by prior id = parent_id;
Yahoo
  Entertainment
    Awards
    Comedy
    Humor
      Jokes
      Advice
      Parody
    Magic
      Card Tricks
      Magazines
      Organizations
  Science
    Astronomy
      Galaxies
      Pictures
      Stars
    Biology
    Geography
    Physics
      Electricity
      Mechanics
    Research
  Social Science
    Psychology
      Conferences
      Intelligence
    Sociology
The next SQL statement restricts the output to subtrees whose active flag is set to 1. Additionally, it starts with the Entertainment sub tree (id = 2). The active flag of Humor is set to 0, so it won't be displayed.
This technique is also covered in pruning in connect by
select
  lpad(' ', (level-1)*2, ' ') || descr
from
  hierarchic_yahoo_tbl
start with id = 2
connect by prior id = parent_id and active = 1;
Entertainment
  Awards
  Comedy
  Magic
    Card Tricks
    Magazines
    Organizations

Further links

On storing hierarchical data shows how the same subtree can be assigned to more than one node.