4 ms·

There's a better solution for many of the exceptional cases that the author describes: aliases & audit logs. Take for example the Danish CPR number. That's pe

There's a better solution for many of the exceptional cases that the author describes: aliases & audit logs. Take for example the Danish CPR number. That's perfectly fine as a natural key; its definition is the first CPR number assigned. 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. Or the chassis number. Also fine as a natural key. If it's changed because of a data-entry error, you also want to record a.) the date of change b.) who changed it. This opens up a whole host of auditing, monitoring, and reporting functionality that eg. lets you catch fraud, determines if a single person is being sloppy, identify mass changes in policy, notify and update external records of owners, etc. URLs are another good natural key: they are defined to be unique (otherwise your webserver won't work), they make for very easy lookups when you're fetching from a web request, and if they change, they break the web. Except that they do change. But when a URL changes, you don't want to just update them in the database everywhere, because again, that will break the web. You want to leave a redirect from the old to the new one. So you create a redirects table of all the other aliases that point to a given page, use it to generate server redirects, and you can throw in other data like the time of change or hit counts on each individual alias.

tapland 2y ago
It's not really fine. I work on a Healthcare system in the nordics. Who you billed, who visited what doctor, who your primary care provider is, all the people a doctors office has as patients, refers to the SSN. You don't want to lose that connection or to have to update everything for any change. You store a unique identifier for the person in the system, and you can then pull the actual personal identification number when needed. You do not keep individual private lists of people changing genders.
- tapland 2y ago
  > You do not keep individual private lists of people changing genders. On this note: Some organization in Sweden actually does store changes like suggested in the top comment. Both the tax agency and police were interested in knowing who did, and who had access, since it's the cause of recent mass doxxing events.
- nikita2206 2y ago
  But there’s no natural key for a person, not even the SSN. This article also starts off by showing an example where no natural key exists (Restaurants) and acknowledges it.
  - wesselbindt 2y ago
    Solution: use a synthetic one
