views:

1117

answers:

9

In the last 3 companies I've worked at, the phone number columns are of type varchar(n). The reason being that they might want to store extensions (ext. 333). But in every case, the "-" characters are stripped out when inserting and updating. I don't understand why the ".ext" characters are okay to store but not the "-" character. Has any one else seen this and what explanation can you think of for doing it this way? If all you want to store is the numbers, then aren't you better off using an int field? Conversely, if you want to store the number as a string/varchar, then why not keep all the characters and not bother with formatting on display and cleaning on write?

I'm also interested in hearing about other ways in which phone number storage is implemented in other places.

+3  A: 

one point with storing phone numbers is a leading 0.

eg: 01202 8765432

in an int column, the 0 will be stripped of, which makes the phone number invalid.

I would hazard a guess at the - being swapped for spaces is because they dont actually mean anything

eg: 123-456-789 = 123 456 789 = 123456789

Pondidum
Leading 0 is a great point for sticking with strings for phone #s...
Codewerks
+14  A: 

Quick test: are you going to add/subtract/multiply/divide Phone Numbers? Nope. Similarly to SSNs, Phone Numbers are discrete pieces of data that can contain actual numbers, so a string type is probably most appropriate.

Codewerks
Great logic. It's so easy to get stuck in the mentality of thinking of numerals as numbers. You're so right. Here, the numerals are not really numbers but string/ text/ varchar type. I love when someone points out assumptions I make without realizing I make them.
Dinah
Yes, a string type is most appropiate - as long as it has check constraints.
Mark Brackett
Agreed. Zip codes, street numbers are other good examples of fields you would not want to make numeric.
dpurrington
Actually, that's not entirely correct. In a database, you will need to index the various parts of the number for performance reasons. And what if one of your requirements is a list that's sortable by NPA or NXX? Store the string as entered, and also store separate integer fields as needed.
Eric Z Beard
The other problem of making them numeric/int is that you can't have leading zero
faulty
A: 

Stripping some characters and allowing others may have an impact if the database table is going to drive another system, e.g. IP Telephony of some sort. Depending on the systems involved, it may be legitimate to have etc.333 as a suffix, whereas the developers may not have accounted for "-" in the string (and yes, I am guessing here...)

As for storing as a varchar rather than an int, this is just plain-ole common sense to me. As mentioned before, leading zeros may be stripped in an int field, the query on an int field may perform implicit math functions (which could also explain stripping "-" from the text, you don't want to enter 555-1234 and have it stored as -679 do you?)

In short, I don't know the exact reasoning, but can deduce some possibilities.

ZombieSheep
Wouldn't it be smarter to store the data in a more human readable form, and then parse out any necessary characters at the instant before sending it to the telephony system.
Kibbee
It probably would, yes. I'm not designing a system, just offering possible explanations. :)
ZombieSheep
+2  A: 

Personally, I wouldn't strip out any characters, as depending on where the phone number is from, it could mean different things. Leave the phone number in the exact format it was entered, as obviously that's the way the person who typed it in is used to seeing it.

Kibbee
A: 

I'd opt to store the digits as a string and add the various "()" and "-" in my display code. It does get more difficult with international numbers. We handle it by having various "internationalized" display formats depending on country.

Jim C
A: 

It doesn't really matter how you store it, as long as it's consistent. The norm is to strip out formatting characters, but you can also store country code, area code, exchange, and extension separately if you have a need to query on those values. Again, the requirement is that it's consistent - otherwise querying it is a PITA.

Mark Brackett
A: 

What I like to do if I know the phone numbers are only going to be within a specific region, such as North America, is to change the entry into 4 fields. 3 for area code, 3 for prefix, 3 for line, and maybe 5 for extension. I then insert these as 1 field with '-' and maybe an 'e' to designate extension. Any searching of course also needs to follow the same process. This ensures I get more regular data and even allows for the number to be used for actually making a phone call, once the - and the extension are removed. I can also get back to original 4 fields easily.

Darryl Hein
A: 

Good stuff! It seems that the main point is that the formatting of the phone number is not actually part of the data but is instead an aspect of the source country. Still, by keeping the extension part of the number as is, one might be breaking the model of separating the formatting from the data. I doubt that all countries use the same syntax/format to describe an extension. Additionally, if integrating with a phone system is a (possible) requirement, then it might be better to store the extension separately and build the message as it is expected. But Mark also makes a good point that if you are consistent, then it probably won't matter how you store it since you can query and process it consistently as well.

Thank you Eric for the link to the other question.