Solved

Hierarchical structure in database tables.

Posted on 1998-09-01
8
1,712 Views
Last Modified: 2010-05-18
How can I represent a hierarchical structure (for example newsgroup messages) and store it in database tables ?
My goal is then to be able to display and modify that structure while making as little database operations as possible to increase performance.
0
Comment
Question by:Barak_Cohen
8 Comments
 

Author Comment

by:Barak_Cohen
Comment Utility
Edited text of question
0
 
LVL 4

Expert Comment

by:sganta
Comment Utility
1. First you have to analyze the master data,
   You 've to divide into major groups.

For NEWS GROUPS

1. Divide this NEWS into different groups

    Eg.
           1. Politics
           2. Sports
           3. Education
           4. Games
           5. Employment
           etc..
   For this have a master table
   called
   Mast_NEWs( code, description, ... etc.)

2. After that you may have different sub-groups.
   To make it to simplify, I don't want to confuse you with sub-groups

3. Now Create a TABLE which contains daily news
   Daily_Master
   (
         news_date          -- Date
         master_code       -- Group which belongs to -- POLITICS,SPORTS etc.
         sequence_num    -- Message no in that group
         description          -- News Description
         etc ..
   )
etc..

I hope this helps you.
0
 

Author Comment

by:Barak_Cohen
Comment Utility
I'm sorry, I did not ask for an analysis of a newsgroup system.
I only used newsgroups as an example of a information arranged in a hierarchical matter.
My question is how to should I store such information within database tables, so that later on, i'll be able to display that information, while preserving its hierarchical structure.
(i.e. in a tree form, like explorer).

0
 
LVL 2

Expert Comment

by:odessa
Comment Utility
try using some like this:
Table(
   Code    int  'ID'  ; identity coloumn
   Parent  int        ; Parent coloumn, 0 for root
..
)

here is your hierarchical structure just on new record say how is his Parent, on root records set 0. that's all
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 3

Expert Comment

by:junfeb
Comment Utility
This is some more explanation on how to store and use hierarchical Data -


Table Definition  - to give a simple example

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        COMM NUMBER(7,2),
        DEPTNO NUMBER(2));


Hierarchical Queries

If a table contains hierarchical data, you can select rows in a hierarchical order using the following clauses:
 START WITH
                      specifies the root row(s) of the hierarchy
 CONNECT BY
                      specifies the relationship between parent rows and child rows of the hierarchy
 WHERE
                      restricts the rows returned by the query without affecting other rows of the hierarchy

 


You can use the information from the above clause to form the hierarchy using the following steps:

   1. selects the root row(s) of the hierarchy--those rows that satisfy the condition of the START WITH clause.
   2. selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY clause with respect to one of the root
     rows.
   3.selects successive generations of child rows. first select the children of the rows returned in step 2, and then the children of those
     children, and so on. Always selects children by evaluating the CONNECT BY condition with respect to a current parent row.
   4.If the query contains a WHERE clause,  all rows from the hierarchy that do not satisfy the condition of the WHERE clause are eliminated. This condition is evaluated for each row individually, rather than removing all the children of a row that does not satisfy the condition.
                               

SELECT statements performing hierarchical queries are subject to the following restrictions:

     A SELECT statement that performs a hierarchical query cannot also perform a join.
     A SELECT statement that performs a hierarchical query cannot select data from a view whose query performs a join.
 
The following sections discuss the START WITH and CONNECT BY clauses.

START WITH Clause

The START WITH clause identifies the row(s) to be used as the root(s) of a hierarchical query. This clause specifies a condition that the roots must satisfy.
If you omit this clause,  all rows in the table are used as root rows. A START WITH condition can contain a subquery.

CONNECT BY Clause

The CONNECT BY clause specifies the relationship between parent and child rows in a hierarchical query. This clause contains a condition that defines this
relationship. This condition can be any condition as described in "Conditions"; however, some part of the condition must use the PRIOR operator to refer to
the parent row. The part of the condition containing the PRIOR operator must have one of the following forms:

PRIOR expr comparison_operator expr
expr comparison_operator PRIOR expr

To find the children of a parent row, prior expression is evaluated for the parent row and the other expression for each row in the table. Rows for
which the condition is true are the children of the parent. The CONNECT BY clause can contain other conditions to further filter the rows selected by the
query. The CONNECT BY clause cannot contain a subquery.


Example I

The following CONNECT BY clause defines a hierarchical relationship in which the EMPNO value of the parent row is equal to the MGR value of the child
row:

CONNECT BY PRIOR empno = mgr;

Example II

In the following CONNECT BY clause, the PRIOR operator applies only to the EMPNO value. To evaluate this condition,  EMPNO is evaluated  for the parent row and MGR, SAL, and COMM values for the child row:

