Solved

Building a company hierarchy tree

Posted on 2009-05-13
5
775 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now