Solved

Building a company hierarchy tree

Posted on 2009-05-13
5
782 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

Question has a verified solution.

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

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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