Spinning this off the earlier Part I (on number types) because it's an equally important topic.
Not necessarily a rivetingly interesting topic, but since some of you are trying to move into more technical roles, these are good things to know to geek up your conversation. Knowing these things before I became a full-time developer, having learned them on the SQL database side, made coding more comfortable for me.
So: very bad things happen when number field types are used to store values that aren't truly numbers, but actually numeric strings.
What's a numeric string?
Numeric strings are sets of digit characters (0-9) that do not actually represent a single number, or perhaps we could say are not actually equal to their apparent number value.
Those seemed vague, I know. Examples are better teachers…
Credit card numbers are numeric strings
Credit card “numbers” (formally, payment card numbers) are numeric strings. They are a set of independent codes mashed together (MII digit + network number + your number + checksum digit), like:
But they do not represent a 16-digit integer: this is not the value 4,110,144,110,144,115 (4 quintillion something) and should never be thought of that way.
Yet some people still ***** up and store CCs as numbers, thinking it'll be more space efficient. It certainly would be — if it worked — since a char(16) takes 16 bytes while a 64-bit int takes only 8 bytes. The ability to reduce your storage requirements by 50% is really tempting. But it's wrong in more ways than one.
The most basic way it's wrong is if you don't actually have a big enough datatype everywhere this value will be used. In the previous post on number types, you saw that the maximum safe value of an SFDC Double is 9,007,199,254,740,991. OK, so 9 quintillion. The above credit card (mis)represented 4 quintillion will fit into that. But what about this card number:
Nope! That's outside the Double range. And that's not surprising, as card numbers are not in any way calibrated to fit in a certain data type.
Note I chose a 16-digit credit card number above. In fact, a Visa number, among others, can be 19 digits. So that blows a Double out of the water completely and might make you contemplate a 64-bit Integer (as long as you have end-to-end support for that type). Sorry, won't work either:
That value, if you attempt to store it as a gigantic integer, won't even fit in a 64-bit int, which has a max value of 9,223,372,036,854,775,807.
James Bond's agent number is an alphanumeric string
While daydreaming about, er, “real-world” examples, 007 came to mind.