asked on

recursive relationships

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

SOLUTION

danblake

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

malcolmbegg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

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...

MrKevorkian

ASKER

great answers.
both work but i went with malcolmbegg's idea.

thanks