5 ms·

In databases, never rely on data you don't control. "Natural" keys are an example of this. Names can be natural keys, but you don't control them. You don't con

In databases, never rely on data you don't control. "Natural" keys are an example of this. Names can be natural keys, but you don't control them. You don't control when or how a name changes, or even what makes a valid name. Addresses change. Or disappear. Or somehow can't be ingested by your system suddenly. Official registration numbers (SSNs, license plate numbers, business numbers etc) seem attractive, but once again you don't control them. So if the license plate numbering scheme changes in some way that breaks your system, too bad. Or people without an SSN. Or people in transition because an SSN needs to be changed in a government system somewhere. Or any other number of things that happen in a government office that affect you, yet you have no control over. Phone numbers? Well, we've already seen that mess with many messenger platforms. Fingerprints? Guess what? They evolve over time, and your system will eventually break. Retrofitting a system that relies on "natural" keys that have broken SUCKS. Use a generated unique key system that YOU ALONE control. The first rule of software design is: Don't try to be clever. You're not clever enough to see all of the edge cases that will eventually bite you.

wvh 2y ago
I agree with your general point, but it might still be cheaper to redesign your system if a rare breaking change happens to usually reliably stable external registration systems, rather than to pay the cost of a thousand paper cuts by indirection, computational cost or human confusion. One edge case and good indicator is if your system is testable by itself without third party cooperation and the ability to, for instance, create license plate numbers.
- kstenerud 2y ago
  Every system that uses SSN as the primary key is fully testable by itself without third party cooperation. And yet every one of these systems has required workarounds because what the designers thought to be invariants, weren't. And this is ALWAYS the case with identifiers that you don't control. You don't make policy decisions about them, but SOMEBODY does. And that somebody isn't aware of - and wouldn't even care - about the invariants that you ASSUMED they followed (and maybe they really did follow them, but they sure don't anymore! Oops...) Fixing broken invariants after the fact is always a nightmare, because it only comes up once you get stuck - either you can't enter something into the database that you absolutely MUST by the weekend, or you can't change something in the database that you absolutely MUST by the weekend. So you do some last minute hacks to get things kind of working, and then it works for awhile until the next problem (usually involving your hack). It's hardly any extra work or complexity to just use an ID generator for the primary key. You'll still have the same indices, the same foreign key linkages etc. You have no reason not to do this. And yet somehow people always seem to fall for the "Oh cool! This existing ID does everything we want! Let's just use that instead of adding one more field to the table! I'm so clever!"
jimbobthrowawy 2y ago
Licence plate numbers are an interesting one, since what those mean varies from country to country. Here (Ireland), they are assigned to the car itself via VIN, are never meant to change once assigned, and are backdated based on information about the vehicle itself (e.g. year of first registration even if first registered in another country, following an older format if applicable), but in other countries they can be reassigned to other vehicles. e.g. 06-LK-12345 might be your car's plate if you move your foreign 2006 car here and register it while living in Limerick, but buying a new car might give you a plate like 242-L-12345 since the format of the first two fields changed since. If you leave and later return with that car, re-registering it gives you the exact same number. https://en.wikipedia.org/wiki/Vehicle_registration_plates_of_the_Republic_of_Ireland https://en.wikipedia.org/wiki/Vehicle_registration_plates_of...
- akira2501 2y ago
  You don't have vanity plates in Ireland?
  - ThePowerOfFuet 2y ago
    Many, many countries do not.
    - cheema33 2y ago
      Vanity plates are essentially free money for the govt. I am surprised every govt. body doesn't go for it. If you really don't like them, make them super expensive.
      - rsynnott 2y ago
        Tbh when you factor in the administration, I'd question whether they actually make any money on these.
  - Macha 2y ago
    There aren't any, the closest is that some authorities reserve low sequence number plates for e.g. the local mayor
andrelaszlo 2y ago
Official registration numbers, such as Swedish personal identification number, or "personnummer" (date of birth + serial + checksum [Luhn], where even serials are used for females and odd for males): - It can take a few days before a newborn is assigned a number - Non-citizens don't have one, but they can get a coordination number on the same format but with the date part incremented by 60 days. - Citizens can have both a coordination number and a personal identification number in certain cases. - They can be changed if the wrong birth date or gender registered at birth or during immigration, for protected identities, or for gender transitions.
- Aaargh20318 2y ago
  In the Netherlands we have something similar: the BSN (translated: citizen service number). This used to be the social security number, but because it was used by an increasing number or government agencies unrelated to social security it was changed to BSN. One of the major problems with this number is that it has a special status under the law. There are very strict rules as to who can process and/or store this number and for which purpose. For example: a bank can process this when opening a bank account, under anti money laundering regulations, but they cannot use it to identify an existing customer. If you originally set up your database to use SSNs you now have a problem. This actually happened with our chamber of commerce: if you registered a one-man business they used the SSN as the business id and you’re required as a business to publish this. Now it’s suddenly a number that is subject to strict privacy rules and they have to renumber all one-man businesses. So that’s another problem with data you don’t control: the legal status of this data can change.
