RE: Database hierarchies

There are two other approaches  

1. Kimballs helper table apprach. Please see the details at

http://www.google.ca/search?q=cache:s8k19JsELzIJ:www.dbmsmag.com/9809d05.htm l+kimball+helper+tables+hierarchies+fact+table&hl=en  

     This will work even if you have multiple hierarchies. If one employee has one manager the Oracle parent_id apprach will work. But if an employee is reporting to one manager for administrative purposes and another managers (project manager) for work related tasks then the apprach of using CONNECT BY MAY not work. But kimball's helper table apprach will work. This is mainly used in reporting/data warehouse environments. But can be used for OLTP also.
The sample code forOracle databaseis available at kimball's website.  

2. Joe celko's left number right number appraoch.

    This was introduced by Joe in his book 'SQL for smarties'. You can get the details at  

http://www.google.ca/search?q=cache:tl_CTRYtCQYJ:www.intelligententerprise.c om/001020/celko.shtml+Joe+left+number+right+number+hierarchies&hl=en  

This is a very flexible apprach and it will give good performance. The only issue is generating the left and right numbers are very complex. There are some sample code on the web for Sql serve. May be you need to modify it for oracle if this is the apprach that you want to go.

Thanks,

Vincent

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Aggarwal, Meenakshi Sent: Wednesday, January 12, 2005 8:46 AM To: oracle-l_at_freelists.org
Subject: Database hierarchies

Hi,

I have parent-child-child-child-.... (multiple hierarchies). What is the best approach to create tables in data warehouse, when user have no clarity on levels of hierarchies and I need to consider future increase in levels of hierarchies, so I need to plan a structure that can absorb future hierarchies.

I saw one example on asktom.oracle.com. Does anybody have better way...

select rpad('*',2*level,'*')||ename EmpName, dname,   2 sys_connect_by_path( ename, '/' ) cbp   3 from emp
  4 start with mgr is null
  5 connect by prior empno = mgr
  6 order SIBLINGS by ename
  7 /

EMPNAME                        DNAME          CBP
------------------------------ -------------- ------------------------------


**KING                         ACCOUNTING     /KING

****BLAKE                      SALES          /KING/BLAKE

******ALLEN                    SALES          /KING/BLAKE/ALLEN

******JAMES                    SALES          /KING/BLAKE/JAMES

******MARTIN                   SALES          /KING/BLAKE/MARTIN

******TURNER                   SALES          /KING/BLAKE/TURNER

******WARD                     SALES          /KING/BLAKE/WARD

****CLARK                      ACCOUNTING     /KING/CLARK

******MILLER                   ACCOUNTING     /KING/CLARK/MILLER

****JONES                      RESEARCH       /KING/JONES

******FORD                     RESEARCH       /KING/JONES/FORD

********SMITH                  RESEARCH       /KING/JONES/FORD/SMITH

******SCOTT                    RESEARCH       /KING/JONES/SCOTT

********ADAMS                  RESEARCH       /KING/JONES/SCOTT/ADAMS

Thanks

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l