IBazz: thanks for the example, I agree future-proofing is important though it can be a fine line to tread.
me, i prefer to use the most appropriate datatype for each element
unless you are seriously going to find AVG(phonenumber) or SUM(phonenumber) then it is ~not~ numeric data, and should be stored as VARCHAR
my advice is to store two values, formatted for display and unformatted for search -- but they're both VARCHARs
i would store two VARCHARs: 9375551212 for searching, and (937) 555-1212 for display
then when you run a search, usethis strips out the formatting characters from the user-entered value in the form field, and allows the index on the search_number to be usedCode:SELECT display_number FROM phonenumbers WHERE search_number = REPLACE(REPLACE(REPLACE(REPLACE('$form_number','(',''),')',''),'-',''),' ','')
of course, you could also strip the extraneous characters in your application language prior to running the search (e.g. regular expression which ditches everything except digits)
so if the user enters 937-555-1212 or (937)5551212 or other formats, they will all be found -- efficiently
MySQL :: Re: Primary key performance int vs. char
List of TCP and UDP port numbers - Wikipedia, the free encyclopedia
What datatype would you use for the port numbers?
1. You are using two fields when you could just use one.
2. On insert you need to ensure the numbers of both fields match.
3. On updates you need to change the number in two places and also ensure they match.
With one field (formatted by the application on display) I can avoid the above problems, plus:
1. If I decide I want to change the display format from (937) 555-1212 to 937-555-1212 I can do that without having to update all the records in the database.
2. I have the ability to allow the user to decide which format the number is displayed to them.
I don't know what r937 will say but to me they qualify to be int because port numbers are single numeric entities, they are not written as formatted in any way, and - as the table shows - they have certain meaningful ranges, which means that it makes sense to make numeric comparisons and searches using < and >, while it doesn't make sense to search port numbers using LIKE '%...%'. With the phone numbers it's the opposite: numerical searches with < and > make no sense while textual searches with LIKE '%...%' make sense, that's why it's more logical to use a text field for phone numbers. And, if you want to find all phone numbers from a certain area (by first few digits) then your argument about speed is lost completely because no index is used on an INT column with LIKE '...%'.What datatype would you use for the port numbers?
instead ofCode:WHERE numeric_field LIKE '1__'Now consider the fact that not all numbers are 10-digit long and your where clause becomes even weirder. I'm not saying it won't work. It's just a clumsy workaround.Code:WHERE numeric_field BETWEEN 100 AND 199