>>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
>
>Thomas,
>
>Examples A. and B. (with a slight modification) are good options. Example C. is not that great.
>
>Example B is very close, but it needs two more tables to make it properly normalized:
>

>              Store      Employer      Corp.
>                   \   _/   |    \_    /
>                   /\ /\    |     /\  /\
>               EmployerStore|   CorpStore
>                            |
>                           /\
>                        Employee
>
>

>However, properly normalized tables may not be the right solution to this problem. There are performance issues and, of course, the complexity issue that can be more important than having a perfectly normalized database.
>Modern DBMS are perfectly capable of handling NULL foreign keys and orphaned child rows.
>
>Also, if you have separate tables for Corp. and Store, then there will always be structural changes when adding more parent types. Having the EmployerStore and CorpStore tables in the middle will minimize the impact of the structural changes (new FK columns go into these two tables only).

Keith,

Thanks for your thoughts. However, I do not follow your reasoning. Why are the new tables EmployerStore and StoreCorp needed for normalization? (And do you mean EmployerCorp?) A Store or Corporation is related to at most one Employer. Did you add the EmployerStore table just to avoid having NULL foreign keys in those Stores that are not Employers?

Also, if we have, say, a table Warehouse and we want to make it an employer, wouldn't we have to add a table EmployerWarehouse? I don't see why we would add new FK columns to EmployerStore or CorpStore when adding more parent types.

---Tim