ID design and primary keys
13 points by squadette
13 points by squadette
Instead of introducing new terms like “external ID” and “anchor ID”, using the already-established “natural key” and “surrogate key” would give readers who want to dig deeper something to search for, and a much larger existing discussion to find.
We cannot use ISBN, because some books do not have ISBN. We cannot use the title: maybe we have five different Bibles in our collection, and so on.
Two different books can also end up sharing the same ISBN. ISBN assignment is up to the publisher, and some publishers reuse the ISBN of a book that sold poorly for a later one. That's not supposed to happen, but it does in practice, which makes ISBN a poor natural key too.
The thing is that I find a lot of existing terminology, including terms like "natural key" and "surrogate key" (and "normalization" too), completely unsatisfactory for the purposes of teaching actual database design, starting from business requirements.
Did you create this terminology pairing "external IDs" and "anchor IDs"? It seems like a great way of disentangling two similar concepts that people often talk past each other on.
Yes. I've introduced it in the book, but in a somewhat handwavy way (because I was not sure how exactly those concepts coexist with primary keys, unique constraints etc). Now I've bit the bullet and am trying to deconstruct all of this.
Thank you!
Overall, I found the post interesting with the separate terminology (as pushcx pointed out, it's weird that there isn't standard terminology for this).
Some minor quibbles on technicalities & questions:
at any given moment there is exactly one entity corresponding to each external ID
I think you meant to say "at most one" here? For the user handle example, a handle can become abandoned.
(I guess this example was picked for familiarity for American readers but) AFAIK SSNs are not guaranteed to be unique, if you have people who got SSNs assigned before 1961 using a service.
Also, prior to 1961 SSA field offices issued new SSNs. Only a fraction of these SSN assignments were screened at the central office for a previously assigned SSN, and then only manually (Long 1993, 84). Thus, issuing duplicate SSNs was possible. Beginning in 1961, the central office in Baltimore issued all new SSNs, but it was not until 1970 that an electronic method of checking for previously issued SSNs (called "EVAN" for "electronic verification of alleged numbers") was devised (SSA 1990, 4).
https://www.ssa.gov/policy/docs/ssb/v69n2/v69n2p55.html
An additional requirement for the anchor ID is that it is immutable: its value never changes
It's not super clear what this means. Does it mean append-only (i.e. you can't reuse old IDs at any point?)? Or does it allow for deletion + reuse so long as all referents are deleted first?
Note that our system needs to validate and authenticate those external IDs even though they were generated by our system
I think you mean "perform authorization checks for" instead of "authenticate" here?
Thank you,
I think you meant to say "at most one" here?
Yeah, there are several sentences in the text that are nitpickable, but at some point I realized that if I'll try and fix them all to become unambiguous the text becomes unreadable.
The idea of dangling IDs is sort of obvious IMO — everyone are familiar with 404 pages, array index out of bounds etc.
More importantly, I don't like the "at most one" wording because I've seen what happens when people try to extend this to "at most three". So for me it's important to avoid arithmetic comparisons here and stay with "unique existence or non-existence".
I think you mean "perform authorization checks for" instead of "authenticate" here?
Yeah, I think I need to fix it in the next draft, thank you!
It's not super clear what this means. Does it mean append-only (i.e. you can't reuse old IDs at any point?)? Or does it allow for deletion + reuse so long as all referents are deleted first?
Yeah, I think that this part would go into the "Nitpicking" chapter, because you sometimes have to deal with stuff like that in practice.
One other similar thing that I want to talk about is: what if you just want to get yourself a different ID, maybe just due to numerological reasons? Like, you have users.id, nobody ever sees that but you just want it to be different. You will have to somehow change values in all tables that refer to that ID, and do that simultaneously.
But in the end all of that discussion just confirms what you already understand: that's why we try not to do anything like this and just use autoincremented numbers or whatever.