This older NYC MUG blog post has been copied to the central Products blog to be referenced in an upcoming post.
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 this stuff before I became a professional 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:
4110144110144115
│└───┘└───────┘│
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 screw 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:
9792004110144115
│└───┘└───────┘│
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:
9792004110144111248
│└───┘└──────────┘│
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.
In Fleming's world, that's the 00 section plus a 1- or 2-digit ID. When you have numeric values from different sources abutting each other, they don't make a number but rather a numeric string. Otherwise, if stored as an integer, we might confuse 007 with just 7, who could be a lowly JavaScript developer in the basement of Vauxhall.
In the more down-to-earth world of flour, 00 or doppio zero is the finest (literally, the finest grind) while 0 is coarser and fits different recipes. There's even the rare 000, I guess if you want to pretend to be Tony Montana while making gnocchi. You definitely don't want to treat these as their (identical) numeric values!
When you talk “digits,” you've probably got a numeric string
As a rule of thumb, digit length is not something techies usually talk about with true numbers. (Pop culture references to 7-figure incomes are another story, but don't get the two worlds confused!)
So if someone tells me a field must hold “a 10-digit number” I'm already assuming they mean numeric string, not number. From a layperson, “digit” likely means “character,” and characters make a string.
(There are exceptions, certainly, with regard to significant digits, but when someone says “digits” you should at least get the longer story about the field.)
Talk it over first
Of course, if new field creation were always pre-screened by a person with database experience, initial errors in communication wouldn't become production errors. But in the SaaS-to-SaaS world, a data specialist is usually out of the loop. (SFDC admins have a distinct set of skills, but datatype awareness isn't among them in my experience — an SFDC developer may know the difference, but by the time they're involved it's too late. Ditto on the Marketo side.)
So these errors keep happening: I dealt with a product catalog the other week that had 13-character ISBN “numbers” exported as a mixture of JSON String and Number types. If the ISBN had a letter in it, it was exported as a String (which they all should have been); if it had no letters, it was a Number. Ugh. That meant custom code to deal with something that should never have made it through testing.
.png)