Link to home
Start Free TrialLog in
Avatar of MFredin
MFredinFlag for United States of America

asked on

Building a company hierarchy tree

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?
Avatar of gdemaria
gdemaria
Flag of United States of America image


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..


Avatar of MFredin

ASKER

gdmaria, I am actually using an oracle database.
ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MFredin

ASKER

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?
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..