MFredin
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?
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?
ASKER
gdmaria, I am actually using an oracle database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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..
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..
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..