One-to-many with a twist

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 Received on Mon May 30 2005 - 11:34:38 CDT