Solved

Building a company hierarchy tree

Posted on 2009-05-13
5
778 Views
Last Modified: 2012-05-06
What I'm trying to do is figure out table structure so I can create a company hierarchy.  I currently have a users table and a user_type table.  

Here is an example of what I'm trying to do.

                                                President
                                                      |
                                             Vice President
                                                  __|__
                                                  |       |
                                          Manager  Manager

Etc, etc.

How can I put together a table structure to make this work?
0
Comment
Question by:MFredin
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 24381529

TABLE:  Contacts

 Contact_ID   int    - the primary key
 ReportsToContact_ID  int  -- the foreign key to the Contact_ID of this table...
 FirstName    varchar(20)
 LastName    varchar(30)
 

Having a foreign key in the table that points back to the same table is the key..

When the ReportToContact_ID is BLANK, that is the highest person (president?)  in the company

What database are you using?  
You want to design the table so you can easily query the hierarchy.  This is not easy in most databases, only Oracle supplies an easy way to do it..


0
 

Author Comment

by:MFredin
ID: 24381724
gdmaria, I am actually using an oracle database.
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 24384721
>  I am actually using an oracle database.


Yeah !!!!   You're very lucky !


All you have to do is use the CONNECT BY  phrase in your SELECT statement

http://www.oracle.com/technology/products/oracle9i/daily/oct04.html
0
 

Author Comment

by:MFredin
ID: 24454713
Thanks gdmaria,

I just learned that some employees can hold multiple positions within the company.  I suppose I will have to create a table to hold their positions and a joining table.  How will the CONNECT BY be used then?  Is it possible?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 24458101
hmmm, that does make it trickier.

I would think that you would NOT want to have more than one record in the CONTACTS table for any one person

So then you need a new table to define the hierarchy.


ContactReportsTo
   person_id  
   reportsToPerson_id

That will allow you to have a many-to-many relationship between the contact and who they report to..

0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.

832 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