Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

recursive relationships

Posted on 2004-03-22
4
Medium Priority
?
390 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 200 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 1000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

670 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