Solved

recursive relationships

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.

756 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