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?
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?
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.
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.
Joined: Aug 15, 2004
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.
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 ]
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.
Joined: Aug 15, 2004
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".
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
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.
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.
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.
Joined: Aug 15, 2004
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.