If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > How to store phone numbers and zip codes

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 06-06-07, 23:06
CrazyTn CrazyTn is offline
Registered User
 
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?
Reply With Quote
  #2 (permalink)  
Old 06-07-07, 04:23
healdem healdem is offline
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 9,246
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #3 (permalink)  
Old 06-07-07, 05:04
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #4 (permalink)  
Old 06-07-07, 13:03
blindman blindman is offline
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 11,726
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"
Reply With Quote
  #5 (permalink)  
Old 06-07-07, 18:37
pootle flump pootle flump is offline
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
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:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #6 (permalink)  
Old 06-07-07, 18:51
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #7 (permalink)  
Old 06-07-07, 19:57
pootle flump pootle flump is offline
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
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:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #8 (permalink)  
Old 06-07-07, 20:07
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On