Re: One-to-many with a twist

martind-crap1_at_mailblocks.com wrote:
> Hi,
>
> What is the best way to model this: Assume I have two objects: Agency
> and Publisher, and both have a 1-to-n relationship to Employee. This is
> a true 1-to-n relationship, as each Employee can only work for one
> Agency or one Publisher. Let's assume further that I cannot introduce a
> supertype (e.g. Employer) which holds the 1-to-n relationship.
>
> My preferrd solution is to have a foreign key in Emplyee that can
> either link to a primary key of Agency or Publisher (all my primary
> keys are 64-bit IDs that are unqiue across the database). However, now
> I won't be able to map a bi-directional association, without indicating
> in Employee whether this is an "Agency" or "Publisher" relationship
> (ala <ANY>).
>
> My other option is to use two tables, AgencyEmployee and
> PublisherEmployee, which can then be linked as traditional 1-to-n
> bidirectional associations.
>
> What do you guys consider best practice in this situation?
>
> Cheers,
>
> Jen

Over a period of time an employee can be both even if at any specific point-in-time only 1. I would model it as a many-to-many with an intersecting entity and a unique constraint to make it impossible for someone to simultaneously be both if such is the case.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)