8 ms·
Unnatural Keys – Nature doesn’t come with identifiers
- tingletech 3y agoIf you are mapping to a series of bits, then a checksum of some sort seems like a fine identifier to a specific file. But something like "song", minting ARKs[0] for FRBR[1]-esq Work/Expression/Manifestation records would be my first instinct, not a natural key. The Manifestation record could have the checksums of the files. [0] https://arks.org https://arks.org [1] https://en.wikipedia.org/wiki/Functional_Requirements_for_Bibliographic_Records https://en.wikipedia.org/wiki/Functional_Requirements_for_Bi... [1]
- 8organicbits 3y agoFRBR makes a lot of sense. There's some really good insights from library sciences that folks in computer science aught to spend some time studying. [edit] removed a statement about an ARK URL not loading, it loads fine now. Temp issue on my end?
- blooalien 3y agoI agree with the substance of your comment re; FRBR, but ... typo maybe? [1] [1] https://grammarist.com/homophones/ought-vs-aught/ https://grammarist.com/homophones/ought-vs-aught/
- eshnil 3y agoI ran into the same problem while building https://learnawesome.org/ https://learnawesome.org/ . Forget the broad class of "learning resources", even the "books" category doesn't have a usable unique ID. Not everything gets an ISBN for example. There's also the ambiguity between a "work" and an "edition" of a work. This is probably why OpenLibrary supports mapping of books with 40+ identifiers: https://github.com/internetarchive/openlibrary/blob/master/openlibrary/plugins/openlibrary/pages/config_edition.page#L288 https://github.com/internetarchive/openlibrary/blob/master/o...
- makeitdouble 3y agoISBN is an interesting case as the publisher has leeway on how it's handled accross the lifetime of a book. In particular some books will get multiple ISBN for the same version, and some other books can keep the same ISBN through multiple minor revisions.
- atoav 3y agoAs someone who has been thinking about a system to organize my improvisation band's rehearsal recordings I have made many of the same realizations. Only with us some of the points are even wilder, because for us "a song" is the abstract concept or idea for an improvisation rather than a fixed set of notes played in a fixed way in a fixed order. Sometimes that idea can be a word, sometimes it can be very precise sound settings, high-level structural ideas, a set of lyrics, rhythmic patterns, melodic themes etc. So any given recording can contain within itself any number of songs (including zero). Those songs can exist or not. And my conclusion for our case was that there is no automated way of deciding which is which, as at times it could be hard even for us ourselves.
- TeMPOraL 3y agoThis. I'm actually surprised the linked Wikipedia article on "Natural key" only mentions one potential disadvantage, that somewhen in the future, maybe, at some point, somehow, the other system may change, and then your keys are bust. They even give US SSNs as an example of something that may stop working as unique key in the future. There are no "natural keys". They don't exist in nature. They don't exist as a thing in our physical reality. And when it comes to human-assigned identifiers, it should be emphasized much more strongly that whatever external thing you think is a good unique ID, it isn't. Names? Addresses? Dates? Nope - have you heard of the "Falsehoods programmers believe about..."? Government IDs? Nope. US SSN was not designed to be used as UID from the start, but even when you have an identifier that was - like e.g. PESEL number in Poland - it's still not an unique ID, because people and computers make mistakes. Multiple people having the same PESEL is something that's been known to happen. (Side note: "identification number" really means "opaque string". IDs are not numbers. If adding or multiplying them together doesn't make sense, they're not numbers.) Anything business adjacent? Don't make me laugh. The article covered a lot of the reasons why any kind of tracking IDs are broken for the purpose - most of them dealing with the messy nature of reality - but businesses are businesses. They cut corners. They lie and cheat. That's why you see the same EAN code covering multiple products a store might even try and price differently. That's why SKUs get reused. That's why these days you often have multiple distinct products under the same SKU - first, quality batch, released to get early positive reviews, and then the main batch made with much cheaper and lower-quality parts. Product names? That's even worse - you'll have variations such as "extra batch of super-low-quality, reduced functionality, made specifically for Black Friday, so it can be sold under the same name as the OG product, seemingly at big discount". This is not to say "never use other people's IDs". There's a trade-off here, but making a good choice starts with assuming that the external "unique ID" is neither unique, nor a good ID, and you'll have to deal with it at some point.
- amelius 3y agoWhatever you do, always let some manager decide about the actual key, then you can always blame them for their mistake :)
- genuine_smiles 3y ago
- IanCal 3y agoThe really key thing with this is to identify what your database/Id is solving and what it is not. What guarantees are you giving and what problems should you raise to people. You can rarely solve all of them. I helped make GRID which was a database of research institutes in the world (morphed into ROR). But what is one institute? Is it the legal entity? Is a lab an institute? What about a research boat? A hospital? Which is the parent, the hospital or the university? The answer to lots of that is "maybe, it depends". We had to decide what use cases we were solving, and often it came down to some human fuzzy definition, how do people use this in reality? We built it because others had made very strict sets of rules or were very open and then were just not useful. A curated list of a 1000 institutes set up as people generally used them solved most issues. Scaling it up another 10x solved most others. Not all, not for everyone. A recording of background noise or silence isn't a song. Unless of course it's 4'33". > They’re all arbitrary, unnatural, human inventions A really great summary.
- cwillu 3y ago4'33" isn't a song either. If you call a tail a leg, a dog still has 4 legs.
- IanCal 3y agoFor a whole bunch of useful definitions and use cases, it's a song. Arguing about the precise delineations of song/not song is missing my point. > If you call a tail a leg, a dog still has 4 legs. I can't think of a useful definition of legs that includes the tail. I can for calling a recording of a musical piece a song.
- deleted 3y ago[deleted]
- deleted 3y ago[deleted]
- stefncb 3y agoSince we're going into this meta-philosophical thing, your statement is false. If I call a tail a leg, dogs definitely have five legs. Unless they were of course born without a leg, or have otherwise lost one. There is no "reality" from a human perspective, it's all sensory input that goes through a lot of very subjective processing. You can't _really_ categorize things. You can mostly sort of do it, but it never actually works out, because categories are human stuff, and human stuff is subjective and thus different depending on who you ask. Sometimes unclear even to the same person. I can say dogs have five legs and the only reason most people will think I'm wrong is that society has come to categorize limbs based on function instead of length.
- ak39 3y agoAlways use surrogate primary keys. Use alternate keys (AK) for your "natural keys". Modify the AK business rules as and when needed. There are several advantages to using meaningless random integers, sequential integers or UUIDs as your table's PK. The most important one is that if the PK participates (or is expected to participate) as a foreign key in other tables, it's crucial to us a design where you never ever have to change the PK!
- bruce511 3y agoAll this, but I'd steer away from sequential numbers. They suffer from security problems, data-merging problems, data-distribution and replication problems, and more. Over a long career I've come to favor UUID, the cost (space and performance) are well worth the advantages.
- scrollaway 3y agoUUIDv7 has all the advantages of UUID, plus sortability, plus an embedded timestamp which can replace your "created_at" thus reducing the cost aspect. It's also soon becoming an official part of the UUID spec. In fact, if your created_at is indexed and you can get rid of it via uuid7, there is practically no difference in terms of table size. The size cost is solely on foreign keys.
- evntdrvn 3y agoI really hope that I can find someone on the MS SQL Server team to champion implementing native UUIDV7 support, from what I've heard they are really hesitant to add more data types but a person's gotta try :)
- praptak 3y agohttps://en.m.wikipedia.org/wiki/Identity_(philosophy) https://en.m.wikipedia.org/wiki/Identity_(philosophy) For when you want to think real deep about what the identifier identifies.
- fatneckbeard 3y agoor whether it identifies https://en.wikipedia.org/wiki/Philosophical_skepticism#Epistemological_skepticism https://en.wikipedia.org/wiki/Philosophical_skepticism#Epist...
- Anduia 3y agohttps://archive.ph/vuXrb https://archive.ph/vuXrb
- ur-whale 3y ago> we want a database of all of the songs in the world so that we can properly identify unknown songs and provide attribution so that folks can get paid appropriately. It is a noble goal That it is a noble goal is this internet rando's own opinion, and there certainly is no consensus on that. Inflicting it as a fait-accompli to his readership does not bode well for the rest of the article
- cubefox 3y ago"To keep reading this story, get the free app or log in."
- praptak 3y agoSlap a synthetic key on everything, you probably overestimate how bad it is to have multiple records for "the same thing". Even if it is indeed bad, it's probably unrealistic to expect to never let it slip on entry (however good the natural keys might be). Maybe it makes more sense to incorporate deduplication into the design right from the start.
- jack_squat 3y agoWhen you use a surrogate key, you should still put a unique constraint on an alternate key. Multiple records for the same thing is a fundamental problem in a database.
- hdudhsyw 3y ago[flagged]
- xigoi 3y agoMedium has had paywalls for a long time.
- TazeTSchnitzel 3y agoIn Sweden, almost everyone is assigned a number at birth, a personnummer, with the format YYMMDD-NNNN (or YYYYMMDDNNNN in computing, to avoid having a Y2K issue every time someone turns 100). A lot of computer systems here seem to treat it as a natural key, which is a terrible idea, because: • Not everyone has one (notably, asylum seekers and temporary residents don't), so if you rely on them, you're excluding those people. • They can change. They won't for 99.9% of people, but not everyone is so lucky. If you use these as your primary key, you're going to create a headache for you and your users for that 0.1% of cases.
- jackjeff 3y agoI think that's true for Social Security numbers in France/US, and National Insurance numbers in UK. About 15 years ago, after working in Germany I came back to France for a couple of years, the country where I was born / assigned a number at birth / following a similar pattern with the year in the code. My health insurance from Germany did not transfer according to EU rules or something, at least that's what the regional French National Security told me. The way they got me out of the Kafkaesque situation was to give me a "temporary" social security number, like they would give a foreign immigrant, and have it revert back to my "normal" number after 3 months. At some point I had "two" national security numbers. It was a real mess to fix the whole thing afterwards as you can imagine, and considered I left the country only after a couple of years, I wonder why I even bothered.
- tough 3y agoFrench bureaucracy will kill your soul slowly but surely
- c00lio 3y agoYes, but what would be the alternative? Of course you can use artificial keys like a random UUID for each of your db objects. Which will work quite well if you only interact with your own system. But as soon as one of your db objects needs to be linked to some other system, you will need some common ground for a correlation, and something like a personnummer will help immensely and solve 99.9% of your problems. The 0.1% of problematic cases is far less than the headaches that the absence of some common ID scheme will cause you. Imagine e.g. having to correlate on the usual "Name, First Name, Birth Name, Place of Birth, Date of Birth" dance: My place of birth used to be called Lower Unxton at the time of my birth, now it has been reformed with Upper Unxton and Exampleville into Greater Unxton. Which one do you want? Did I always give the same answer to that question? My parents also divorced and my birth name legally changed. Do you want the old or the new birth name? Btw., my legal date of birth is actually in a non-gregorian calendar. How do I input that into your form field, it doesn't seem to like Showa 50 as a year, not even to speak about the proper characters? I'd claim any imperfect personnummer is still far superior to all the problems one would have without it.
- karol 3y agoI think in the future the idea of labelling every passing moment and every sounds that anything ever makes will lose prominence. Wondering what will replace them. I think we will either drop this need altogether or the "natural key" will come from "the state of reality".
- dvdkon 3y agoConsistency in one database is important and "natural keys" can help, it's just a bad idea to apply them to messy nature. They're great for derived or linked data, though.
- swayvil 3y agoone might even say that all form is interpretation. Or that all facts are assertions.
- fwlr 3y agoNature doesn’t come with identifiers; that is why Apollo gave us UUIDs. And always remember, the goal of a database is not to model the domain - it is to model your knowledge of the domain.
- karmakaze 3y agoIronically DNA is an identifier and nature does use identifiers like facial recognition, etc--it only happens not to be text-based nor 100% unique/accurate.
- fanf2 3y agoDNA is not an identifier https://en.wikipedia.org/wiki/Chimera_%28genetics%29 https://en.wikipedia.org/wiki/Chimera_%28genetics%29
- SnowHill9902 3y agoThis is not true. Given two songs A, B, an informed human can tell whether A and B are the same song or not. Now you’ve created a UNIQUE constraint on your data, so you have a unique identifier.
- quesera 3y agoThis is not true. The article gives the great examples of a song through its lifecycle: covered, remixed, remastered, compiled on a greatest hits record, included on a soundtrack, etc. Same song, sometimes. Same recording, sometimes. Different database entries, always. To the extent that "an informed human" and "the same song" have meaningful definitions of "informed" and "same", you've just recreated the problem.
- lucas_membrane 3y agoAll this song data has been standardized and revamped multiple times and these standards have been implemented in mp3 players to decode and display info about that to which you are listening. Waitaminute -- mp3 players can play lots of sounds, including non-song sounds, e.g. speeches, air-checks, phone messages, commentaries, advertisements, radio dramas or comedies, bird-calls of birds, bird-calls of hunters, podcasts, phone-taps, recipes, soundtracks for walking or driving tours, audiobooks of diverse genres, etc, etc, which are quite likely to be acceptably identified and described by various attributes other than those applicable to songs. However, humans find it easier to put square pegs in round holes than to rebilk Romeo in a day.
- quesera 3y agoBut MP3 tags don't attempt to store a universally unique song identifier. They live in situ with the file, and multiple encodings of the same canonical "song" may not be identical.
- xipho 3y agoNature + Identifiers is an issue (e.g. see https://github.com/tdwg/tag/issues/36 https://github.com/tdwg/tag/issues/36). I've routinely mentioned to others in my field to look at other industries for ways forward, particularly ones like the music industry, so its interesting, if unsurprising to see all the same problems arise there. For those who know, in our field, when Identifiers comes up in conversation at conferences etc., we tiptoe away, somehow people can't learn from the past issues. There are two issues that keep coming up in my mind: 1) People want Identifiers to do something (like resolve), rather than just be identifiers and 2) People think that there are such things as "unique" identifiers (one identifier per "thing"). Neither, in my mind, are the purpose of identifiers. Identifiers should do one thing, localize you to some concept. By localize I mean that if you can find the digital space (or in physical collections where identifiers are used the physical "printed" identifier) that "contains" the identifier then you should have a reasonable probability of finding the thing/concept that identifier is for. That's all. No certainty, no uniqueness. It's very akin to what we do when we cite something in a publication, we are giving the researcher who reads it a reasonable chance of finding the origin. This isn't to say that we shouldn't try to keep identifiers unique though, it's to say that when it comes down to crunch time we should never assume 1) that they are unique, and 2) that their special properties (e.g. that they resolve) actually work. I've seen numerous identifier schemes come and go. We have specifically designed a 1-many for things-to-identifiers in our systems (sitting on top our internal IDs, yay, another ID). DOIs? They must be unique, right? Nope. Institutional CODENs? Nope (though the botanists have done it pretty well through community peer-pressure). People IDs? Do you mean wikidata or ORCiD or? As others have noted, identifiers really are just labels, though things like UUIDs have the game-changing property of reducing the probability that you're looking at a homonymous label.
- jack_squat 3y agoI also tend toward surrogate keys, and would also point out that the overhead concerns frequently weigh in favor of them as well (though not always). If your natural key is bigger than the appropriately sized surrogate key, and it will be referenced across the database in other tables, the overhead of referencing it everywhere (and indexing it in those places -- potentially in much larger tables) can easily outweigh the overhead of the extra bytes on the original table. Additionally, surrogate keys can realize much better performance WRT cache locality.
- karmakaze 3y ago> DALL-E — “A photograph of meat in the shape of a key on a solid background” I feel like I'm now reading Matrix code where we can do away with the image itself and just keep the prompt.
- karmakaze 3y agoI deal with inventory systems and there is a difference between your system's key and a 'natural' one--the natural one existed first. The good answer is to support both and use either as appropriate. There are many ways of ID'ing an item, usually a SKU (literally stock keeping unit) is used. The owner of the items can choose to use an existing one (manufacturer, UPC, etc) but more commonly assigns their own so that interchangeable items with different manufacturer codes can share a SKU. Ideally a system should support as many methods of ID'ing as the user deems useful. If you do use surrogate keys everywhere, avoid exposing a one if a pair of existing ones will do. e.g. rows of an association table, unless of course that association has attributes and is a well-known thing to itself. Making every user of your systems always use system generated identifiers (where external ones already in use commonly exist) is kind of like a file-system that only uses tmp-generated names and the user has to do external book-keeping to remember which is which.
- wolfgang42 3y agoa "natural key" is frequently just a really foreign key in a database you and your org don't manage. — 'wwweston, https://news.ycombinator.com/item?id=27349246 https://news.ycombinator.com/item?id=27349246
- chefandy 3y agoI hate to throw OT shade, but as a commercial artist, I'm gonna– the hero photo in this article shows the importance of my profession. Creating images is a lot easier than deciding how best to visually communicate an idea and conceptually manipulate the most relevant components. I probably could have made a vector drawing in 10 minutes, or a photorealistic image in photoshop in about 15 that would have done the job a million times better, and considering how much cheaper artists and designers are than developers, it probably would have been about the same labor cost if money was involved. I get it... It's a blog post... But it's seriously like the clipart revolution all over again, just without the guard rails provided by pre-made imagery.
- jeff-davis 3y ago"There are no real world natural keys — only someone else’s surrogate key... SSN, VIN, email address, UPC, tax numbers, country codes, language codes, URIs… they’re all just someone else’s made up identifier." Every time this topic comes up, there are major definitional problems, and it seems like either surrogate keys or natural keys get defined out of existence. The author is drawing the line such that natural keys are surrogate keys that are out of your control, and surrogate keys are in your control. That doesn't seem to match the definition from wikipedia: https://en.wikipedia.org/wiki/Surrogate_key https://en.wikipedia.org/wiki/Surrogate_key I agree with the author that control over the ID is an important distinction, but if you're going to make up a new ID, why not just say so? "Surrogate key" just introduces confusion -- it makes it sound like something only programmers and database admins care about, which is definitely not true. If customers are seeing it, everyone in the organization should care that it's in a recognizable pattern (so you don't mix up different IDs from different places) and memorable and easy to understand when spoken.
- lucas_membrane 3y agoVIN is a good example of how this stuff gets pretty crazy. The standards define it so as to be unique, but only unique within defined n year ranges of model years (VIN identifies vehicles), where n depends, however, on the vehicle type, which can be determined from some specific sub-fields of the VIN. So, multiple vehicles may be on the road with the same VIN if at least one has survived more than n model years (which map flexibly and only approximately to ordinary calendar time). In addition, sometimes a manufacturing error produces non-identical vehicles with the same VIN because of blunder by human, machine, or both in concert. These scenarios are resolved by IT means similar to the method used by Mark Twain and his parents to avoid confusion between him and his mythical twin brother.
- 36097082 3y ago[flagged]
- efitz 3y agoSlightly OT but related: one of the problems with identifiers that I have come to accept is that the things underlying the identifiers change over time. Things come and go, businesses launch and fail or are acquired/absorbed into other businesses, humans are born and die, humans change their names or genders, people buy and sell and subdivide or combine real property, people paint things different colors- the only constant is change. And it generally wreaks havoc because people who thought up identifiers for things usually assumed immutability of a mutable thing, and often don't have workable procedures for keeping up with change. I've come to the conclusion that identifiers should be structured in such a way that they have no relationship to what they identify - for instance, never ever ever use a name as an identifier, no matter how you normalize it. All properties of things should be mutable descriptors. Identifiers should be numeric-ish. Identifiers are not user interface, either - don't try to format them for readability - the audience for identifiers should be automation, not humans. You should give humans a way to serialize them, such as a URL or UUID or whatever, but the point is just to port the thing unambiguously between systems, not to be human readable. You can build UX that looks up the ID and shows you what the descriptive properties are.
- MilStdJunkie 3y agoSomething that anyone who's brushed shoulders with the S1000D document specification knows in their guts. S1000D is a CCS (component content system), which "chunks" the content into units called DMs (Data Modules). Each DM has a DMC (Data Module Code), which is also its filename, its natural key, and its addressable unit. The Data Module Code has tons of little IDs indicating all sorts of things[1], but see, they're all tied in to different business processes. I don't think I need to explain that this whole scheme goes batshit haywire at the smallest provocation, leading to many S1000D systems to be kept in the MIL-STD equivalent of a locked sacristy inside of the Logistics office. Locked sacristies don't get updated a whole lot, so the documents are comically - dangerously - out of sync with basically everything. CCSs in general, and S1000D in particular, were architected by people that were outsiders to the broader trends in computing and programming. DITA came from Horn's Information Mapping via IBM, and Rob Horn was a PoliSci guy. IBM did what it could, but the basic notion that "document" could be replaced by "map" was a conceptual leap not taken by equivalent designers with LaTeX and the others in the more programmer-focused world. And compared to DITA, S1000D's far, far, far worse, being shaped by a steering group inside of a steering committee that's controlled by this . . this . . sort of Lovecraftian block diagram, if I am looking at the right issue of the specification and lighting the right candles. Both of the big CCS specs run into big thick walls of complexity as a fundamental consequence of their explicitly not studying computational complexity at design. Alright, yeah, I have a few chips on my shoulder about this. But I hate waste, I hate it with the brilliance of a million suns, and I have seen so many decades of bodies thrown at this problem. It's beyond time at least a few of us raised our hands and asked, "So why does this never work?". The first step, as indicated, is admitting that your mental abstractions aren't actually real, not in the touchy-space-verse, and then to design systems accordingly. [1] Namespaces? What's a namespace? S1000D avoids all things namespaces. Instead, when you have a new MODELIC + SDC combination, you mail a letter to NISPA (NATO Support and Procurement Agency) to register your new MODELIC, and then your DMCs will be - very hopefull y- uniquely addressable. How often do people actually register their MODELICs? Ah, I see you found the flaw in our cunning plan! MIL-STD-3031 (the USAA BREX / S1000D thingy) explicitly tells the publications administrator to NOT register their MODELICs with NISPA, because EUROS ARE GAYYYYYYY. Also, MIL-STD-3031 has some interesting ideas about what different parts of the DMC actually mean, which makes all this really pertinent (I think) to the article.