Hacker News Reader

Comment by kls | Hacker News Reader

Some numeric data types will trim off leading 0's so if you have a number like 00543 when stored in the DB it will be retrieved as 543. Things like social security numbers and phones numbers are unique identifiers that just so happen to be numeric they should be treated as such with regards to data considerations.

View on HN

Phone numbers are not unique identifiers. In several regions around the world, phone numbers are reused. Please keep this in mind because it is very short sighted to say you will never expand globally.

I agree, neither phone numbers nor social insurance numbers are unique.

But the issue is with storing them in numeric datatypes. They aren't guaranteed to be unique forever (and indeed, are alphanumeric in many jurisdictions) and the leading zero problem is a killer when you go to reconstitute them, both for performance and for logic reasons. Same goes for US ZipCodes.

This is why I have to have a very strong technical reason to make a non-math column numeric, especially with externally set data (like SSNs, SINs, Account codes, etc.) The people who set them could just start adding letters or symbols...and this isn't rare.

Indeed, Medicare numbers are usually the SSN with a letter appended

Sure perhaps I should not have used the term "unique" but they are identifiers, they identify how to contact a certain individual based on a numeric sequence. Same with SS numbers they identify a living person. I would never use either as a unique key in my database but I am not a natural key person for the most part. I tend to like a uniquely generated key to serve as a surrogate for my unique primary identifier.

Reply on news.ycombinator.com