Hacker News Reader

Comment by Kon-Peki | Hacker News Reader

> how about a personal identification number? In Denmark we have the CPR number, and I understand that the US Social Security Number is vaguely analogous.

The US SSN is not guaranteed to be unique, the SSN assigned to a person could change, there is no guarantee that a person with an SSN assigned to them is a US citizen, and there is no guarantee that a US citizen has an SSN - they must be requested, and you don’t need one unless you do something that requires having one. There are also things called ITINs and ATINs that look like an SSN but are not, yet can be used in place of an SSN in a huge range of SSN-required situations!

(Please don’t use the SSN as a database key!)

View on HN

In Spain we have the DNI number, that a lot of people asume is unique, even database designers that use is as a natural key.

Turns out the DNI can have, and actually have, a lot of duplicates. The police has a page explaining it (https://citapreviadnipasaporte.es/dni/dni-duplicados-espana/), and how it's not a primary key in their databases, but a number entered manually from a pool of possible numbers. And number re-using is a possibility. They estimate the number of duplicates in 200,000 for a population of 50,000,000.

The point is that if you asume DNIs are unique and use them as PK your database is exposed to the bad design of the DNI database. There are some stores that use the DNI as the "unique" identifier for fidelity cards.

I remember having to deal with this and other identifiers like NIF and NIE while working on the academic titles homologation platform for the MCIN. I didn't understand why it wasn't used as, not necessarily PK, but an identifier logging in. Thanks for letting me know that the hellish time spent integrating Cl@ve wasn't in vain.

Also your DNI can change. Typically foreigners get a NIE (used for the same thing but a different format), and get a new DNI if they ever get Spanish nationality.

I've seen banks or insurers use DNI as user login.

The user login (hopefully) is not the internal primary key for the user. It should be unique at a given point in time, obviously, but certainly there are reasons it might need to change.

And I've been bitten by this design numerous times. The most common bug: literally the birthday paradox, i.e. "these two people have been assigned the same number, and now we need to distinguish them in our database".

All of them do I think. But from experience, they can change it for your account.

We have the same problem in Denmark, most people just don't realize it. At my dayjob we get at least one person every year who changes gender and consequently gets a new SSN (the final digit is supposed to signify gender). Most people don't store SSNs so they never realize, but it does happen fairly frequently.

If Denmark is anything like Sweden there's also:

- SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN.

- SSN:s have a long and a short form; the short form which cuts off century information can be the same for someone who is 5 years old and someone who is 105 years old.

- When an unconscious patient comes in to the E.R. you don't know their SSN, so a temporary one is assigned for use in patient records. Such temporary SSN:s are not coordinated nation-wide so multiple patients may have the same SSN. In some hospitals they don't even have a local standard for ID:s. The staff just makes something up on the spot. It happens that the SSN they made up collides with a valid SSN for another person.

Immigrants and refugees will get a "replacement CPR number", which is outside the normal space/range of CPR numbers. Once registered as living in Denmark, they'll get a real CPR number.

Danish SSNs doesn't have a long or short form, you used the seventh digit to do a table lookup to see if the person is born in 18XY, 19XY or 20XY. The date of birth is always ddmmyy, there is no long form. So if the seventh digit is 9, and the 5-6 digit is between 00 and 36, then you're born in between 2000 and 2036, if the 5-6 digits are 37 to 99, then your born in the 1900. But you need the published table to figure that out.

Last point, there is backup system for unconscious patients, but it should be the same across all medical records as these are somewhat standardized.

> - SSN:s have a long and a short form; the short form which cuts off century information can be the same for someone who is 5 years old and someone who is 105 years old.

We don't do that. instead, we shove that extra bit of information into the digit following the last two digits through a table: https://da.wikipedia.org/wiki/CPR-nummer#Under_eller_over_10...

Also: the personnummer carries a date that often means birth date, but there are cases where it’s not, but I’ve seen a few system that just assumes it’s the same.

> SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN

Having gone through that, my personummer didn’t change. Maybe that doesn’t happen anymore?

It does, but if you already have a Personnummer or get a residence permit right away so that you are eligible for one you don't get the temporary Samordningsnummer.

And the samordningsnummer isn't only for immigrants - it's also for Swedes born abroad who have never been folkbokförd.

They're still still an immigrant. The numbers are for residents (at some point in time) and citizenship isn't reflected by it.

Interesting, I've never heard of anyone referring to a citizen as an immigrant, but looking up the legal definition, they certainly are. TIL.

In Ireland, until the 90s, if a woman got married, she gave up her PPS number (essentially a social security number) and took her husband's, with a 'W' appended (this fact tells you a lot about pre-90s Ireland...) If she subsequently divorced or the husband died, she got a _new_ PPS number.

While this was abolished in the 90s, _some people still have these 'W' numbers_, ticking timebombs for anyone relying on them as a key.

Pernicious assumptions!

Google Cloud projects have three attributes: user-friendly names, system numbers, and system names. System names are alphanumeric. They can be chosen by the user, derived from the friendly name if there's no collision.

But! There's some system names from the olden days that are actually all numbers - so not actually alpha-and-numeric. Thankfully we don't run into those often.

Yeah, these things are almost never as simple as they are supposed to be.

Poland has PESEL numbers since 70s. It was supposed to be unique, only apply to Polish citizens, never change, and have a checksum digit. Every Polish citizen gets one at 18 when they get their national ID document, and you can request it earlier if you want to.

Turns out there are duplicated PESEL numbers. A LOT of non-Polish citizens have them assigned (mostly Ukrainian refuges but not only). The checksums are sometimes wrong. And some people have several PESEL numbers.

If you used PESEL as database key you're fucked.

The system works perfectly, but it interfaces with external world through computer-human-paper-human-computer interface. And at some point the mistake propagates so far that it becomes the truth assumptions be damned.

If I used this as db key, I think our security/risk team might actually have me assassinated.

How to uniquely identify an American citizen?

You cannot.

I used to work for one state's Department of Motor Vehicles.

Part of why REAL ID is such a charley foxtrot is because there exists no such identifier and since every state counts approximately as a sort-of-almost sovereign nation (this is basically what the 10th Amendment guarantees), there is and can be no possible nationwide citizen identifier.

There are about 3500 different counties in the US. All of which may or may not issue their own birth certificates. States are supposed to do that now.

To give a slightly more technical answer, at a large insurance company I used to work for, the legal department had provided definitions on what conditions we would consider various components of PII a match. So between SSN, DoB, First Name, Last Name, and a couple others, there were potential combinations that our system would say, "yes this is the same person". Note that we didn't necessarily need exact matches on things like names, but "close enough" matches were sometimes sufficient.

You can't.

That's why everybody use SSN.

The population health database I work with uses generated IDs for people and relies on health systems to link records before submission (they put a lot of effort into data used for billing). But we do check for problems by looking at duplicates of SSN with date of birth. We don't consider names, because they could be transcribed differently. Even though the SSN+DOB pair should be unique, there have been cases where a widow provides her deceased husband's SSN. Likely because she used it for Social Security benefits and forgot her own long ago.

You can get kind-of close with driver's license numbers. They will change when US citizens move among states, (and we do that quite frequently.)

(People who don't drive have to get state-issued IDs that have the same number. At least, they have to if they want to buy do anything that requires proof of identification.)

How about not doing that?

Then I guess it could lead to duplicate records. Now, whether this is a problem or not depends on the business and how much one cares about data integrity. I work with higher education, so uniquely identifying students and keeping all their records organized is somewhat important. Granted, I don't work in the US, and here we have a unique, national number. So this is covered, except for foreign students. I was curious how this problem is solved in other countries.

The US government doesn't assume that it can uniquely identify a citizen with 100% certainty. When things get tricky, we rely on the judicial system to weigh the evidence and make a decision. Which could later get changed.

If you want to design your system to assume that you can do it, that's your problem. Literally, it is now your problem, and nobody is going to step in to help.

ha ha only serious; ask them for their papers and if they say “who the fuck are you?”, they’re americans!

a slightly more serious answer: you largely don’t. The US doesn’t have a national ID, proof of birth is not even close to standardized, etc.

> The US SSN is not guaranteed to be unique

The cases you listed do not mean SSNs are not unique, unless there are people who share the same SSN. You can still define a unique index for the SSN column. A column can be both nullable and unique as each null is different in SQL.

> It’s not as uncommon as you might think. In fact, some 40 million SSNs are associated with multiple people, according to a 2010 study by ID Analytics.

https://www.pcworld.com/article/424392/a-tale-of-two-women-s...

Funny enough, I used to work at IDA.

I’ll add that there is a huge difference between the SSN database that the Social Security Administration maintains, and the list of SSNs that have been associated with a person. Especially because it is very common to change a single digit of your SSN when performing credit fraud - because they’ve already burned their real one. Some people will have dozens of SSNs attached to them.

IDA was very good at determining who a person is through the graphs that represent identities in our world (names, DOBs, phone numbers, addresses, SSNs, etc.)

EDIT - SSA claims that numbers are not recycled. But there are known cases where the same number has been assigned to multiple people.

Note that in less than 100 years, more than half of all possible SSNs have already been used…

So we need SSNv6? ;)

> unless there are people who share the same SSN

That happens all the time. First of all people steal SSN's and use them (and you are not the police, so it's not your responsibility to do anything about that). Second people make up fake SSN's because they don't want to give you their SSN.

