Home » Developer & Programmer » Designer » database design
database design [message #26127] Thu, 12 June 2003 06:18 Go to next message
S
Messages: 27
Registered: December 2001
Junior Member
How to represent tree like hierarchy in a database table?
Re: database design [message #26128 is a reply to message #26127] Thu, 12 June 2003 08:03 Go to previous messageGo to next message
Jon Haugsand
Messages: 2
Registered: May 2003
Junior Member
Should be incredible easy:

create table foo ( id number(10), info varchar2(255), parent number(19 );

insert into foo values ('0','root',null);
insert into foo values ('1','etc',0);
insert into foo values ('2','home',0);
insert into foo values ('3','usr','0);
insert into foo values ('4'.'lib','3);

This represents a beginning of a unix like file system.
Re: database design [message #26129 is a reply to message #26127] Thu, 12 June 2003 08:41 Go to previous message
balwan
Messages: 17
Registered: January 2003
Junior Member
Here is the example to the hierarchy database design, with the help of this example you will able to design your tables.

create table emp (
empno number(6),
ename varchar2(10),
mgr number(6)
job vatchar(3)
)

Here you are assigning each employee as have manager other than the Job type 'CEO' .
E.g empno = 1
ename = SAM
mgr = null
job = CEO

empno = 2
ename =MAX
mgr = 1 // Note here we specifying the link for the hierarchy
job = MGR

empno = 3
ename =TOM
mgr = 1 // Note here we specifying the link for the hierarchy
job = MGR

empno = 4
ename =GERRY
mgr = 2 // Note here we specifying the link for the hierarchy
job = GRA

You can execute the query the following query to print the hierarchy
SELECT LPAD( ' ', 6*( LEVEL- 1)) || ename,empno, mgr, job
FROM emp
Start with job = 'CEO'
CONNECT by PRIOR empno = mgr
Previous Topic: how to design table for m:m relationships?
Next Topic: Designer
Goto Forum:
  


Current Time: Thu Mar 28 04:23:28 CDT 2024