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

Since you really have 2 one-to-many relationships, why not create 2 tables. Create one table to store your PERSON to ADDRESS relationship. Create another table to store your BUSINESS to ADDRESS relationship. If you want all the relationships in one "table", then create a view which is the UNION of the 2 relationship tables.

In article <01bcbfca$a6f05ac0$764c1bcc_at_ToddO.gcr1.com>, toddo_at_gcr1.com says...
<
<
<I would like some advice regarding foreign keys in a situation where a
<child table has an either-or situation with regard to the parent.
<
<I have a PERSON table that stores info about people, a BUSINESS table
that
<stores info about businesses, and an ADDRESS table which stores all
<addresses. The primary keys are personid, businessid, and addressid,
<respectively, and are all generated by the same sequence. There is a
<one-to-many relationship between PERSON and ADDRESS, and there is a
<one-to-many relationship between BUSINESS and ADDRESS. An address
belongs
<to either a person or a business, but not both. As a consequence, the
<PERSON-ADDRESS and BUSINESS-ADDRESS relationships are optional from the
<standpoint of ADDRESS.
<
<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.)
<
<I understand the pros and cons of each method to be as follows:
<1) Two separate foreign keys
< Pro: no search of PERSON or BUSINESS is necessary to determine if
the
<address belongs to a person or business.
< Con: wasted database space, because one of the fields will always
be
<empty.
<2) One foreign key
< Pro: no wasted database space.
< Con: must search PERSON, BUSINESS, or both to determine if the
address
<belongs to a person or business.
<
<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 Owers
<ToddO_at_gcr1.com
Received on Tue Sep 16 1997 - 00:00:00 CDT