>I have a situation where a store is assigned to a regional manager who in turn reports to an area manager who in turn reports to a regional area vice president. The issuse is that Store A may be assigned to Joe Blow from January 1 - June 1 then be assigned to Sally Sue after that (restructuring). What is Joe Blow decides to move on in May (promotion or leave the company?).
>
>What is the best way to structure data tables to handle this type of hierarchical situation? Should it be with a many to many intersection table with a date start and end or a history (archive) table hanging off each? of course all of this should be handled via the use of primary and foreign keys - just need to have everything reflect the current (latest) environment. Once the data is properly structured, I anticipate creating a view to unify the output for end user use.
>
>I suspect this type of situation is a common occurance. Anyone worked with this type of data before or have some suggestions for the best way to handle it?

Tom

The way I see it is quite simple:

STORE
Store#, Store Name, etc.

EMPLOYEE
Emp#, Emp Name, etc.

IS_MANAGER (1-to-1) reln. between store and employee
Emp# (FK to EMPLOYEE), Store#, Reps_to# (another emp# FK to EMPLOYEE, giving person he reports to),
Date Start, Date End, etc.

HTH

Terry