- wasmitnetzen 2y ago
  Nitpick: long-term residents get a personnummer as well, not just citizens.
nailer 2y ago
I was this recently. I moved to US in November and it was February before I had an SSN. A bunch of companies had to put in fake SSNs into their system, which they have a standard for.
deleted 2y ago
[deleted]

wvh 2y ago

I agree with your general point, but it might still be cheaper to redesign your system if a rare breaking change happens to usually reliably stable external registration systems, rather than to pay the cost of a thousand paper cuts by indirection, computational cost or human confusion. One edge case and good indicator is if your system is testable by itself without third party cooperation and the ability to, for instance, create license plate numbers.

kstenerud 2y ago
Every system that uses SSN as the primary key is fully testable by itself without third party cooperation. And yet every one of these systems has required workarounds because what the designers thought to be invariants, weren't. And this is ALWAYS the case with identifiers that you don't control. You don't make policy decisions about them, but SOMEBODY does. And that somebody isn't aware of - and wouldn't even care - about the invariants that you ASSUMED they followed (and maybe they really did follow them, but they sure don't anymore! Oops...) Fixing broken invariants after the fact is always a nightmare, because it only comes up once you get stuck - either you can't enter something into the database that you absolutely MUST by the weekend, or you can't change something in the database that you absolutely MUST by the weekend. So you do some last minute hacks to get things kind of working, and then it works for awhile until the next problem (usually involving your hack). It's hardly any extra work or complexity to just use an ID generator for the primary key. You'll still have the same indices, the same foreign key linkages etc. You have no reason not to do this. And yet somehow people always seem to fall for the "Oh cool! This existing ID does everything we want! Let's just use that instead of adding one more field to the table! I'm so clever!"

jimbobthrowawy 2y ago

Licence plate numbers are an interesting one, since what those mean varies from country to country. Here (Ireland), they are assigned to the car itself via VIN, are never meant to change once assigned, and are backdated based on information about the vehicle itself (e.g. year of first registration even if first registered in another country, following an older format if applicable), but in other countries they can be reassigned to other vehicles. e.g. 06-LK-12345 might be your car's plate if you move your foreign 2006 car here and register it while living in Limerick, but buying a new car might give you a plate like 242-L-12345 since the format of the first two fields changed since. If you leave and later return with that car, re-registering it gives you the exact same number. https://en.wikipedia.org/wiki/Vehicle_registration_plates_of_the_Republic_of_Ireland https://en.wikipedia.org/wiki/Vehicle_registration_plates_of...

akira2501 2y ago
You don't have vanity plates in Ireland?
- ThePowerOfFuet 2y ago
  Many, many countries do not.
  - cheema33 2y ago
    Vanity plates are essentially free money for the govt. I am surprised every govt. body doesn't go for it. If you really don't like them, make them super expensive.
    - rsynnott 2y ago
      Tbh when you factor in the administration, I'd question whether they actually make any money on these.
- Macha 2y ago
  There aren't any, the closest is that some authorities reserve low sequence number plates for e.g. the local mayor

andrelaszlo 2y ago

Official registration numbers, such as Swedish personal identification number, or "personnummer" (date of birth + serial + checksum [Luhn], where even serials are used for females and odd for males): - It can take a few days before a newborn is assigned a number - Non-citizens don't have one, but they can get a coordination number on the same format but with the date part incremented by 60 days. - Citizens can have both a coordination number and a personal identification number in certain cases. - They can be changed if the wrong birth date or gender registered at birth or during immigration, for protected identities, or for gender transitions.

Aaargh20318 2y ago
In the Netherlands we have something similar: the BSN (translated: citizen service number). This used to be the social security number, but because it was used by an increasing number or government agencies unrelated to social security it was changed to BSN. One of the major problems with this number is that it has a special status under the law. There are very strict rules as to who can process and/or store this number and for which purpose. For example: a bank can process this when opening a bank account, under anti money laundering regulations, but they cannot use it to identify an existing customer. If you originally set up your database to use SSNs you now have a problem. This actually happened with our chamber of commerce: if you registered a one-man business they used the SSN as the business id and you’re required as a business to publish this. Now it’s suddenly a number that is subject to strict privacy rules and they have to renumber all one-man businesses. So that’s another problem with data you don’t control: the legal status of this data can change.
wasmitnetzen 2y ago
Nitpick: long-term residents get a personnummer as well, not just citizens.

nailer 2y ago

I was this recently. I moved to US in November and it was February before I had an SSN. A bunch of companies had to put in fake SSNs into their system, which they have a standard for.

deleted 2y ago

[deleted]