Storing Phone Numbers – Proper design

database-design

First off, I'm not a DBA, I'm a software engineer and have been building applications which are database backed for my entire career. One of the things I remember (maybe incorrectly) is that when designing the ERD that you take the real world into account as it serves as the context for your design.

I have a situation where we have the concept of a customer, who can have two and only two phone numbers. A customer can have zero to many addresses as well, and each address can have only two phone numbers (and a flag indicating if the number is a mobile number). The numbers on an address would only be used to contact someone at that particular address, while the numbers for the customer are used to contact the customer (who's address may not be in the system).

The design I came up with was to have a Phone1 Phone2 as columns on the customer table as well as on the address table. I've had others suggest that this isn't a good design and that I should have created a PhoneNumber table instead (I'm not sure how they were suggesting I relate the numbers to the other records).

Certainly I can see that being valid as well, but I either still need to have Phone1Id and Phone2Id in the customer and address table, or have something in the Phone table that tells me which record owns the number. The issue I have though is that of course makes the appliction logic more complex since I now need to add, remove or update the record in the phone table instead of just blanking or nulling the value in the respective tables.

Is my design also acceptable? Is one or the other preferable? Or are both just as valid?

Best Answer

The anti-null brigade would insist you normalize further to create the suggested PhoneNumber table. The practical folk would consider your original design perfectly acceptable. I'm in the latter camp.

Normalize until it hurts, de-normalize until it works.

Related Solutions

Fact table foreign keys null

You can leave the FK to some dimension tables as NULL if those dimensions are not known or not applicable. You just have to remember to use outer joins when you do your reporting query.

Alternatively, some people create a "none" and/or an "n/a" dimension record for data mart dimensions and then populate fact table FKs to point at these rather than using NULLs. People who do this like this approach because they have an aversion to outer joins.

People who use NULL FKs in fact tables usually have an aversion to people who have an version to outer joins. ;) (in other words, this is a stylistic issue which can generate religious wars)

I say do whichever you prefer, but pick one approach and stick to it fervently.

Is id and phone number enough to uniquely identify a row in phone numbers

There are probably ways to solve this (e.g. an instead of trigger rather than an explicit constraint, or a clever filtered index if on 2008+), but why?

As an example, you can do this with two filtered indexes in 2008+:

CREATE TABLE #cpn(c INT, pn VARCHAR(32), x VARCHAR(8000));

CREATE UNIQUE INDEX x ON #cpn(c, pn) WHERE x IS NULL;
CREATE UNIQUE INDEX y ON #cpn(c, pn, x) WHERE x IS NOT NULL;

INSERT #cpn(c, pn   ) SELECT 1, '555-5555';        -- OK
INSERT #cpn(c, pn, x) SELECT 1, '555-5555', '345'; -- OK
INSERT #cpn(c, pn, x) SELECT 1, '555-5555', '555'; -- OK
GO
INSERT #cpn(c, pn   ) SELECT 1, '555-5555';        -- fails on index x
GO
INSERT #cpn(c, pn, x) SELECT 1, '555-5555', '345'; -- fails on index y

But I fail to see the point. This seems like unnecessary flexibility. If I'm going to call the office and try to reach Bob at extension 55, why would I be any more likely to reach him at extension 67? Does this seem like a feasible, real-world scenario to anyone?

I suggest you make it a business rule that a user can only have one extension at the same number. This way you can keep your constraint on customer id and phone number, and allow extension to be optional.

Also, assuming you are storing phone number and extensions as strings, you can go the other way and let people enter them together instead of separate. If they want to enter 15 different extensions at the same number, why stop them? I also think it's silly to allow an extension of 0, but this would allow that also.

Related Question