3 ms·

>> If a person's CPR number changes because they've changed their gender, you will want a separate table recording a.) the date of the change. The new CPR numbe

>> If a person's CPR number changes because they've changed their gender, you will want a separate table recording a.) the date of the change. The new CPR number is not valid before that time b.) the new gender c.) probably the reason for the CPR number change, since if the policy now is that they can change because of a gender change, there's a decent chance they'll be some other policy in the future that results in a new CPR issuance. But what if you have a bunch of records in some other table - billing information for example, and it's indexed by the CPR number (a foreign key). When they change the CPR number you can no longer query for their entire billing history based on CPR. None of your proposed extra complexity does anything about this problem. The only good way to solve it is to use a synthetic key with NO meaning. It would still be good to do as you say and track all the CPR number changes for a given person, but they will still need a unique key. So a sort of "identity table" used to figure out what unique key you're dealing with.

wild_egg 2y ago
Why could you not join with the audit table and find historical billing information from the old CPRs?
- lolinder 2y ago
  You could, but that introduces a lot of complexity that would be saved by just giving the customer a synthetic ID that uniquely and stably identifies them in your system. The grandparent's proposal basically turns the first-entered CPR into a meaningless ID field that should not generally be used in a piece of business logic (unless for some reason you need to display "first CPR they entered"). Once you've declared that you should look elsewhere if you actually need the CPR, why did we even bother using it as the primary key?
- phkahler 2y ago
  >> Why could you not join with the audit table and find historical billing information from the old CPRs? 1) complexity and 2) what is actually tying the CPRs together? We're not going to have a CPR table per-customer, so all the CPRs of every customer are in the same table. Presumably the CPR table has a unique key for the customer that can be used to associate multiple CPRs with that person, so we have come full circle - just use that unique key in the audit table.
nostrademons 2y ago
You need this complexity anyway - if you had surrogate keys, the primary key would be an opaque identifier, and to do anything involving the CPR, you'd need to join against your CPR table (which, again, needs to be 1:many because CPRs themselves are not a 1:1 relationship). The first CPR in this case becomes identical to your surrogate key - it's an opaque ID that you use to reference other tables in the DB - but with the added benefit that for the common case, you don't need any additional lookups. You only need to lookup CPR changes if you don't find the CPR that the user gave you. And then it has other added benefits in that you have a record of CPR changes, you can understand how common a case this is, the CPR change table itself has semantic meaning and you can query a wide variety of properties without joining your primary user table, etc.
- shkkmo 2y ago
  > but with the added benefit that for the common case, you don't need any additional lookups. You only need to lookup CPR changes if you don't find the CPR that the user gave you Doing the CPR lookup ONLY when you don't find a record is really, really stupid. All you need is one bad cache, one database update that go rolled back and suddenly you've lost all recent updates the the user and will only see the bad record with no indication you've failed. Trying to use force changeable data into being a 'natural key' means that the number of edge cases you have to predict, write around and test for is going to rise significantly for no real benefit.
- lolinder 2y ago
  > The first CPR in this case becomes identical to your surrogate key - it's an opaque ID that you use to reference other tables in the DB - but with the added benefit that for the common case, you don't need any additional lookups. You only need to lookup CPR changes if you don't find the CPR that the user gave you. I'm not buying that this is a meaningful gain in either performance or code complexity. In the world of synthetic keys I look up the CPR in the CPR table and join it to the user table using the synthetic ID. If I find a record for the CPR+user join then I'm set, if I don't then the customer doesn't exist. In the world of natural keys, you're advocating that I first query the user table directly by CPR. Then if I turn nothing up I run a separate query with a join on the original CPR. Then if I still don't turn something up the customer doesn't exist. The code in the second instance is obviously more complicated than the code in the first instance. It has increased risk of someone writing a bug because now there's a very tempting CPR field that will be right most of the time but wrong in some edge cases. Depending on the database and usage patterns, indexing on the CPR may be much less efficient than indexing on an autoincrementing integer. The only thing it has going for it is that I might be able to avoid a single join on a very specific path where a user is looking up a customer by typing in the CPR. That seems like the wrong thing to optimize for in the face of all the downsides.

wild_egg 2y ago

Why could you not join with the audit table and find historical billing information from the old CPRs?

lolinder 2y ago
You could, but that introduces a lot of complexity that would be saved by just giving the customer a synthetic ID that uniquely and stably identifies them in your system. The grandparent's proposal basically turns the first-entered CPR into a meaningless ID field that should not generally be used in a piece of business logic (unless for some reason you need to display "first CPR they entered"). Once you've declared that you should look elsewhere if you actually need the CPR, why did we even bother using it as the primary key?
phkahler 2y ago
>> Why could you not join with the audit table and find historical billing information from the old CPRs? 1) complexity and 2) what is actually tying the CPRs together? We're not going to have a CPR table per-customer, so all the CPRs of every customer are in the same table. Presumably the CPR table has a unique key for the customer that can be used to associate multiple CPRs with that person, so we have come full circle - just use that unique key in the audit table.

nostrademons 2y ago

You need this complexity anyway - if you had surrogate keys, the primary key would be an opaque identifier, and to do anything involving the CPR, you'd need to join against your CPR table (which, again, needs to be 1:many because CPRs themselves are not a 1:1 relationship). The first CPR in this case becomes identical to your surrogate key - it's an opaque ID that you use to reference other tables in the DB - but with the added benefit that for the common case, you don't need any additional lookups. You only need to lookup CPR changes if you don't find the CPR that the user gave you. And then it has other added benefits in that you have a record of CPR changes, you can understand how common a case this is, the CPR change table itself has semantic meaning and you can query a wide variety of properties without joining your primary user table, etc.

shkkmo 2y ago
> but with the added benefit that for the common case, you don't need any additional lookups. You only need to lookup CPR changes if you don't find the CPR that the user gave you Doing the CPR lookup ONLY when you don't find a record is really, really stupid. All you need is one bad cache, one database update that go rolled back and suddenly you've lost all recent updates the the user and will only see the bad record with no indication you've failed. Trying to use force changeable data into being a 'natural key' means that the number of edge cases you have to predict, write around and test for is going to rise significantly for no real benefit.
lolinder 2y ago
> The first CPR in this case becomes identical to your surrogate key - it's an opaque ID that you use to reference other tables in the DB - but with the added benefit that for the common case, you don't need any additional lookups. You only need to lookup CPR changes if you don't find the CPR that the user gave you. I'm not buying that this is a meaningful gain in either performance or code complexity. In the world of synthetic keys I look up the CPR in the CPR table and join it to the user table using the synthetic ID. If I find a record for the CPR+user join then I'm set, if I don't then the customer doesn't exist. In the world of natural keys, you're advocating that I first query the user table directly by CPR. Then if I turn nothing up I run a separate query with a join on the original CPR. Then if I still don't turn something up the customer doesn't exist. The code in the second instance is obviously more complicated than the code in the first instance. It has increased risk of someone writing a bug because now there's a very tempting CPR field that will be right most of the time but wrong in some edge cases. Depending on the database and usage patterns, indexing on the CPR may be much less efficient than indexing on an autoincrementing integer. The only thing it has going for it is that I might be able to avoid a single join on a very specific path where a user is looking up a customer by typing in the CPR. That seems like the wrong thing to optimize for in the face of all the downsides.