Solved

recursive relationships

Posted on 2004-03-22
4
350 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
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 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

23 Experts available now in Live!

Get 1:1 Help Now