Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 786
  • Last Modified:

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?
0
MFredin
Asked:
MFredin
  • 3
  • 2
1 Solution
 
gdemariaCommented:

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
 
MFredinAuthor Commented:
gdmaria, I am actually using an oracle database.
0
 
gdemariaCommented:
>  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
 
MFredinAuthor Commented:
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
 
gdemariaCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now