6 ms·

> 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

> 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!)

selcuka 2y ago
> 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.
- ars 2y ago
  > 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.
- Kon-Peki 2y ago
  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…
  - bloopernova 2y ago
    So we need SSNv6? ;)
- dqv 2y ago
  > 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-same-birthday-same-social-security-number-same-big-data-mess.html https://www.pcworld.com/article/424392/a-tale-of-two-women-s...
  - pseudosavant 2y ago
    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.)
- cmiles74 2y ago
  People also type them in wrong, so there's another thing: where is the data coming from.
  - Tangurena2 2y ago
    > 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 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 https://www.ssa.gov/policy/docs/ssb/v45n11/v45n11p29.pdf
otherme123 2y ago
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/ 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.
- prmoustache 2y ago
  I've seen banks or insurers use DNI as user login.
  - benhurmarcel 2y ago
    All of them do I think. But from experience, they can change it for your account.
  - SoftTalker 2y ago
    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.
  - Piskvorrr 2y ago
    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".
- benhurmarcel 2y ago
  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.
delusional 2y ago
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.
- winternewt 2y ago
  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.
  - kassner 2y ago
    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?
    - tapland 2y ago
      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.
      - kalleboo 2y ago
        And the samordningsnummer isn't only for immigrants - it's also for Swedes born abroad who have never been folkbokförd.
mikedelfino 2y ago
How to uniquely identify an American citizen?
- hprotagonist 2y ago
  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.
- Longhanks 2y ago
  How about not doing that?
  - mikedelfino 2y ago
    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.
    - Kon-Peki 2y ago
      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.
- j16sdiz 2y ago
  You can't. That's why everybody use SSN.
- vharuck 2y ago
pradn 2y ago
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.
ajuc 2y ago
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.
threecheese 2y ago
If I used this as db key, I think our security/risk team might actually have me assassinated.

selcuka 2y ago

> 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.

ars 2y ago
> 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.
Kon-Peki 2y ago
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…
- bloopernova 2y ago
  So we need SSNv6? ;)
dqv 2y ago
> 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-same-birthday-same-social-security-number-same-big-data-mess.html https://www.pcworld.com/article/424392/a-tale-of-two-women-s...
- pseudosavant 2y ago
  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.)
cmiles74 2y ago
People also type them in wrong, so there's another thing: where is the data coming from.
- Tangurena2 2y ago
  > 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 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 https://www.ssa.gov/policy/docs/ssb/v45n11/v45n11p29.pdf

otherme123 2y ago

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/ 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.

prmoustache 2y ago
I've seen banks or insurers use DNI as user login.
- benhurmarcel 2y ago
  All of them do I think. But from experience, they can change it for your account.
- SoftTalker 2y ago
  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.
- Piskvorrr 2y ago
  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".
benhurmarcel 2y ago
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.

delusional 2y ago

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.

winternewt 2y ago
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.
- kassner 2y ago
  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?
  - tapland 2y ago
    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.
    - kalleboo 2y ago
      And the samordningsnummer isn't only for immigrants - it's also for Swedes born abroad who have never been folkbokförd.

mikedelfino 2y ago

How to uniquely identify an American citizen?

hprotagonist 2y ago
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.
Longhanks 2y ago
How about not doing that?
- mikedelfino 2y ago
  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.
  - Kon-Peki 2y ago
    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.
j16sdiz 2y ago
You can't. That's why everybody use SSN.
vharuck 2y ago

pradn 2y ago

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.

ajuc 2y ago

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.

threecheese 2y ago

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