relational design for phone numbers

Question

I have a database with phone numbers stored for three types of entities: customers, offices, and providers. The customers, offices, and providers are each in their own separate tables.

Should I have one phonenumbers table that has separate foreign key fields for customers, offices, and providers? Or should I have separate customerphonenumbers, officephonenumbers, and providerphonenumbers tables, each with only one foreign key link to the appropriate table?

Answer 1

Does a Phone Number need to be its own entity? It sounds more like an attribute which describes another entity, such as a Customer or an Office. In which case it would more likely just be a character field on the Office table, on the Customer table, etc.

After all, why is it necessary to normalize a phone number? Consider if two entities have the same phone number. Are they necessarily the same "phone number" entity, or just the same value for a value type? If both of those entities refer to a single instance of a Phone Number entity in the data, what happens when one of them changes their phone number? Does the other entity's phone number also change as a result? That doesn't sound like desirable behavior.

If you do separate the phone numbers into their own table, you should still treat them like value types and not entity types. That table can then be as simple as:

ID (PK, int, identity)
Number (character data)

Then in the Customer table, Office table, etc. you would just have a simple FK to that table:

PhoneNumberID (int, FK)

Or even multiple phone numbers:

PrimaryPhoneNumberID (int, FK)
SecondaryPhoneNumberID (int, FK)
MobilePhoneNumberID (int, FK)

etc.

This doesn't prevent duplicates in the Phone Number table, nor should it. Value types can be duplicated, and are essentially immutable and separate from other instances which coincidentally have the same value. But in the end, this doesn't really justify the need for a separate table. Phone numbers are just value types which describe an entity, they're not really an entity in and of themselves.

---

Edit: In response to your comment, is your data tracking the telecommunications routing for the phone number exchange, or just tracking a client's phone number (or set of phone numbers)? If the former, then a phone number does indeed start to look more like an entity in and of itself. It's uncommon in general, but certainly a reality in the telecommunications industry.

If you're not talking about a telecommunications database, but just a business database of customer data, then it sounds like you're trying to work toward a many-to-many relationship between entities and their phone numbers, which would still have the problem of breaking one entity's data when another entity changes their phone number.

You can avoid having multiple phone number fields on the Customer and Office tables by offloading it to a linking table, which may have 0 or more records for each entity. In that case we're talking about what I think Amirreza was alluding to in his answer. A table for phone numbers, possibly with a "phone number type" and maybe "entity type" as well. Something like this perhaps:

ID (PK, int, identity)
Number (character data)
NumberType (enumeration or FK to a lookup table of phone number types)
EntityType (enumeration of entities such as Customer or Office)
EntityID (int)

The problem here, however, is that EntityID isn't actually a FK since it could be a key to one of multiple possible tables. So relational integrity is a manual process, which isn't ideal. The EntityType enumeration is also kind of a hack. Perhaps a linking table for each entity would maintain the relational integrity more effectively? Something like this:

PhoneNumbers:

ID (PK, int, identity)
Number (character data)
NumberType (enumeration or FK to a lookup table of phone number types)

CustomerPhoneNumbers:

CustomerID (int, FK)
PhoneNumberID (int, FK)

OfficePhoneNumbers:

OfficeID (int, FK)
PhoneNumberID (int, FK)

This involves more small tables, but maintains the relational integrity of the data more effectively. In this case any given entity can have as many phone numbers as you like (including none), each of any given phone number, without having to add lots of potentially nullable phone number value columns to that entity's table.

Answer 2

It is better to have a phonenumber table with a column for type . for example 0 for customer 1 for offices and 2 for providers.