| |
|
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.
|
 |

06-06-07, 23:06
|
|
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?
|
|

06-07-07, 04:23
|
|
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
|
|

06-07-07, 05:04
|
|
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
|
|

06-07-07, 13:03
|
|
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"
|
|

06-07-07, 18:37
|
|
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.
|
|
|

06-07-07, 18:51
|
|
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
|
|

06-07-07, 19:57
|
|
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.
|
|
|

06-07-07, 20:07
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|