October 27, 2003 at 8:23 am
I have a table AAA and two columns a1 and a2.first column is of int datatype and the second column stores telephone number and it allow nulls.So what data type should i use,Int or varchar. and the format the number is going to be stored is 777 888 9999.ANd let me know the reason behind the proper use of the data type.
Thanks.
October 27, 2003 at 8:59 am
I would suggest a varchar or - if you are sure that almost all telefone numbers do have the same length a char column.
Why?
Well, I'm not familiar with US telefone numbers but here we do have a format like
02103 347282
02103 for the city, 347282 for the number of that participant?!?
When you store this in a numeric data type, you loose some information.
And what about international numbers
00492103347282
int is not enough for that kind of numbers, you'll have to go for bigint or decimal.
The formatting is job of your client app.
But I might be wrong on this.
Just my $0.02 cents.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 28, 2003 at 2:12 am
I generally use the rule that if you are performing any maths on the number, or if it represents a quantity, etc, then store it as int (or numeric, etc)... Things like post codes, phone numbers, etc should be stored as varchar/char as you can, as mentioned, keep your formatting becuase a phone number is just that - a formatted string of characters that happen to be numbers....
My 2c plus Frank's = 4c - hope that's enough advice 🙂
October 28, 2003 at 7:58 am
To toss a few more cents in and to emphasize a point of Frank's, I would recommend not including formatting characters in the telephone string.
In the US the stadard convention is
(xxx) yyy-zzzz
but that obviously is not the case elsewhere, as per Frank's
02103 347282
and who knows what else is out there--let alone how long it can get. (Varchar 50 suggests itself, for no particularly good reason.)
Now, if you absoltely, definitely, and definitively know that you'll always deal with only one kind of telephone number formatting, for now and forever, then you could include the formatting charactesr... but since they're always the same, why not parse them out and have the app toss them in? This way you save [US example] 4 bytes (big whoop) times the number of rows (2 million = 8M, big whoop) times every page read, fed through the CPU, and fed across the network during the life of your application (adds up over time, eh? I say save the resources for processes that need it.) From programming days I recall this kind of formatting issue being amazingly trivial, and these days it has to be easier, so accept no griping from lazy programmers.
If you don't have the luxury of only ever dealing with one formatting, <understatment> it gets tricky </understatment>. The options would seem to be either do load the formatting characters (and deal with issues such as, how do you ensure data entry gets them right?), or have some kind of associated code value indicating how the number is to be formattetd. Neither seems ideal, and I've often wondered how this problem has been dealt with by others.
Philip
October 28, 2003 at 9:02 am
To build on your statements and to answer Philip...
In most instances, localization usually happens at the business logic stage, which is either in the client or the app server tier (without going too far out on a limb -- we all know there are complete books on this). This makes it easy(er) to localize software for a specific market area by keeping modifications segregated.
Simply, if using a fat client, that is where I would do the formatting, otherwise I would do it in the app server (COM+ or whatever else) layer. I very seldom put that type of info in the back-end.
Anyway, that is how I handle it.
Joe Johnson
NETDIO,LLC.
Joe Johnson
NETDIO,LLC.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply