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

Question

Was designing a table a customer_phone_numbers table a while back, which only purpose was to allow us to store as many phone numbers as a user wanted to have on file. I originally came to the conclusion that customer_id + phone_number was unique, but then it occurred to me, it is possible for the same user to have 2 extensions at the same number. I am not sure how likely that is though, and it creates another issue.

phone_number ext
555-555-555, NULL
555-555-555, NULL

becomes legal, unless I find a good way to constrain ext against null. What is the best way to normalize and constrain phone numbers?

Answer 1

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.

Answer 2

Just throwing it out there: you could instead put your PK on { CustomerID, PhoneType }. You'd be storing a little context on what each phone number means, which could be useful.

If you make PhoneType a free-text string rather than an FK to a PhoneTypes dimension table, you allow an indefinite number of phone numbers per customer (or you could make it an FK but allow users to create new phone types, though I wouldn't take it far). By all means, encourage users to pick from a list of common phone types (work, home, cell, fax) first, though.

Answer 3

You cannot create a UNIQUE constraint and allow NULL in sql server, so i guess you can store default value 0 in the ext, and when displaying it you use case to display ext as empty value

Select 
customer_id, 
phone_number, 
ext = case when ext = 0 then '' else ext  
end