OpenEuphoria

1. Hierarchy Management

• Posted by euphoric (admin) Oct 11, 2008
• 912 views

I'm building a program to help manage a large company's hierarchical organizational chart. What is the most efficient way to store hierarchy information as well as "manage" it? For instance, some functions would be "Who is the boss?" "Who are the underlings?" "Who is at the bottom of a branch?" etc.

I'm thinking of storing the data in a database with at least

   unique_id 
   ... 
   boss_id 

In order to build the organization chart, I would start with the President. I would look for records that have him as boss_id. I would work down from there. But is there a more efficient solution? I'm considering an index, but how would I store that index so it is better than just searching the records for boss_ids?

Any ideas would be appreciated.

Here's a sample possible hierarchy:

                       Pres 
                     /   |   \ 
                   VP1  VP2 VP2 (Vice Presidents) 
                  / |   | |  | \ 
                 M1 M2 M3 M4 M5 M6 (managers) 

Under managers would be secretaries, sales managers, etc., then sales personnel, etc.

   unique_id 
   ... 
   boss_id 

                       Pres 
                     /   |   \ 
                   VP1  VP2 VP2 (Vice Presidents) 
                  / |   | |  | \ 
                 M1 M2 M3 M4 M5 M6 (managers) 

3. Re: Hierarchy Management

• Posted by DerekParnell (admin) Oct 11, 2008
• 978 views

This is not too hard really. There are a couple of tricky bits but the D/B structure is fairly simple.

^{(This is one of many potential ways to do it)}

You need one table that defines people. Each person would have a unique number (eg. Employee #) that will never ever change. This will be the primary key for this table. The person record would have fields of ...

Employee_Number eg. 123 
Family_Name     eg. Parnell 
Personal_Name   eg. Derek 
Other_Names     eg. John 
Honorific       eg. Mr/Mrs/Ms/Dr/ 
... 

It is possible that a given person actually reports to the same person more than once. This is because who one reports to depends on one's role in the organization. An org chart really describes which roles report to which other roles, and people are the associated with the roles. In some organisations at any given time, a role can be empty even though it is still on the org chart.

A person in an organization can have more than one role or job to do. And because you will want to know "which people do job X" (eg. Who is the company president) you need a way to associated roles with people. You could have this as a field in the person record, but it would have to be a sequence to hold (potentially) multiple roles, and that make look ups a bit slower.

So, you have another table that describes job roles. Again, it too would have a unique number that will never change, and is used to identify a role.

Role_ID           eg. 1 
Title             eg. Vice President of Sales 
Management_level  eq. 2 

The 'management level' is not needed but can help later on when checking certain validity rules, such as making sure you only have one President, and ensuring that a higher management level does not report to a lower level.

Next you need a table to associate people with roles. Because the unique key to this is really the combination of two foreign keys - Employee_number and Role_ID - it is a useful technique to use an artificial record id as the unique key and use related index tables for look up.

Record_ID         eg. 36 
Employee_Number   eg. 7 
Role_ID           eg. 3 
Date_Assigned     eg. 2008-04-26 
Date_Deassigned   eg. 0000-00-00 (still assigned) 

When then have two related index tables for this so we can quickly find out what roles do employee 'X' perform? and which employees do role 'Y'?

Employee_Role index:

Employee_Role   eg. {7,3} -- key to this record 
Record_ID       eg. 36 

Role_Employee index:

Role_Employee   eg. {3,7} -- key to this record. 
Record_ID       eg. 36 

Now comes the Org Chart stuff. Because it's possible for a role to report to multiple managers, we need a flexible way of showing this. Again we can use related index tables to store this stuff.

Reports_To:

Reporting_Role    eg. 7 
Reports_To        eg. 4 
Assignment_Date   eg. 2008-01-01 -- When this reporting relationship became effective. 

Reported_to:

Reported_Role     eg. 4 
Reported_By       eg. 7 

These will enables us to quickly find who reports to whom given either a subordinate role or a mangement role.

The tricky part involves making sure that you don't have circular references, eg. A reports to B, who reports to C, who reports to A. This situation is illogical and must be avoided. The way to do this is when adding a "Reports_To" record, you must first read up through the hierachy to ensure that the 'Reporting_Role' isn't already in the chain. For example ...

Let's say we want to add that role #7 reports to role #9. First we find out who 7 already reports to, and a this stage pretend that '#9' is amongst those. Then for each of those managers, we find out who they reports to, etc ... Each time we keep a list of the 'managers' and if role #7 ever appears in the manager's list, we can't add the new relationship due to a circular reference. Also, if the combination #7 - #9 already exists in the "Reports_To" table we can't add it either, because the relationship already exists.

This might be enough to get you started.

5. Re: Hierarchy Management

• Posted by DerekParnell (admin) Oct 11, 2008
• 1000 views

No, but I am writing one. Red-Black trees too. However, this is not a b-tree situation.

Yes, but if you are working with large volumes, you would be advised to use a disk-based database. EDS would probably do as a start and if performance began to be an issue then migrate to MySQL or similar. If you code your business logical in separate files from the data access logic, this will not be a big problem.