Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

Standard Database format for phone numbers

I am looking for the recommended standard for storing phone numbers in a database. I would like this standard to work with demostic and internal formats. I have always stored phone numbers like "602-555-1212" but was wondering if I should store just the numbers and handle the formatting on the UI.

Thursday, April 21, 2005

What's recommended depends on what you think is important.

Should the database trust people to get their numbers right, or should it do validation?

Should it allow for international numbers, which can vary widely, or should it only allow U.S. numbers?

Should you let people put in their own formatting?  If you do, you'll have things like (555) 555-5555 and 555-555-555 and 555 555 5555.  But if you don't, you'll have a heck of a time trying to format international numbers properly.  (In fact, if you're going to validate and format international numbers, surely there's a product you can buy that's better than rolling your own.)

The only solid, universal recommendation I know of is that phone numbers should be stored as text and not as integers or something.

Thursday, April 21, 2005

==>The only solid, universal recommendation I know of is that phone numbers should be stored as text and not as integers or something.

For me, it's always char(25) -- or however your database of choice designates a character sting of 25 digits.

This allows enough space for the user to input anything -- international, extensions, etc.

The only time I have ever done differently, was a system that maintained about 5,000 vendors located around the country. The daily orders were automatically faxed (yes *faxed*) to these vendors on a nightly basis (this was pre-internet -- at least the internet as we know it today). A lot of these vendors did not have email, or even an ISP.

The system spoke directly with the fax/modem, and therefore that phone numbers had to be "just right", and the free-form char(25) phone number was dumped in preference for a straight numeric format with no formatting.

I prefer the free-form text to allow the user to enter the phone number any old way they want, but if there's going to be any automatic dialing of any kind, you'll want to define a single format and stick to it for consistency's sake.

Thursday, April 21, 2005

Free text is your best bet. Most people presume the default format should be:


However with the way formats go things keep changing.

* Some people dial their area codes differently when dialing locally as oppose to internationally. (in Toronto we'd dial 416-555-5555 because the system won't assume 416. Whereas back when I was in Honolulu a call didn't require the area code). In other cases area codes with preceeding zeros are omitted when dialing them in an international call.

* Area codes and phone numbers shrink and grow as needed.

So don't attempt to hard code text size--it will just give you head aches.

Another thing that will drive you numbers is the number of contact numbers people have. Fax. Home. Office. Voice mail. Pager. Extension Number for any given number. And the latest craze--passcodes. On and on....

Perhaps you'll end up using a schema like VCard and serialize it into a BLOB/XML field.

Li-fan Chen
Thursday, April 21, 2005

In countries like Israel and Taiwan the number of cell phones per capita is staggering. People with 5 or 6 SIM cards each associated with a number is the norm. Disposable cell phones are coming too.

Li-fan Chen
Thursday, April 21, 2005

>Should the database trust people to get their
>numbers right, or should it do validation?

That's what I do. After all, if you can't trust them to write out their telephone right then do you really want to call them at all?

Colm O'Connor
Friday, April 22, 2005

I would have three elements - the international (country) code, the area code and the local number.  Store each as a string.

Formatting will vary on the ccountry.  Australia, for example, has two digit area codes, but 8 digit local numbers.  But then mobile numbers have a 4 digit "area" code, with a six digit local number.  The UK has a different convention.

Ken Ray
Friday, April 22, 2005

*  Recent Topics

*  Fog Creek Home