Design Structure for a hierachy

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Design Structure for a hierachy

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-08 : 10:18:16

Hello everyone,
I have a table that shows a hierarchal system of an organization. In this fashion

1. Head Quarters
2. Regions
3. Zones
4. Districts

Then office(s) are the last

Its for an ERP software, and we are not sure of what each organization would implement. But the above description is the standard.

SOLUTION:
Now I have a table called Setup_Hierarchy_zones which is to cater for whatever entry a user might choose. If they choose the above 4, They enter into the fields.

PROBLEM:
Now each hierarchy except the HQ should have a table showing them. E.g list of regions, with its corresponding details, till we get to the offices. Knowing fully well that each hierarchy must have its corresponding relationships to each detailed table.

Whats the best approach to do this, bearing in mind that, we would need to pull data for each hierarchy for their fiscal accounting years.

Am thinking that I would create a SP that would select the total and dynamically create the tables corresponding to their setups (Having some basic info, e.g.

RegionID
name_or_region
Location
etc

Knowing fully well that from each organization they would have different setups

Thanks

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-08 : 10:23:17

I would have added a graphical overview, but there is no were to attach here

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-08 : 10:26:55

I have a multiparent, multilevel hierarchy system. What we do, is we generate detail info at the lowest level, and then we rollup with a 'key' table. The 'key' table includes a record for each 'node' to each 'end node'.

for example
relationship: HQ / Region / Zone / District

The key table would have

HQ_1, District_1
Region_1, District_1
Zone_1, District_1
HQ_1, District_2
Region_1, District_2
Zone_1, District_2
HQ_1, District_3
Region_1, District_3
Zone_2, District_3
etc...

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-09 : 17:20:20

Sounds Ok, But am wondering.

What would be the name of this columns and would they have relationships?

1. I also dont understand what you meant by the relationship above

quote:
Originally posted by Seventhnight
for example
relationship: HQ / Region / Zone / District

The key table would have

Where would these relationships be ? Coudl you expansiate a bit more on this ?

2. When you say work my way up from bottom, the lowest node here are the offices. Woudl these go into a seperate table ?

thanks
Afrika

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-18 : 10:26:31

Let us say that there is a HQ that has 2 Regions (Region 1 & Region 2)
So there are relationships that describe this:
HQ - Region 1
HQ - Region 2

Similarly, each region has a 3 Zones:
Region 1 - Zone 1
Region 1 - Zone 2
Region 1 - Zone 3
Region 2 - Zone 4
Region 2 - Zone 5
Region 2 - Zone 6

And lastly, each Zone has 4 districts:
Zone 1 - District 1
Zone 1 - District 2
Zone 1 - District 3
Zone 1 - District 4
Zone 2 - District 5
...
Zone 5 - District 20
Zone 6 - District 21
Zone 6 - District 22
Zone 6 - District 23
Zone 6 - District 24

To calculate values at each level in the hierarchy, you need to determine what 'rolls up' to each node (or point in the tree, i.e. a single Region, Zone, District etc)

So my suggestion was to build a table that relates each node to all of the nodes that 'roll up' to it:
for example:

Zone 1 would have the following records:
Zone 1 - District 1
Zone 1 - District 2
Zone 1 - District 3
Zone 1 - District 4

Region 1 would have the following records:
Region 1 - District 1
Region 1 - District 2
Region 1 - District 3
Region 1 - District 4
Region 1 - District 5
Region 1 - District 6
Region 1 - District 7
Region 1 - District 8
Region 1 - District 9
Region 1 - District 10
Region 1 - District 11
Region 1 - District 12

And so forth.

When it becomes time to calculate totals, simply join this mapping table to the information for each district, and then group on the parent node. Then you get all the summary values with one query.

As for question #2: If the lowest node is Offices, then they should go in the same table, and would show up in the child column. That would mean Districts would have links to Offices and Zones would link to Offices etc.

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources