I'm looking for advice on a problem of data (or object) design.

The general situation, which has come up in a couple of instances with an application I'm writing, is that there is a class of object that can have different classes of "parent" objects. Here are some examples.

Ex. 1. An Employee can work for a Store or for the Corporation. So we have relationships "Employee works for Store" and "Employee works for Corporation", both of which have multiplicities n to 0 or 1, and we have the constraint that an Employee must work for a Store or a Corporation. In one scenario, we may want to say the Employee cannot work for both a Store and a Corporation. In another, an Employee may work for both.

Ex. 2. An Asset can be assigned to a Location or to a Company or neither. There is a constraint that an Asset may not be assigned to both a Location and a Company.

Ex. 3 A Job must be part of a Service Order or a Work Order or both. In this case the relationship "Job is part of Service Order" has multiplicity 1 to 0 or 1: a Service Order contains exactly 1 Job, and a Job may be part of 0 or 1 Service Order. On the other hand, the relationship "Job is part of Work Order" has multiplicity n to 0 or 1: a Work Order may contain one or more Jobs, and a Job may be part of 0 or 1 Work Order. It's also true that a Job which is part of a Service Order and a Work Order must be the only Job contained in the Work Order.

In each of these examples, the "child object" (Employee, Asset, or Job) has the same properties regardless of its "parent", but the "parent" classes are significantly different, having different properties and participating in different relationships. What is the best way to model these situations in a relational database? If there is no one best way, what are the most important considerations?

I can think of three approaches:

A. Create tables for the child and parent types and put multiple foreign keys in the child table to relate it to the parent tables. So in Ex. 1, the Employee table would have fields fkStore and fkCorporation, not both null.

This allows the constraints to be checked fairly easily, but it wastes space on null FK's and requires a structural change to add more parent types.

B. Create a table for a common abstract parent type and relate the child and the concrete parent types to this. So in Ex. 1, there would be a "Employer" table and each of the tables Employee, Store and Corporation would have a field fkEmployer.

This allows us to put common properties of the various parent types in the abstract parent, and does not involve null FK's, but it makes enforcing the constraints difficult.

C. Create a table for a commoan parent type and put the attributes of both parent classes in it. So in Ex. 1, there would be an Employer table which would serve as both a Store and a Corporation.

It seems pretty clear to me that this would be a bad idea, because of wasted space and complexity of relationships and constraints on the parent, except in cases where the parent types are very similar.

There are also lifetime issues, like what happens if we try to delete a parent object that has children?

Any wisdom you can offer would be appreciated.

Thomas M. Lamm
Head of Programming
Bradbury and Associates
www.bradburyandassociates.com