Unlimited Hierarchy

[login to unmask email]

Unlimited Hierarchy
We have a need to establish a table that defines an unlimited hierarchy.
This hierarchy also needs to be flexible such that each user may define their
own hierarchy in terms of number of levels, meanings, etc. The lowest level
is always account number. So for example we might have a hierarchy that is:

Region --> State ---> Company --> Division --> Employee --> Account

When looking for specific accounts, we need to know what the hierarchy is
for that account. Likewise, we may need to get all accounts for any portion
of the hierarchy. I remember seeing a table design somewhere that may allow
this but I can't recall the source. We do not want to have fixed columns in
a table as a key, because as I said, the hierarchy needs to be flexible in
the number of levels and meaning. I'm thinking of some sort of
self-referencing table or tables that can define the hierarchy. For example:

Hierarchy_table
Parent Level ID
Parent Type
Child Level ID
Child Type

The solution I believe I saw was something to the effect:

Hierarchy_table
Parent Level ID
Parend Type
Child Level ID
Child Type
Account number

This seems to exponentially increase the size of the table depending on
the number of levels. What kind of solutions have you employed for this
sort of problem with reasonable performance.

Thanks in advance for any help.

Dave.



Richard A Yevich

Re: Unlimited Hierarchy
(in response to DPetro@AOL.COM)
Dave,

What platform as there are simple solutions for OS/390 and simpler solutions
for non-OS/390 platforms. Recursive SQL on non-OS/390, relatively simple in
this case. On the OS/390, it will depend on the number of levels, etc.
There are 2 different solutions on the OS/390, one in SQL, limited to a
number of levels. The other uses multiple SQL statements and a global temp
table and can handle almost anything. Which platform are you?

Richard Yevich
+=====+======+
Information Technology Consulting, Data Modeling, Advanced Education
RYC® Inc. USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-512-476-3930
Web: < http://www.ryci.com > Email: [login to unmask email] Offices: USA and Europe
DB2® Family and Oracle® Specialists - Parallel Technologies
VLDB and Data Sharing Technologies (specialties SAP®, Peoplesoft®)
Authors of "DB2 Answers" by Osborne-McGraw Hill, May, 1999

> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
> [login to unmask email]
> Sent: Sunday, December 12, 1999 3:46 PM
> To: [login to unmask email]
> Subject: Unlimited Hierarchy
>
>
> We have a need to establish a table that defines an unlimited
> hierarchy.
> This hierarchy also needs to be flexible such that each user may
> define their
> own hierarchy in terms of number of levels, meanings, etc. The
> lowest level
> is always account number. So for example we might have a
> hierarchy that is:
>
> Region --> State ---> Company --> Division --> Employee
> --> Account
>
> When looking for specific accounts, we need to know what the
> hierarchy is
> for that account. Likewise, we may need to get all accounts for
> any portion
> of the hierarchy. I remember seeing a table design somewhere
> that may allow
> this but I can't recall the source. We do not want to have fixed
> columns in
> a table as a key, because as I said, the hierarchy needs to be flexible in
> the number of levels and meaning. I'm thinking of some sort of
> self-referencing table or tables that can define the hierarchy.
> For example:
>
> Hierarchy_table
> Parent Level ID
> Parent Type
> Child Level ID
> Child Type
>
> The solution I believe I saw was something to the effect:
>
> Hierarchy_table
> Parent Level ID
> Parend Type
> Child Level ID
> Child Type
> Account number
>
> This seems to exponentially increase the size of the table
> depending on
> the number of levels. What kind of solutions have you employed for this
> sort of problem with reasonable performance.
>
> Thanks in advance for any help.
>
> Dave.
>
>
> To change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list can be reached
> at [login to unmask email]
>



Lockwood Lyon

Re: Unlimited Hierarchy
(in response to Richard A Yevich)
See March 1996 (and later articles in the series) by Joe Celko from the
DBMS magazine archives at:

http://www.dbmsmag.com/artin301.html

for a discussion of how to implement tree structures in SQL, which seems to be one possible answer to your question.

-- Lockwood Lyon
Amway Corp




Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:

Subject: Unlimited Hierarchy


We have a need to establish a table that defines an unlimited hierarchy.
This hierarchy also needs to be flexible such that each user may define
their
own hierarchy in terms of number of levels, meanings, etc. The lowest
level
is always account number. So for example we might have a hierarchy that
is:

Region --> State ---> Company --> Division --> Employee --> Account

When looking for specific accounts, we need to know what the hierarchy
is
for that account. Likewise, we may need to get all accounts for any
portion
of the hierarchy. I remember seeing a table design somewhere that may
allow
this but I can't recall the source. We do not want to have fixed columns
in
a table as a key, because as I said, the hierarchy needs to be flexible in
the number of levels and meaning. I'm thinking of some sort of
self-referencing table or tables that can define the hierarchy. For
example:

Hierarchy_table
Parent Level ID
Parent Type
Child Level ID
Child Type

The solution I believe I saw was something to the effect:

Hierarchy_table
Parent Level ID
Parend Type
Child Level ID
Child Type
Account number

This seems to exponentially increase the size of the table depending
on
the number of levels. What kind of solutions have you employed for this
sort of problem with reasonable performance.

Thanks in advance for any help.

Dave.








Isaac Yassin

Re: Unlimited Hierarchy
(in response to Lockwood Lyon)
Hi,
I have something similar with Endeavor data about programs and subroutines.
I use a table in the following layout:
"father_name","father_type","son_name","son_type" per row.
each son can be a father as well and to many sons too.
A small PL/1 program reads the file an builds a vector with the hierarchy and
then displays it.
takes sub-second to most problems.
The biggest (6000+ rows per root element) takes < 3 sec. elapse.

--
Isaac Yassin

DBMS & IT Consultant

Tel: +972 9 9505172
Cel: +972 54 452793
Fax: +972 9 9560803


[login to unmask email] wrote:
>
> We have a need to establish a table that defines an unlimited hierarchy.
> This hierarchy also needs to be flexible such that each user may define their
> own hierarchy in terms of number of levels, meanings, etc. The lowest level
> is always account number. So for example we might have a hierarchy that is:
>
> Region --> State ---> Company --> Division --> Employee --> Account
>
> When looking for specific accounts, we need to know what the hierarchy is
> for that account. Likewise, we may need to get all accounts for any portion
> of the hierarchy. I remember seeing a table design somewhere that may allow
> this but I can't recall the source. We do not want to have fixed columns in
> a table as a key, because as I said, the hierarchy needs to be flexible in
> the number of levels and meaning. I'm thinking of some sort of
> self-referencing table or tables that can define the hierarchy. For example:
>
> Hierarchy_table
> Parent Level ID
> Parent Type
> Child Level ID
> Child Type
>
> The solution I believe I saw was something to the effect:
>
> Hierarchy_table
> Parent Level ID
> Parend Type
> Child Level ID
> Child Type
> Account number
>
> This seems to exponentially increase the size of the table depending on
> the number of levels. What kind of solutions have you employed for this
> sort of problem with reasonable performance.
>
> Thanks in advance for any help.
>
> Dave.
>
>
>