ironchef 2y ago
You appear to be describing a classic type 2 or type 4 slowly changing dimension. (https://en.wikipedia.org/wiki/Slowly_changing_dimension https://en.wikipedia.org/wiki/Slowly_changing_dimension )
lolinder 2y ago
I'm a fan of audit logs, but what you're describing is more event sourcing than audit logs. From what I understand what you're suggesting is that you don't change the key in the original table, you just record the change in a second table. Presumably, then, when you need to read a row you must also look up the list of diffs for that row to make sure that the natural key hasn't changed. Two things strike me about this proposed model: First, under your proposal the version of the model that you work with at the application layer must have two copies of the key field—one is the database key for when you need to make changes or look up more data and one is the meaningful business-model field that's actually up to date. That's exactly the same extra mental overhead that natural keys are supposed to have solved, only worse because the fields will have similar names and will contain the same content most of the time, making it easy to accidentally use one where the other was expected. Second, if you're going to introduce effectively an event-sourced data model then you've introduced a ton of new database records already, so why not just give everything a proper unique key while you're at it? Once you've done that you can modify the original row after all (while retaining the audit logs!) or, if you're serious about event sourcing, cache the latest derived value and look it up by its database key instead of carrying around an out of date natural key that's just waiting to be used incorrectly.
phkahler 2y ago
>> 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.
trevor-e 2y ago
URLs can be tricky and have plenty of gotchas depending on what you're trying to do. For example, the order of query params is free to change but it's still the same URL. Nothing that can't be worked around with a little normalization.
- Karellen 2y ago
  > For example, the order of query params is free to change but it's still the same URL. Are you sure that's guaranteed by any spec? I thought an end-point would be free to treat `?a=1&b=2` and `?b=2&a=1` differently. I mean, it would be a nightmarish implementation, but I don't think it would be non-conforming?
  - Piskvorrr 2y ago
    It's a mere convention: "a querystring of this type is an array of parameters, the order of which is irrelevant" - this is one of the tautological "valid except when it's not" non-rules (a surprising number of cases). Example: ?a=1&b=2&a=3 - will the server treat this to be equivalent as ?a=3&b=2&a=1 , or ?b=2&a=1 , ?b=2&a=3 , or something else entirely? You'd need to check the serverside parsing implementation to be sure - those could even be valid (and distinct) filenames FWIW. (And that's before you get to caching - "?b=2&a=1 is not to be served as a cached version of ?a=1&b=2")
    - trevor-e 2y ago
      The repeated case is something I hadn't thought of when I wrote the original comment since I would never do that so this got me curious. Looks like it _isn't_ guaranteed by any spec, and different frameworks have their own rules as you pointed out. For example, PHP will take the last duplicate param value as the "winner", and others group them into an array. While I think this would be a bad application design, it looks like swapping the order of params could result in errors in some cases. In practice though I would treat the URLs the same.
      - Piskvorrr 2y ago
Pengtuzi 2y ago
> URLs are another good natural key: they are defined to be unique No, see more info here: http://localhost:8080/ http://localhost:8080/
shkkmo 2y ago
> Take for example the Danish CPR number. That's perfectly fine as a natural key; its definition is the first CPR number assigned. This is a horrible idea. You now have two different pieces of information that are identical in form and indistinguishable in the majority of cases: "first CPR number" and "current CPR number. Every place you enter, or use a CPR number, you now must track which piece of information you have. If you make a mistake doing this, it will be hard to catch. Now you've decided that first piece of information is a natural key and will be every single place the record is used, even if that spot doesn't do anything specific to the CPR number. Every single time a CPR number is ingested from an exterior source, you need to do a lookup to make sure it is the original CPR number and then track it. Ever place you don't do this lookup is a place where an error can creep in if something changes in your pipeline our source. Even in an context where external sources are using something like a CPR number as an key, I would still use a different key internally since I see only downsides to using a "natural key". > URLs are another good natural key: they are defined to be unique (otherwise your webserver won't work), they make for very easy lookups when you're fetching from a web request, and if they change, they break the web URLs are also horrible natural keys. They are not defined to be unique and provide no guarantee that the content has not changed or even that the same content is sent to different users. If the location for content changes, you may or may not get a redirect. If you do get a redirect, you'd have to now go update every single place that uses the key to the new value. It is much better to map URLs to an artificial key and update that mapping in a single place.
IggleSniggle 2y ago
URLs are only really uniform not necessarily unique. The most obvious case is something like `http://localhost/file.txt http://localhost/file.txt`, where the resource being located is almost sure to be different on every single "localhost," but this is true of any server. The pointer is neither unique nor has any true guarantee that the resource being pointed to can or should be considered unique for any given context. It is merely unique within its calling context, which, when dealing with URLs, is often anywhere on the web where DNS responds. Even if you presume that the resource being located is in fact a unique resource in the general case, the "unique resource" may not be unique in the way that you presume. Some URLs at a given location are intended to be idempontent and cache-able, others are not, and many are time-limited forwarders. And there's no guarantee or even expectation that two identical forwarding URL's will resolve to the same location; it may well be network-topography dependent.
crote 2y ago
> Take for example the Danish CPR number. That's perfectly fine as a natural key; its definition is the first CPR number assigned. The problem is that the new CPR number is now the one you want to use for all display purposes and future interactions with external systems. In other words, you can't use the "original CPR" field for anything except as key. It's no longer a CPR field, because it no longer has any relation to the person's CPR! And at that point it'd be better to just use a GUID or something as key and avoid any potential confusion between the "real" CPR and "fake" CPR, because when the two are the same 99% of the time it is guaranteed to cause a shitton of bugs. The only solution is to essentially rewrite all your records with the new CPR as key, and leave a redirect entry at the old CPR. That's pretty much what happens in Sweden when you change your gender: your old identity ceases to be and you're issued a completely new one.
samspot 2y ago
>> That's perfectly fine as a natural key; its definition is the first CPR number assigned. I don't know about CPR's, but in the US SSN's get recycled. So two people can have the same "first SSN" assigned.

tapland 2y ago

It's not really fine. I work on a Healthcare system in the nordics. Who you billed, who visited what doctor, who your primary care provider is, all the people a doctors office has as patients, refers to the SSN. You don't want to lose that connection or to have to update everything for any change. You store a unique identifier for the person in the system, and you can then pull the actual personal identification number when needed. You do not keep individual private lists of people changing genders.

tapland 2y ago
> You do not keep individual private lists of people changing genders. On this note: Some organization in Sweden actually does store changes like suggested in the top comment. Both the tax agency and police were interested in knowing who did, and who had access, since it's the cause of recent mass doxxing events.
nikita2206 2y ago
But there’s no natural key for a person, not even the SSN. This article also starts off by showing an example where no natural key exists (Restaurants) and acknowledges it.
- wesselbindt 2y ago
  Solution: use a synthetic one

ironchef 2y ago

You appear to be describing a classic type 2 or type 4 slowly changing dimension. (https://en.wikipedia.org/wiki/Slowly_changing_dimension https://en.wikipedia.org/wiki/Slowly_changing_dimension )

lolinder 2y ago

I'm a fan of audit logs, but what you're describing is more event sourcing than audit logs. From what I understand what you're suggesting is that you don't change the key in the original table, you just record the change in a second table. Presumably, then, when you need to read a row you must also look up the list of diffs for that row to make sure that the natural key hasn't changed. Two things strike me about this proposed model: First, under your proposal the version of the model that you work with at the application layer must have two copies of the key field—one is the database key for when you need to make changes or look up more data and one is the meaningful business-model field that's actually up to date. That's exactly the same extra mental overhead that natural keys are supposed to have solved, only worse because the fields will have similar names and will contain the same content most of the time, making it easy to accidentally use one where the other was expected. Second, if you're going to introduce effectively an event-sourced data model then you've introduced a ton of new database records already, so why not just give everything a proper unique key while you're at it? Once you've done that you can modify the original row after all (while retaining the audit logs!) or, if you're serious about event sourcing, cache the latest derived value and look it up by its database key instead of carrying around an out of date natural key that's just waiting to be used incorrectly.

phkahler 2y ago

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

trevor-e 2y ago

URLs can be tricky and have plenty of gotchas depending on what you're trying to do. For example, the order of query params is free to change but it's still the same URL. Nothing that can't be worked around with a little normalization.

Karellen 2y ago
> For example, the order of query params is free to change but it's still the same URL. Are you sure that's guaranteed by any spec? I thought an end-point would be free to treat `?a=1&b=2` and `?b=2&a=1` differently. I mean, it would be a nightmarish implementation, but I don't think it would be non-conforming?
- Piskvorrr 2y ago
  It's a mere convention: "a querystring of this type is an array of parameters, the order of which is irrelevant" - this is one of the tautological "valid except when it's not" non-rules (a surprising number of cases). Example: ?a=1&b=2&a=3 - will the server treat this to be equivalent as ?a=3&b=2&a=1 , or ?b=2&a=1 , ?b=2&a=3 , or something else entirely? You'd need to check the serverside parsing implementation to be sure - those could even be valid (and distinct) filenames FWIW. (And that's before you get to caching - "?b=2&a=1 is not to be served as a cached version of ?a=1&b=2")
  - trevor-e 2y ago
    The repeated case is something I hadn't thought of when I wrote the original comment since I would never do that so this got me curious. Looks like it _isn't_ guaranteed by any spec, and different frameworks have their own rules as you pointed out. For example, PHP will take the last duplicate param value as the "winner", and others group them into an array. While I think this would be a bad application design, it looks like swapping the order of params could result in errors in some cases. In practice though I would treat the URLs the same.
    - Piskvorrr 2y ago

Pengtuzi 2y ago

> URLs are another good natural key: they are defined to be unique No, see more info here: http://localhost:8080/ http://localhost:8080/

shkkmo 2y ago

> Take for example the Danish CPR number. That's perfectly fine as a natural key; its definition is the first CPR number assigned. This is a horrible idea. You now have two different pieces of information that are identical in form and indistinguishable in the majority of cases: "first CPR number" and "current CPR number. Every place you enter, or use a CPR number, you now must track which piece of information you have. If you make a mistake doing this, it will be hard to catch. Now you've decided that first piece of information is a natural key and will be every single place the record is used, even if that spot doesn't do anything specific to the CPR number. Every single time a CPR number is ingested from an exterior source, you need to do a lookup to make sure it is the original CPR number and then track it. Ever place you don't do this lookup is a place where an error can creep in if something changes in your pipeline our source. Even in an context where external sources are using something like a CPR number as an key, I would still use a different key internally since I see only downsides to using a "natural key". > URLs are another good natural key: they are defined to be unique (otherwise your webserver won't work), they make for very easy lookups when you're fetching from a web request, and if they change, they break the web URLs are also horrible natural keys. They are not defined to be unique and provide no guarantee that the content has not changed or even that the same content is sent to different users. If the location for content changes, you may or may not get a redirect. If you do get a redirect, you'd have to now go update every single place that uses the key to the new value. It is much better to map URLs to an artificial key and update that mapping in a single place.

IggleSniggle 2y ago

URLs are only really uniform not necessarily unique. The most obvious case is something like `http://localhost/file.txt http://localhost/file.txt`, where the resource being located is almost sure to be different on every single "localhost," but this is true of any server. The pointer is neither unique nor has any true guarantee that the resource being pointed to can or should be considered unique for any given context. It is merely unique within its calling context, which, when dealing with URLs, is often anywhere on the web where DNS responds. Even if you presume that the resource being located is in fact a unique resource in the general case, the "unique resource" may not be unique in the way that you presume. Some URLs at a given location are intended to be idempontent and cache-able, others are not, and many are time-limited forwarders. And there's no guarantee or even expectation that two identical forwarding URL's will resolve to the same location; it may well be network-topography dependent.

crote 2y ago

> Take for example the Danish CPR number. That's perfectly fine as a natural key; its definition is the first CPR number assigned. The problem is that the new CPR number is now the one you want to use for all display purposes and future interactions with external systems. In other words, you can't use the "original CPR" field for anything except as key. It's no longer a CPR field, because it no longer has any relation to the person's CPR! And at that point it'd be better to just use a GUID or something as key and avoid any potential confusion between the "real" CPR and "fake" CPR, because when the two are the same 99% of the time it is guaranteed to cause a shitton of bugs. The only solution is to essentially rewrite all your records with the new CPR as key, and leave a redirect entry at the old CPR. That's pretty much what happens in Sweden when you change your gender: your old identity ceases to be and you're issued a completely new one.

samspot 2y ago

>> That's perfectly fine as a natural key; its definition is the first CPR number assigned. I don't know about CPR's, but in the US SSN's get recycled. So two people can have the same "first SSN" assigned.