How can I enforce that a record be a child of only one record when it can be a child of several tables?

Question

I'm brainstorming how to restructure the contact information of a database. As you know, phone numbers can be linked to a person (cell phone), to a family (home phone), to an organization/business, etc. Logically, a phone number is a phone number is a phone number. There's no real difference between a cell phone number and a home phone number. And a person can have multiple cell phones, a family can have multiple phone lines, and an organization can have many many phone lines.

Normally, when designing tables, that means there should be a single phone number table. And it should link in a one to many to persons or families or organizations. The rub is, how would someone enforce that a phone record is only owned by a single parent record, whether that record be a person record, or a family record, or an organization record?

The only two ways I've figured out are kludges, in my opinion. I want an elegant solution.

The first is to create 3 tables, PersonPhones, FamilyPhones and OrganizationPhones. Then you've got 3 tables whose mission is to store essentially the same data.

The second is to create a single phone table with a weird structure. It would have phone number, a nullable field for a person id, another for a family id and a third for an organization Id. Then add a constraint that enforces that 2 of those 3 are NULL.

Any ideas?

Answer 1

You can have 3 columns in your PhoneTable (Person_Id,Phone_Number, and Phone_Details). In the Phone_Details column you can mention whether its a cell phone or home or organization. Then you can define a unique key on the combination of Person_Id and Phone_Details.

Answer 2

There are generally two ways to deal with a situation like that:

• One way is to use inheritance - crate a "PhoneOwner" table that will be the logical parent of a Person, Family, Organization and any other entity that needs to be able to have a phone. Then just link the Phone to the PhoneOwner.
• The other is to use exclusive foreign keys, which you already described in your question.

Neither is ideal and you'll have to decide which one better fits your needs.

See also: "What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys?"