Solved

recursive relationships

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

773 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