CONNECT BY PRIOR empno = mgr AND sal > comm;

To qualify as a child row, a row must have a MGR value equal to the EMPNO value of the parent row and it must have a SAL value greater than its
COMM value.

The LEVEL Pseudocolumn

SELECT statements that perform hierarchical queries can use the LEVEL pseudocolumn. LEVEL returns the value 1 for a root node, 2 for a child node of a
root node, 3 for a grandchild, etc. For more information on LEVEL, see the section "Pseudocolumns".

The number of levels returned by a hierarchical query may be limited by available user memory.

Example I

The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is 'PRESIDENT'. The child rows
of a parent row are defined to be those who have the employee number of the parent row as their manager number.

SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart,
        empno, mgr, job
    FROM emp
    START WITH job = 'PRESIDENT'
    CONNECT BY PRIOR empno = mgr;

ORG_CHART    EMPNO      MGR        JOB
------------ ---------- ---------- ---------
KING               7839            PRESIDENT
  JONES            7566       7839 MANAGER
    SCOTT          7788       7566 ANALYST
      ADAMS        7876       7788 CLERK
    FORD           7902       7566 ANALYST
      SMITH        7369       7902 CLERK
  BLAKE            7698       7839 MANAGER
    ALLEN          7499       7698 SALESMAN
    WARD           7521       7698 SALESMAN
    MARTIN         7654       7698 SALESMAN
    TURNER         7844       7698 SALESMAN
      JAMES        7900       7698 CLERK
 CLARK             7782       7839 MANAGER
   MILLER          7934       7782 CLERK

The following statement is similar to the previous one, except that it does not select employees with the job 'ANALYST'.

SELECT LPAD(' ', 2*(LEVEL-1)) || ename org_chart,
     empno, mgr, job
     FROM emp
     WHERE job != 'ANALYST'
     START WITH job = 'PRESIDENT'
     CONNECT BY PRIOR empno = mgr;

ORG_CHART            EMPNO      MGR        JOB      
-------------------- ---------- ---------- ---------
KING                       7839            PRESIDENT
  JONES                    7566       7839 MANAGER  
      ADAMS                7876       7788 CLERK    
      SMITH                7369       7902 CLERK    
  BLAKE                    7698       7839 MANAGER  
    ALLEN                  7499       7698 SALESMAN
    WARD                   7521       7698 SALESMAN
    MARTIN                 7654       7698 SALESMAN
    TURNER                 7844       7698 SALESMAN
    JAMES                  7900       7698 CLERK    
  CLARK                    7782       7839 MANAGER  
    MILLER                 7934       7782 CLERK

Oracle does not return the analysts SCOTT and FORD, although it does return employees who are managed by SCOTT and FORD.

The following statement is similar to the first one, except that it uses the LEVEL pseudocolumn to select only the first two levels of the management hierarchy:

SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart,
empno, mgr, job
    FROM emp
    START WITH job = 'PRESIDENT'
    CONNECT BY PRIOR empno = mgr AND LEVEL <= 2;

ORG_CHART    EMPNO      MGR        JOB
------------ ---------- ---------- ---------
KING               7839            PRESIDENT
  JONES            7566       7839 MANAGER
  BLAKE            7698       7839 MANAGER
  CLARK            7782       7839 MANAGER

I'll post this as an answer if this satisfies your query.

Thanks.
0
 

Author Comment

by:Barak_Cohen
Comment Utility
I'm actually looking for a little more complet solution.
Although storing the parent ID can be one of the ways, the job doesn't finish there. The main problem with this sort of applications, is extracting the data from the database, and being able to sort it later on so that children appear right after their parents, and their children appear right after them.

0
 
LVL 3

Accepted Solution

by:
junfeb earned 150 total points
Comment Utility
Please try my solution and it will do exactly what you want. Successive generations will be handled.

Again I repeat ,

       You can use the information from the above clause to form the hierarchy using the following steps:

          1. selects the root row(s) of the hierarchy--those rows that satisfy the condition of the START WITH clause.
          2. selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY clause with respect
       to one of the root
            rows.
          3.selects successive generations of child rows. first select the children of the rows returned in step 2, and then the children of
       those
            children, and so on. Always selects children by evaluating the CONNECT BY condition with respect to a current parent row.
          4.If the query contains a WHERE clause,
all rows from the hierarchy that do not satisfy the condition of the WHERE clause
       are eliminated. This condition is evaluated for each row individually, rather than removing all the children of a row that does not
       satisfy the condition. .

Thanks.
0
 

Author Comment

by:Barak_Cohen
Comment Utility
Your answer looks very good, I hope it'll work for me.
Thanks.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now