Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

Question about phone number storage.

by rfb (Sexton)
on Jan 18, 2001 at 06:28 UTC ( #52672=perlquestion: print w/replies, xml ) Need Help??

rfb has asked for the wisdom of the Perl Monks concerning the following question:

Whats the average pm's thoughts on storing phone numbers in a database, which column type is generally used? my boss, and most others I've talked to say I should use int, I'm thinking varchar, .. mainly because no numeric calculations will be done on a phone number.. hmm. -rfb

Replies are listed 'Best First'.
Re: Question about phone number storage.
by lzcd (Pilgrim) on Jan 18, 2001 at 06:32 UTC
    My Choice (TM) is Var Char as it avoids a lot of the possible nasty conversions that could face a large int.

    Also it allows for future alpha things such as the international '+' and the area code '()'s.

    Update:Also using int's brings up a few nasties when it comes to leading zero's. eg. 02 987654321 becomes 2987654321.
      Unless the database is huge or performance isn't a concern you should probably just use a char field. The real payoff of varchar is wildly variable data where most of the time the varchar field would be small. In these days of huge hard-drives you might as well fix the field at the largest size and get the performance win of the DB knowing how many records per page you will have. If neither performance nor size matter, do what-ever makes you happy! =)

      varchar for size, char for speed

      P.S. The same boss that insists on int's will one day ask you how you can get (1-555-use-perl) in that DB...

      $you = new YOU;
      honk() if $you->love(perl)

Re: Question about phone number storage.
by wardk (Deacon) on Jan 18, 2001 at 09:57 UTC

    Worked on more than a few systems storing phone numbers. I've encounter char, varchar(2) and even pic x(n), but cannot recall having a phone number stored as an integer. In fact most of the storage methods have been so freeform (lazy coding?) as to have caused numerous datascrubs to excise or maybe make uniform the hodgepodge of ways people enter phone numbers. (argument for integer?)

    lzcd makes great points about non numeric characters for area code and international numbers. I suggest that if you avoid integer, make sure you take measures to edit/format the numbers uniformly right up front.

    and...imagine if someday we begin using HEX phones due to lack of numbers. an int won't handle a-f . maybe tell your boss that this could someday dwarf the Y2K problem ;-)

    I wouldn't figure you'd draw any lines in the sand over this, if the boss is clamoring for an integer...

      I hate DBs that store phone numbers as (NNN)NNN-NNNN. What, you don't ever want any money from outside the United States?

              - tye (but my friends call me "Tye")
        I once had to deal with a DB (of my own creation, gone wild) that had three fields for the three parts of the NANP number. We wound up stuffing international into it by making the first field a varchar that allowed up to 8 or 9 digits, making the second field NULL allowed and making the last field a varchar that could hold up to 15 digits. Thus international numbers had a huge areacode and a huge local number and could be distinguished from NANP numbers by having a null NXX.

        I am still worried about buring in hell over that one. Of course we were able to get away with minor perl changes and didn't have to change one script at all. Still, I'm pretty sure I owe a few coders beer just for not complaining louder in the meeting when they decided to do it.

        $you = new YOU;
        honk() if $you->love(perl)

        Why yes. Canadians are allowed to pay. But you can't ship anything to them, and will confuse them about the date they won't be be able to get things by.
      Actually, touch tones already are HEX, including *, #, and a fourth column of A, B, C, D
Re: Question about phone number storage.
by Elgon (Curate) on Jan 18, 2001 at 16:33 UTC
    Ah, something close to my heart - one of the first serious programs I wrote in Perl was a script that generates a 'contacts page' from a MySQL database pauses briefly to reminisce over carefree days of coding.

    Varchar is definitely the way to go - you don't need to do any arithmetic, (as has been pointed out) leading zeros are not cut and what you are entering isn't really a number, it is a string of digitsNote: I'm sure that made sense the first time round.

    Remember to make your field nice and big for when they decide to change number yet again etc...


    UPDATE: I knew I forgot something - If you want to give international phone numbers, ie. something like +44 (0) 1234 765 4321, with spaces and brackets varchar is obviously the way to go because it allows this kind of encoding and also allows numbers of different formats.

    Update on UPDATE I just noticed that lzcd already posted this reason, oh well, goodbye originality!

Re: Question about phone number storage.
by bastard (Hermit) on Jan 19, 2001 at 03:23 UTC
    I've been struggling with this one for a while on a DB i've been creating.

    Basically I agree and disgree with what has been said so far.
    Phone numbers are quite complex when you look at all the telecoms in the world. Nothing is standard. For this reason I think I agree with varchar. (mostly to get the breaks right)

    Don't store country codes with the numbers. Store the country(prolly full address too) of the number in the DB and have the country code and whatever extra dialing specifics added on the fly based upon where you are.
    At least that's the direction I plan of traveling down.

    The other question is how big? I'd love to know the answer to that one. The largest number i have seen (with country code I think) was about 17 chars. I wasn't even thinking about extentions at the time (probably better as a separate field). I went with about 20 chars, just to cover everything I could think of. If anyone knows what the longest phone number is (with and without country code) i'd be very interested.

      From my vague memory, the ITU used to limit phone numbers to a total of 16 digits (that includes country code). They recently (within the last 3 years) had to increase that limit and I think it is now 20 total digits which means that there aren't any phone numbers that long yet. So you'd probably want to allow something like 32 character so you've got room for formatting. Having to decide field lengths is one of the things I hate about most relation databases.

      I'd recommend storing the country code with the phone number myself.

              - tye (but my friends call me "Tye")

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://52672]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2021-11-27 12:22 GMT
Find Nodes?
    Voting Booth?

    No recent polls found