Some "general rules" about database design are really just personal preferences, often adopted without really thinking about it then promulgated as "convention". This thread is about one such rule.
Integers are considerably more efficient to store and index than text. I can see absolutely no reason to store a number as text simply because it isn't involved in calculations.
Where I work we have Member Number. No calculations done but I always use Long Integer. It is up to something around 150,000 after 45 years in business and Long Integer should see it through for another century or two even if we expand beyond anyone's wildest dream.
In Australia we have a four digit numeric postcode. It never has a leading zero and I store it an an integer. Why not?
Australian financial institutions have a BSB (Bank State Branch) which uniquely identifies it. It is consistently a six digit number but may have a leading zero. I store it as a Long Integer and use a Format string to display it with the optional leading zero and customary dash in the middle.
When it come to phone numbers the debate will really hot up.
Australian phone numbers can be split into the eight digit number (can be held as Long), area code and the country code. (In Australia the area code is a single digit dialled with a preceding zero to access an interstate or mobile call). Then another field can be provided for the extension. This is best as text because zero may be a significant digit in some extensions.
I work near a state boundary and I see it all the time. Some numbers entered with the area code and others without. It is inevitable where one control is provided to enter the phone number. Those who use a single field for phone have not completed a thorough data analysis. It is like using one field for Name.
The four separate controls clearly designate the entry requirement. I have only worked on databases in Australia but I have looked around and am yet come across a country where this kind of system wouldn't work.
I would be curious if anyone is aware of systems where this would fail such a local number which can have a leading zero and a variable number of digits.
Those who store phone numbers as text need to deal with the random insertion of spaces, dashes etc by various users. It gets really out of hand when they start storing extension numbers in the string too. "Ext-", "Ext:", "E", Everyone does it differently and it renders any attempt to automatically dial completely useless.
All non-numeric characters entered in an sub-number can be stripped in a numeric system whatever the user chooses to type. Storing as a number allows for a simple system display format to be implemented.
All comment and disagreement most welcome but those who have encountered me in debate before will know they had better have a well considered case.
