- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Storing customer IDs as number types is not just "not ideal", it is WRONG, TRWTF, and the source of all these issues.
I'd like to hate on Microsoft, but this is not their fault.
Admin
Oh, and "I like to prefix" is also just wrong. Either you always pad to a specific width, or you never prefix.
Admin
Sorry, I was a bit unspecific: Storing as floating point numbers (no matter the precision) is wrong. Proper integers are fine.
Admin
Consideration: If we can't do arithmetics with them, they are not numbers, no matter how we call them. Let's say we have customer 123 and customer 456, can we do something like "customer 123 + customer 456" to get customer 579? What if we substract 100 from customer 12345? Do we have 3 times customer 23456? What is 1/12th of customer 9876? Would that make any sense? No? Then: Those are strings (or maybe even a specific native or constructed datatype), but not numbers, no matter if we "implicitely" consider them integers, floats our doubles just because they could look like those. It can even start with plain words, thought about carefully, maybe: "customer ID" (an ID doesn't imply it's a number) instead of "customer number", and on the other hand, customer IDs like "003.0278854", "CT8879556", "187/9965/0002774" wouldn't be called "numbers" either. Sadly, we routinely say things like "phone number" and then use integers... Language enables thinking. And that should have been possible decades ago.
Admin
The only main reason you'd use numbers instead of strings is for storage size... if customer IDs are guaranteed to always be integers (e.g. they're auto-increment IDs), they use way less storage than a string, and things like indexes are much more performant. Obviously depends on the scale of data you're working with, how big the strings/numbers are, and how important performance is.
Admin
re: phone numbers Technically... you can int-divide and modulus telephone numbers to get area code and local part (which should be done with substrings)
Admin
For some countries. There is no standarized international phone number structure. For instance in the US a
1-771-*is a number tied specifically to the D.C. area but in Chile a56-2-*is a landline number while a56-9-*is a mobile number and there is no regional coding. In other places like the UK the area codes vary in length, for instance London is020and Oxford is01865, so good look using a modulus there unless you also coded the length rules. And some places are even weirder because the number of digits in a phone number varies, i.e. mobile numbers being either longer or shorter than landline numbers, although this rarer and generally only found in developing countries that have a mix of outdated and modern telecom infrastructure.Admin
So you're saying that an int allows for certain operations that don't make sense on a customer number, so customer number shouldn't be an int. By that logic it shouldn't be a string either, as it doesn't make sense to take a substring, or do a string replace, etc, on a customer number either.
The real answer is to get rid of primitive obsession in your applications, and then you'd have a customer number of type CustomerNumber, which only provides the operations that make sense, and ensures that you can never have an instance with an invalid value.
And once you have that, it doesn't matter what type it is internally, as you'd only use that type at the boundaries of your application, when you need to interop with simple, commonly used, types. Like say when storing or retrieving data from a database.
So having customer number be an int in your DB is absolutely fine, and arguably much better than a string.
Admin
I wouldn't say "developing countries", since it's developed countries that will have quirks due to mixing old with new. Sweden is arguably one of the most modern telecom infrastructures (think telco Ericsson), but it is also one of the oldest, and has variable-length telephone numbers. It seems that they started with shortish ones, like five-digit 2x xxx, but soon realized that would not be enough, and so if you start with a 3 it's 3xx xxx, and if you start with 5 it's 5xx xx xx. It also varies by region (my examples are from Stockholm), and the regional prefixes also vary in length.
https://en.wikipedia.org/wiki/Telephone_numbers_in_Sweden
Admin
And then you get dates, which actually are three numbers and usually encoded as... a string!
Rules are there to be broken.
Admin
That used to mean something, but the days of cheese-paring storage are gone. You can store 18-digit customer IDs with 64-bit integers in 8 bytes each. Or you can use strings, and have them take up 18 bytes each. If you have a billion customers, integers will save you 10 Gigs. Which costs more, 10 Gigs or the trouble you're borrowing by using integers?
Admin
I just had a flashback to the "Pentium Chip Can't Do Math" bug in 1994, but I should have been reminded of the Excel bug that sortakinda still exists.
Admin
And there'll be a time when your company aquires another company, and management decides that your old customers are now all "ABC1234545660" and the acquired company gets "JKL02101021".
Admin
What problems? Strings will almost certainly give you more problems than a 32 or 64 bit integer will give you.
With strings sorting will be broken, unless you think customer 11 comes before customer 2.
Also with strings, customer numbers 5 and 005 represent different values, which they almost certainly shouldn't. Yes, you can fix that by normalizing the values, but with ints you wouldn't have to bother in the first place.
Admin
Are you Posixtive about that?
Admin
I feel you're working off the implicit assumption that customer numbers are just that - numbering your customers; and if that's the case, then sure, ints all the way (although: why even have separate customer numbers then instead of just the DB id?) There's no guarantee a given system behaves like that, though, and from what I've seen, purely numeric identifiers are more the exception than the norm.
Admin
As well as all that, in the UK, phone numbers have a leading 0 for national numbers, as you wrote, and a leading 00 for international numbers, so storing them as integers wouldn't work.
Admin
Hardly seems like an assumption given that the article is about numerical values.
Admin
10 gigs, obviously. Why do you think it could be acceptable in any way to perpetrate such a waste of resources?
Admin
For a developed country like Sweeden having different-length phone numbers is a choice, meaning they have the tech to standarize the numbering but decided not to for whatever reason and if that reason is equipment then again is a matter of won't not can't because they don't lack the funds to get whatever they need. Developing countries usually don't have the means to modernize all their telecom at once so in large population centers you might find tech that matches and in some cases surpases the average of more developed countries but as you move away it goes from 100 to 0. From personal experience I know at least one place where you'd be lucky to pick up a 4G signal in a major city and the moment you leave the city limits the best you can hope for is 2G and a bit farther than that you better get used to talking to the locals because without a sat-phone you will not be talking to anyone else.
Admin
I am a bit confused about the confusion when it comes to the data types.
SQL has variable length data types, C# has binary length fixed data types. In both cases we are talking about floats (short for floating point number) but in C# you obviously need a way to specify the length and this is done by using either the 32bit Single type or the 64bit Double type (short for single/double precision floating point number).
So I am a bit confused how anyone can actually mess up something so basic beside a token generator that doesnt know sematics only token relationships.
Admin
The article is actually explicit on that many of the "numbers" start with zeroes. So in this case, 005 and 5 would indeed be different, though it sounds like the latter would not even be allowed under their schema, so they just store 5 with the understanding that it's actually 005 and needs to be converted to such as appropriate.
Admin
10 gigs in one column, 20 gigs in that.
30 gigs in this table, wasted just like that.
50 wasted over here, 60 over there.
Hosting prices going up!
Layoffs everywhere.
Admin
IMHO, it's a real WTF when DB engine designers uses terms like "float" knowing full well what that means in a plethora of common programming languages and yet use it in a very different way. It would have been far too simple to name the variable precision type something like "real" or "decimal" and have "float" and "double" correspond to more common sizes.
Admin
Not sure how the discussion became mostly about string vs number, but TRWTF is using a double as an index value, then casting it to float. For example, 16,777,217 stored as a 64-bit double is fine. But cast that to a 32-bit float and you'll get 16,777,216 because 16,777,217 can't be stored as an exact value in a float and gets rounded. With customer numbers up to 10 digits there's going to be a lot of these cases, probably resulting in customers magically merging together or losing the data altogether. Ouch. I bet that was fun.
Admin
Those are identities. Why do you want to apply natural sorting rules to identities?
Admin
I'm gonna go on a limb here. Let's assume said customer weighs 90 kg (roughly 200 lb).
Ninety divided by 12 equals 7.5 kg , so... I'm guessing it could be one of the customer's legs?