Database configuration

[Linda Thomas]

We are having a debate regarding how a phone number should be saved in the database. The program we are testing is saving the phone number with dashes (i.e. 123-456-7890). I argue that we should not be putting dashes into the database for a phone number.

What would the standard be for most professional databases?

[Bear Bibeault]

Moving to JDBC.

[Jeanne Boyarsky]

Linda,
I agree with you. Storing the formatting in the database limits your flexibility in the future. It is easy for a Java application to add in the formatting after you obtain the data. This is especially important if you ever have to support other formats or international phone numbers.

What reasons are the others giving for storing the dashes?

[Adeel Ansari]

I think its better to store the phone numbers with dashes. And afterwards if your system is going to call on those numbers then you can just remove those dashes in your code.

Formatting phone numbers in order to seperate the country, city code, and the actual phone no. would be rather difficult, offcourse not impossible. Different lengths of phone number, country code, city code does matter.

what you people say??

[Jeanne Boyarsky]

Obviously, there is no one right answer to this.

Adeel,
Why would removing the dashes afterward be any harder than adding them in? For there to be dashes in the first place, the phone number would have to be a certain number of digits.

[Adeel Ansari]

Originally posted by Jeanne Boyarsky:
Why would removing the dashes afterward be any harder than adding them in? For there to be dashes in the first place, the phone number would have to be a certain number of digits.

Jeanne,

What are you saying? adding the dashes afterwards or removing the dashes afterwards.

I am thinking of removing the dashes afterwards. As I said earlier.

Not sure about the country code, but different cities may have different length of phone numbers and city codes.
[ December 28, 2004: Message edited by: Adeel Ansari ]

[satish kumar]

My preference would be to store phone numbers without dashes. In future if you have some other software utility (faxing or auto dialing application) reading the data directly, it is easier because you cannot be sure if those utilities ignore the dashes.

[Adeel Ansari]

Originally posted by satish kumar:
My preference would be to store phone numbers without dashes. In future if you have some other software utility (faxing or auto dialing application) reading the data directly, it is easier because you cannot be sure if those utilities ignore the dashes.

Satish,
I have considered and addressed this in one of my previous post. Just format the number first then pass it on to the utility.

And I think my point is also valid till now. Anyways, as Jeanne said, "there is no one right answer to this".

cheers.

[Jeanne Boyarsky]

Adeel raises a good point. With my system, I am both removing the dashes that the user typed in and then adding them back when getting the data from the database. I still prefer storing without the dashes because it decouples the data from the presentation.

Linda,
Does this help? We seem just has conflicted as your original debate

[Shailesh Chandra]

I agree with adeel some what, but I am still in favor of putting number in database without dashes.

If the system is dealing with telephone numbers of various country which may change in future, we have possibily to format them once we pick number from database.

for example if database is having number 123456789
we can format the same
for one country like 1-234-56789 and for other country 1-2345-6789

taking case that number were stored with dashes and if sudden number formatting system changes (eg 1-234-567 to 1-23-4567) then one may need to update all number exist in database.

but in first case (without dashes) only formatting logic will be changed.Even formatting ligic can be extended as per country basis

thanks...
Shailesh
[ January 20, 2005: Message edited by: Shailesh Chandra ]

[Linda Thomas]

Lol, well I can see that it does differ on opinion. I think the number should be left in there as digits without dashes. I wonder if uploading it directly into another database if we were to move from sql to say oracle would cause any issues.

We were taught in school that only numbers that are meant to be used in calculations should be saved as numbers and rest as varchars. But our other programs save the phone number without dashes. I feel that all programmers should follow the same practice especially since there is talk of moving to different language in the next year.

[Paul Sturrock]

What you do depends on how you use the data. I'd argue that unless you use the phone numbers for anything other than display then you should use a varchar datatype and store the numbers with their formatting. Remember that a phone number can viably include the "+" character - which you will not be able to persist in a numeric datatype. Including formatting will cause problems if you have to search for telephone numbers though.

[Shailesh Chandra]

even I am in favor of defining phone number as varchar, because defining as number will limit the flexibilty of putting dashes.

But same time I am very much in side of putting phone numbers without dashes


thanks

[Jeanne Boyarsky]

Linda,
Like Shailesh, I agree that the type should be varchar regardless of whether formatting is stored. Phone numbers are not numbers despite looking like them and having the word number in the name. If something can't take place in a mathematical operation, it is not a number. What is my phone number plus your phone number?

Switching database won't matter as long as you leave the schema alone. You are correct that consistency will make your migration easier. But don't implement a poor design (like saving in a numeric field) just because all the legacy apps do it. We can never change under that system.

[Adeel Ansari]

I am with you all. But please save the number with dashes.

Saving the nos. as numeric might be beneficial, if you will get my phone no just by adding 87 into Shailesh no., and Jeannes no just by dividing Pauls no. by 2.

cheers.

[Tualha Khan]

Saving the nos. as numeric might be beneficial, if you will get my phone no just by adding 87 into Shailesh no., and Jeannes no just by dividing Pauls no. by 2.

which is very very rare i believe.

regards,
Tualha Khan

[Jeanne Boyarsky]

Adeel,
Jokes aside, there is one legitimate use for using a numeric field: If you have an auto-dialer that either increments or randomly selects numbers.

As this is unlikely to be the case, we favor varchar.

[Adeel Ansari]

Originally posted by Jeanne Boyarsky:
Adeel,
Jokes aside, there is one legitimate use for using a numeric field: If you have an auto-dialer that either increments or randomly selects numbers.

As this is unlikely to be the case, we favor varchar.

Jeanne,
I agreed. And ofcourse it is unlikely.
thanks.