|
| Featured Contents |
| Question of the Day |
|
| The Voice of the DBA |
The Number that shouldn't be a number This editorial was originally published on Feb 2, 2015. It is being republished as Steve is on holiday. Sometimes I get really confused by the data types people choose for their tables. Take this example from one of the popular SQL forums:
Why, why, why did they choose an integer for the telephone number? Sure, it's probably ultimately numeric, but does that really mean it should be an integer? Is it likely that the phone number needs to be added to something? How often do need to find the square root of a phone number? Or invert one? Is one ever likely to need to do something like this:
If so, I'd certainly love to see the reason. Telephone numbers, despite being composed of numeric digits, are not numbers. They are strings. They are not likely to be manipulated mathematically, and leading zeros are meaningful. I've seen similar strange choices with postal codes, which in South Africa are four digits long, and where leading zeros are, again, meaningful. Cape Town's postal code is 0001, not 1. Storing a postcode in a My general rule is that if the column isn't going to be mathematically manipulated, it's probably a string, not a number. I remember the pain of working with a system where a developer had chosen the I've also seen Here is my call to arms: if you're designing a table, think about the domains for each of the attributes, think about what the maximum and minimum values can be, and then think very carefully about the best data type for that attribute. And don't get fancy. For financial data, while it might be possible to convert the value into hex and store it in a Binary column, storing it in a Numeric is probably easier. Finally, what are the weirdest data type choices you've seen? Gail Shaw (Guest Editor). Gail Shaw Join the debate, and respond to today's editorial on the forums |
|
| Featured Contents |
