"One-to-many" modeling question

Question

What is the best way to model the following...

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 preferred solution is to have a foreign key in Employee that can either link to a primary key of Agency or Publisher (all my primary keys are 64-bit IDs that are unique 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.

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 consider best practice in this situation?

UPDATE: Thanks for the great responses in such short amount of time! What do you think of the following solution: Foreign keys in Employee for both Agency and Publisher, such as agency_id and publisher_id?

Answer 1

Sounds like this could be modeled as a ternary relationship. See if you like the approach taken in this MSDN article.

Answer 2

The best practice would probably be introducing an Employer class.

Dividing Employee into AgencyEmployee and PublisherEmployee, on the other hand, would be a Bad Thing™ to do.

If introducing Employer was out of the question, I would indicate the employer type (Agency or Publisher) in Employee.

Response to the updated question:

That is an option, but employer_type is better because:

1. You might want to add other employer types in the future
2. There will be an empty field in each row of the Employees table.

Answer 3

My personal preference would be to have a table you expressly assume can not be used (the SuperType of Agency and Publisher; Employer)

A simple alternative would be to simply not enforce the foreign key constraint. This is not necessarily problematic, provided all modifications to the data structure is done through Stored Procedures which you control.

My final preference would be similar to your suggestion, of having one table for AgencyEmployees and one for PublisherEmployees. But I would separate this out one step further...

Table1: Agency              (id, name, etc)
Table2: Publisher           (id, name, etc)
Table3: Employee            (id, name, etc)
Table4: AgencyEmployees     (agency_id, employee_id)
Table5: PublisherEmployees  (publisher_id, employee_id)

One reason I would do this is that it becomes trivial to make this time dependent. Employees start, leave and move around between companies. The following slight amendment to the above would allow you to track that...

Table1: Agency              (id, name, etc)
Table2: Publisher           (id, name, etc)
Table3: Employee            (id, name, etc)
Table4: AgencyEmployees     (agency_id,    employee_id, start_date, leave_date)
Table5: PublisherEmployees  (publisher_id, employee_id, start_date, leave_date)

This does not enforce 1 employer per employee at any one time, but that can be enforced by your stored procedures, GUI, etc...

Answer 4

I have used this approach:

Employee.EmployerType ('AGENCY' or 'PUBLISHER')

Employee.EmployerID (AgencyID or PublisherID)

It works ok, but you need a case statement in a lot of your SQL. Also you if you are using an ORM you loose the FK relationship.

I am moving most of my code to the n:m relationships so that I can take advantage of our ORMs FK support.

Answer 5

Clearly an Employer entity would be preferred, but otherwise...

Having AgencyEmployee & PublisherEmployee tables means it will be difficult to determine if an employee belongs to one category or another.

So instead I would add and EmployerType column to the Employees table; it adds a little extra complexity to your subsequent SQL queries, but it works within the constraints you have given.

Out of interest, why would you be able to add AgencyEmployee & PublisherEmployee tables, but not add an Employer table? Just curious...

Answer 6

A +1 for Can Berk Güder.

Technically, introducing an Employer supertype means you introduce an Employer relation with all the attributes common to Agency and Publisher, an AgencySpecific (not sure how to call it) relation with the Employer key and all Agency-specific attributes and the Employer key as a foreign key, then Agency as a query or view that just joins AgencySpecific with Employer, and likewise for Publisher.

It may be a little cumbersome to have Agency and Publisher data spread across two tables that you have to join every time, but the alternative is to write most of your queries related to employees twice, once using Agency and once using Publisher, and then handcraft their unions to combine the results. (I haven't seen a query builder that helps out with basic stuff like this.) I've had to do it for a project that used the "no supertypes" approach and I don't want to do it again.

BTW ternary relationships and even n-to-n relationships aren't good design practice if you ask me. Using only functions (i.e. 1-to-n and 1-to-1) e.g. constraints become a lot easier to formulate.