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

From: Todd Owers <toddo_at_gcr1.com>
Date: 1997/09/12
Message-ID: <01bcbfca$a6f05ac0$764c1bcc@ToddO.gcr1.com>#1/1

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 Fri Sep 12 1997 - 00:00:00 CDT