|
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.
| [reply] |
|
|
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)
| [reply] |
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...
| [reply] |
|
|
| [reply] |
|
|
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)
| [reply] |
|
|
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.
| [reply] |
|
|
Actually, touch tones already are HEX, including *, #, and a fourth column of A, B, C, D
| [reply] |
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...
Elgon
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! | [reply] |
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.
| [reply] |
|
|
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")
| [reply] |
