Results 1 to 2 of 2

Thread: database design

  1. #1
    Join Date
    Dec 2002
    Location
    india
    Posts
    1

    Unanswered: database design

    database design for storing and easy retrieval of tree structure.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: database design

    Originally posted by raman_anuradha
    database design for storing and easy retrieval of tree structure.
    The usual design is:

    CREATE TABLE tree
    ( id NUMBER PRIMARY KEY
    , parent_id NUMBER REFERENCES tree
    , ...
    );

    The standard Oracle example table SCOTT.EMP is an example of this, with column MGR referencing another EMPNO.

    To retrieve the data in a hierarchic form you can do this:

    SELECT empno, rpad( '*', LEVEL, '*' ) || ename ename
    FROM emp
    CONNECT BY PRIOR empno = mgr
    START WITH empno = 7839;

    EMPNO ENAME
    ---------- ------------------------------
    7839 *KING
    7566 **JONES
    7788 ***SCOTT
    7876 ****ADAMS
    7902 ***FORD
    7369 ****SMITH
    7698 **BLAKE
    7499 ***ALLEN
    7521 ***WARD
    7654 ***MARTIN
    7844 ***TURNER
    7900 ***JAMES
    7782 **CLARK
    7934 ***MILLER

    This shows that JONES, BLAKE and CLARK report to KING, SCOTT and FORD report to JONES, etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •