foreign key design question; multiple parent tables

[Christopher Glaeser]

Suppose I want to generate a fax from various forms/tables. For example,
suppose I generate a fax from the contact information in either a WorkOrder,
Memo, or Contact and want to maintain the parent/child relation among these
tables. So, tblFax includes a ForeignKeyType/ForeignKeyID pair that may
point to the WorkOrderID, MemoID, or ContactID, and the field ForeignKeyType
specifies which table for ForeignKeyID is associated.

Is this a reasonable design? In other words, is it a good design to use a
ForeignKeyType/ForeignKeyID pair to point to more than one parent table?
Are there any pitfalls I should be aware?

Best,
Christopher

 

[Tim Ferguson]

So, tblFax includes a
ForeignKeyType/ForeignKeyID pair that may point to the WorkOrderID,
MemoID, or ContactID, and the field ForeignKeyType specifies which
table for ForeignKeyID is associated.

Is this a reasonable design?

It's not robust, but if the Faxes table is temporary that may not be a
problem.

The full solution is a method called subtyping. Start with one table called
FaxableThings, which has a PK field and any fields that are common to the
other types (there must be some; otherwise there is little point in trying
to collect them together). You link this table to the three other ones
WorkOrders, Memos, and Contacts -- their PKs are also FKs referencing the
FaxableThings table. This has two effects: one is to ensure that there is
no WorkOrder with the same PK as any Memo etc; the other one is that you
have a single target for the FK field Faxes(FaxedThing) to point to.

The advantage of this is that you can enforce that Faxes(FaxedThing) always
points to a valid object. The disadvantage is obvious: that it forces a
huge contortion on the rest of the db design, which your business model
may not be able to tolerate. In the end it comes down to this -- how much
do you care that the Faxes table contains a reference to Memo 0922 that no
longer exists? If that is a business-breaker, you need a robust design, and
probably a pretty sophisticated one. If it doesn't matter very much, then
go with the simpler method and free up the rest of the design; in fact, I'd
wonder whether you really want or need the Faxes table at all. What about
an old-fashioned box file?

Hope that helps


Tim F