Solved

recursive relationships

Posted on 2004-03-22
4
384 Views
Last Modified: 2008-02-20
i have a table employees.  
each employee has a manager.
a manager is also an employee that is stored in the employees table.

over their lifetime an employee could report to many different managers (depending on promotions, demotions of merges of departments.)

How can i model this?
ensuring that there is a history of every manager the employee has reported to over their lifetime.

thanks
0
Comment
Question by:MrKevorkian
[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
4 Comments
 
LVL 13

Assisted Solution

by:danblake
danblake earned 50 total points
ID: 10647685
employee table (Psuedo SQL):

employee_id int (identity 1,1) NOT NULL
manager_id int NULL,
fname varchar(50),
lname varchar(50),
datecreated datetime default: getdate()

Create a PK on employee_id,
then add a FK on manager_id pointing to employee_id -- this ensures that a manager is an employee of the company.

Then create an audit of this table using something like the auditing from:
http://www.nigelrivett.net/AuditTrailTrigger.html

into an employee_history table.

Then current records will be stored from employee table, historical info from employee_history table.

0
 
LVL 1

Accepted Solution

by:
malcolmbegg earned 250 total points
ID: 10648347
I have done a similar thing using Effective/Until datetimes.

Create a seperate table to hold your manager/employee relationships:

EmployeeId int not null,
ManagerId int not null,
EffDateTime datetime not null,
UntilDateTime datetime

EmployeeId, ManagerId and EffDateTime together are your primary key.
EmployeeId is a foreign key to your employee id field in your employee table.
ManagerId is also a foreign key to your employee id field in your employee table (i.e. a manager is an employee!).

An entry is made into the table to show that from the given EffDateTime, the employee (EmployeeId) has a manager (ManagerId).  When this is no longer true, set the UntilDateTime value to something other than null.  This means that no data is deleted regarding the relationship between employee and manager, and a history can be reconstructed for any point in time.

Malcie.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10648725
depends at what level your attempting to model

basically you are recognising the Time element in your design

and will have to consider its impact throughout

Employee
Manager
Department
jobs  grades etc

all have life cycles

will an employee who leaves and returns later be the same employee id or separate ones?

 
the two basic ways of achieving this are

Separate Current and History Tables
1 table including all the data with relevant  periods for which its is valid indicated...

for the keys  you can either go for generated keys
 or compositie keys which include a StartDate (and a Sequence number potentially)


Both methods allow you to have a table which is basically just the  ID column upon which
you can base relationships...

 





0
 
LVL 1

Author Comment

by:MrKevorkian
ID: 10656206
great answers.
both work but i went with malcolmbegg's idea.


thanks
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

628 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