Re: Figuring out relationships between tables.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


"Andrew Cooper" <kairoscreative@xxxxxxxxx> wrote in message news:h6rlrr$jro$1@xxxxxxxxxxx
Greetings,

This is a general database design question. I've got a database where I need to hold information on employees. Every employee has much of the same information so I've created an Employee table. Now, some employees are supervisors or managers. An employee can only have 1 manager/supervisor but the hierarchy can be varying depths. For example...

I have the following employees:

Bob, Jill, Tom, Bill, Harry, Jane, Amy, Jim, Fred, Sue and June

The management hierarchy works like this:

Bob
/ | \
Jill Tom Bill
/ \ | \
Harry Jane Amy Jim
| |
Fred Sue
|
June

How can I create these relationships in the database? I don't want a Manager table that duplicates all the information for employees for the managers. Besides, a manager can have a manager who can have a manager... and so on. There is probably a simple solution to this problem but I'm not seeing it. Any help would be appreciated.

Thanks,

Andrew

Hi Andrew

There are many ways to model a hierarchy, the most common one and simplest one is the adjacency list model. An example of this is in the Adventureworks database where the Employee table has a ManagerId column and the relationship is enforced by the foreign key FK_Employee_Employee_ManagerID. The top/root of the hierarchy is the CEO with a NULL value for the managerid.

With SQL 2005 onwards you can use CTEs to traverse up and down the hierarchy, which may be quite slow with a deep hierarchy, but on the plus side updates are quick. In SQL 2008 you have a hierarchyID which with methods that will help you find parents and children. This can require more work to maintain the hierarchy but retrieval should usually be quicker. There are other methods but they end up being more complex with their own trade offs.

John

.

Relevant Pages

  • Re: Books on why programmers think more than coding
    ... > The reason the technicians all left, was becuase the boss was a total PHB ... This "hierarchy" can be inefficient too, ... employees badly and such... ... all...that in the "Chinese Whispers" chain up the hierarchy, a "manager" ...
    (alt.lang.asm)
  • Re: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)
  • Re: OT: SQL
    ... said was a totally failed attempt to formalize and automate database ... query processing. ... create table Employees ... Manager and Employee Salaries. ...
    (sci.logic)
  • Re: OT: SQL
    ... said was a totally failed attempt to formalize and automate database ... query processing. ... create table Employees ... Manager and Employee Salaries. ...
    (sci.logic)
  • Re: How to make it so users see only certain records in a database
    ... The database stores information about the performance of the ... information about each employee entered by each manager. ... I have a table called Employees with the ... A combo box in the form uses this table as its record source. ...
    (microsoft.public.access.forms)