Results 1 to 8 of 8
  1. #1
    Join Date
    May 2007
    Posts
    24

    How to store phone numbers and zip codes

    My current database stores these two fields as integers, but might be better if they are store as Strings so no conversion would be needed.

    Would that be the correct path?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    yes
    not all phone numbers are (999) 999 9999
    similarly not all postal codes are numeric

    UNLESS you see you application as specific to one geographic location and you never see a requirement to store inforamtion form outside that geographic location

    IF you know that all your rows containing addresses are US based then a integer may be reasonable

    If you know that all your rows containing phone numbers are North American then you can use the (999) 999 9999 format

    On phone numbers irrespective of where you are are I think you need to separate any exchange / area codes.

    Arguably you could consider pre-pending the international number prefix (eg +1 or +44.. although that can be derived assuming that you store the country separately. In my books it used to be a cunning trick to equate the country name with its international country code.. that was until so many counties started using the 001 prefix.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    unless you plan to retrieve the average phone number or average zip code for some group, there is no point in using a numeric datatype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    If you don't add it, subtract it, multiply it, or divide it, then it is a string even if it looks like a number.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ditto. Maybe throw in some sort of check constraint if you want to ensure people do not enter things like "see x person's phone number". They will if you let 'em
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh! oh! a CHECK constraint!

    this puts you in the "database mensa" category -- the top 2% of database practitioners, who not only know what a CHECK constaint is, but when to use one

    full marks, pootle
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    oh! oh! a CHECK constraint!

    this puts you in the "database mensa" category -- the top 2% of database practitioners, who not only know what a CHECK constaint is, but when to use one

    full marks, pootle
    Pfff - that's nothing. Normally I create a table of all the possible phone numbers and create a foreign key

    Quote Originally Posted by r937 (guessed at by poots)
    zero marks, pootle
    Awwww
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hold on a sec -- do you foreign key all your phone number digits to a digits table?

    (if i may be permitted to use "foreign key" as a verb)

    if not, you have not fully normalized your phone numbers

    some purists, blindman among them, if i recall, then further foreign key their digits to a bit table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •