hierarchical tree structures in db

参考http://www.cnblogs.com/shenr/archive/2008/09/30/1323452.html
In Oracle, there is 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(same concept as we do laster in Ms Sql server 2008):

create table hierarchic_tbl (

id number primary key,

parent_id references hierarchic_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_tbl values ( 1, null, 'Language' , 1);

-- 2nd level:

insert into hierarchic_tbl values ( 2, 1, 'VB' , 1);

insert into hierarchic_tbl values ( 3, 1, 'C#' , 1);

insert into hierarchic_tbl values ( 4, 1, 'JAVA', 1);

-- 3rd level (below VB)

insert into hierarchic_tbl values ( 5, 2, 'VB6.0' , 1);

insert into hierarchic_tbl values ( 6, 2, 'VB.NET' , 1);

-- 4th level (below VB.NET)

insert into hierarchic_tbl values (7, 6, 'VB.NET2003' , 1);

insert into hierarchic_tbl values (8, 6, 'VB.NET2005' , 1);

Showing the tree with start with .. connect by:

select

lpad(' ', (level-1)*2, ' ') || descr

from

hierarchic_tbl

start with parent_id is null

connect by prior id = parent_id;

------------------------------------------------

Result:

Language

VB

VB6.0

VB.NET

VB.NET2003

VB.NET2005

C#

Java

Mysql
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html