| database design [message #26127] |
Thu, 12 June 2003 06:18  |
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  |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
