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

You could use a discriminator column combined with the general adress_owner column. Use 1 column in adress for Owner-type. (P[erson] / B[usiness]

HTH, Tolli

MRife_at_admin.usf.edu (Michael A. Rife) wrote:

>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 Wed Sep 17 1997 - 00:00:00 CDT