Re: Table Design Question: Foreign keys when child has one of two parents

> My question is this: should I have personid and businessid as two separate
> foreign keys in ADDRESS? Or should I have only one foreign key (say,
> address_ownerid) that is populated with personid if it is a person's
> address or populated with businessid if it is a business's address?
> (Because personid and businessid are generated by the same sequence, there
> would be no duplication.)
> Is there a consideration I am overlooking which makes one method clearly
> preferable over the other, or is either one acceptable? In general, what
> is the best way to handle this situation? Thanks in advance for your help.

Todd,

The best way to handle this is with either a intermediate table that relates
all three tables. This table has as columns address_id, person_id, business_id,
and possibly a unique key column of its own or use all three id's as a primary
key. The reason is that your first option does not allow for the one to many
relationship with either your business or person table. More than one person (or business) can have the same address, so a person_id in the address table only
indicates one person. Your second option forces you to query both tables to find out if the address is for a business or a person.

Nevin Hahn Received on Sun Sep 14 1997 - 00:00:00 CDT