aspose file tools*
The moose likes JDBC and the fly likes Database configuration Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Database configuration" Watch "Database configuration" New topic
Author

Database configuration

Linda Thomas
Greenhorn

Joined: Aug 12, 2004
Posts: 8
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
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61761
    
  67

Moving to JDBC.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

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?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

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
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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
Greenhorn

Joined: Nov 26, 2004
Posts: 18
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
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

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
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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 ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Linda Thomas
Ranch Hand

Joined: Jun 21, 2004
Posts: 36
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

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
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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
Ranch Hand

Joined: Nov 22, 2000
Posts: 287
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


SCJP2, BEA WLS 6.0, DB2 UDB 7.1
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

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
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Database configuration