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?

  • Is your goal to allow a customer to use the same number but different extensions, or the same number and maybe NULL for one extension and a valid extension for another? – Aaron Bertrand Jun 10 '12 at 23:25

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));


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
INSERT #cpn(c, pn   ) SELECT 1, '555-5555';        -- fails on index x
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.

  • people consider storing them as some sort of INT? phone numbers are not numbers, but what I call "numeric codes" which means strings that consists of only digits, in which no math really makes sense. – xenoterracide Jun 10 '12 at 23:29
  • 1
    note varchar 4 may be insufficient for extensions, because some ridiculous softwares allow extensions up to 11 digits long, just saying. – xenoterracide Jun 10 '12 at 23:30
  • @xenoterracide yes, especially extensions. A lot of places you'll find use a separate column for area code, city code, local part, extension, etc. Makes validation easier. – Aaron Bertrand Jun 10 '12 at 23:30
  • @xenoterracide ok, I was just making up something quickly for demonstration, I have absolutely no way to guess your data model. You get the point, right? – Aaron Bertrand Jun 10 '12 at 23:31
  • to some degree, but if you're an international company I never wanted to deal with that complexity, 5 digit country code + 15 digit? phone number (I'd have to look) + 11 digit extension was enough not to worry about how the locals my store the local part. – xenoterracide Jun 10 '12 at 23:33

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.

  • 1
    Interesting, this method would also allow a voice number to be the same as a fax number (a legitimate scenario). – Leigh Riffel Jun 11 '12 at 17:13

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

ext = case when ext = 0 then '' else ext  
  • 3
    You mean that only one NULL is allowed when there is a UNIQUE constraint - in SQL-Server. – ypercubeᵀᴹ Jun 10 '12 at 21:11
  • 1
    but what if 0 is the valid extension... nothing prevents one from using 0 as extension (AFAIK though convention is an operator) – xenoterracide Jun 10 '12 at 23:02
  • 2
    Guys you can also get around the 0/1/many NULLs problem using a unique filtered index. I don't think it helps in this specific case, but I didn't want to leave it hanging here that you can't have NULLs in a unique constraint in SQL Server (which is false) or that you can't have multiple NULLs in a unique index in SQL Server (which is also false - a filtered index allows compliance with the ANSI version of a unique constraint, which unfortunately wasn't how Microsoft initially chose to implement it). – Aaron Bertrand Jun 10 '12 at 23:22
  • Aaron is correct. A partial Unique index can overcome this limitation now. – ypercubeᵀᴹ Jun 11 '12 at 7:56
  • It doesn't look like the OP ever said they were using SQL Server. – FrustratedWithFormsDesigner Jun 11 '12 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.