People also make typo's, and you can end up with the same SSN.

An SSN is not unique in the real world.

People also type them in wrong, so there's another thing: where is the data coming from.

> The most misused SSN of all time was (078-05-1120). In 1938, wallet manufacturer the E. H. Ferree company in Lockport, New York decided to promote its product by showing how a Social Security card would fit into its wallets. A sample card, used for display purposes, was inserted in each wallet. Company Vice President and Treasurer Douglas Patterson thought it would be a clever idea to use the actual SSN of his secretary, Mrs. Hilda Schrader Whitcher.

> The wallet was sold by Woolworth stores and other department stores all over the country. Even though the card was only half the size of a real card, was printed all in red, and had the word "specimen" written across the face, many purchasers of the wallet adopted the SSN as their own. In the peak year of 1943, 5,755 people were using Hilda's number. SSA acted to eliminate the problem by voiding the number and publicizing that it was incorrect to use it. (Mrs. Whitcher was given a new number.) However, the number continued to be used for many years. In all, over 40,000 people reported this as their SSN. As late as 1977, 12 people were found to still be using the SSN "issued by Woolworth."

https://www.ssa.gov/history/ssn/misused.html

As late as the 1970s, the first 3 digits of your SSN told what office issued your number to you. And the next 2 digits told workers at that office what filing cabinet held your application.

https://www.ssa.gov/policy/docs/ssb/v45n11/v45n11p29.pdf

Reply on news.ycombinator.com