20 ms·
You'll regret using natural keys
- pphysch 2y agoThe article should be renamed "natural primary keys". Natural primary keys have fatal flaws, but natural "secondary" keys are all sugar. Best of both worlds.
- porker 2y agoAs with everything, natural keys are sometimes useful or even good. Where business rules fit, I like using them. But you have to know that the value will never change or be reused.
- hot_gril 2y agoI don't get why relational DBs are usually taught around natural keys. With MySQL/Postgres you almost always want bigserial PK.
- duxup 2y agoI just have some form of unique id field / synthetic key, everywhere. Even if just for documenting issues it makes life easier. “Table whatever id 12345 is the record in question.” I’ve just seen data / relationships change too much too often in new and interesting ways to believe in using a natural key.
- deleted 2y ago[deleted]
- deleted 2y ago[deleted]
- ltbarcly3 2y agoSometimes you will regret it, other times you won't. Notice that even if you believe that you should 'never use natural keys', that is NOT the same thing as 'always add a generated synthetic key to every table as the primary key'. You should NOT always add a generated key to every table, even if you use a trash ORM that really wants you to do this.
- aaronbrethorst 2y agoThat’s quite the absolute statement to make without even one example.
- hansvm 2y agoMaybe? Their statement was that you should "not always add," as opposed to the much stronger "always not add."
- selcuka 2y agoAt least the latter provided some examples.
- Twisell 2y agoHere's a real world example if you run a script once every 5 minutes that launch sub-task you might be tempted to add a non natural auto-increment number that identify each occurence to create a link between the script and the subtasks. However it will be way more painless to use a timestamp of the script starting point a natural key. This way when shit happen you have a relevant and stable way to identify each occurrence. And timestamp is easily indexable and ordered.
- aaronbrethorst 2y agoFast-forward a couple years: now I have scripts that launch sub-tasks more than once a second.
- yellowapple 2y agoSo use timestamps with sub-second precision, which virtually every SQL database supports (even ones like SQLite that don't have built-in date/time types).
- 2y ago
- Spivak 2y agoNatural keys are great when they're someone else's synthetic keys. My Discord bots make liberal use of this. model: DiscordMessage key = discord_message_id key = discord_user_id key = discord_channel_id key = message_type model: UserData key = discord_user_id key = field_name model: GuildFeature key = discord_guild_id key = well_known_feature_name model: DiscordEvent key = discord_event_id key = discord_guild_id This is how I was always taught to use them so I'm kinda confused what the author is getting at. If the thing you're using as a key can change and that doesn't define the row then you don't have a key. If you're at a conference where everyone has a badge number then that's a great natural key when scanning people into your workshop. If you're at Disney and everyone has a magicband with ids then you can use that as a natural key all over the place when to you that's a visitor.
- fn-mote 2y agoI think this is an excellent example of one of the pitfalls that the author is getting at. (Disclaimer: not a Discord bot programmer.) Suppose discord_user_id is an identifier like @Spivak#2024 that uniquely identifies the user. When Discord forces all users to change to unique ID's and eliminates the disambiguating numbers (or allows users to change them), then where does that leave you? (For the unfamiliar: something like this happened. Not sure if those are really the "discord user id" used in bot service though, but suppose that they are for the sake of the argument.)
- iaabtpbtpnn 2y agoIt probably leaves you in the same place you’d be with a synthetic key of your own: unable to track users across this key change without additional Discord data.
- foota 2y agoYes, but if you then reference that as a foreign key in some other table or system, then whatever migration you need to do will encompass those systems as well, whereas if you use a synthetic key from the start you wouldn't need to change them.
- ray_v 2y agoFeels like this could have used a few more solid examples up-front. I think another good example would be PlayStation Network using the natural key of "gamer tags" as the primary key to identify players would be a good example. Since this effectively locks players into having to keep a gamertag in order to uniquely identify them in the service -- instead of having a synthetic key that carries no meaning or data other than "uniqueness"
- robomc 2y agoyeah the way he described it it's like... well who would ever do that. but foreign keying to emails or usernames is much easier to "accidentally" do and is a classic source of long-term headaches.
- jaggederest 2y agoAs a great example: My steam account name is the email I was using in 2003. I have largely not used it since then. The email on the account has been updated, but the account name? Stuck.
- jimbobthrowawy 2y agoI was about to ask how you got past steamguard without email access. Makes sense if you were able to navigate off it.
- jaggederest 2y agoEmail was changed about 5 years before steamguard release :)
- mths 2y agoLast I checked, Steam still has me logging in with my two decade old hotmail address as my account name. At least it's not something that shows publicly, I think.
- 2y ago
- davidhyde 2y agoA natural key is what I would normally call unique index. Say first, surname and date of birth in an employee table as a bad (poor design) example. As opposed to a surrogate key like personId being an auto incrementing ID which is the norm since it can be easier to use when joining tables. I wish articles like these would explain terminology up front. I found it irritating to wade into the anecdotal trivia and not know what I was reading about and I’m very familiar with how databases work!
- bongodongobob 2y agoOne reason to not do that is because you could then figure out other users IDs by their hiring date. This ended up being a problem for me once with the auxiliary systems that relied on that ID, namely when it was used in links in other applications.
- deleted 2y ago[deleted]
- tadfisher 2y agoAnother reason is sharding, in which case any non-random (to be more precise, non-uniformly distributed) bits are going to skew the partitions.
- eddd-ddde 2y agoWhy is knowing someone's ID an issue? They are meant for identification, if anything it should be a benefit that they are easily guessable.
- selcuka 2y ago> Why is knowing someone's ID an issue? It increases the attack surface as an authorization vulnerability will allow an attacker to enumerate and access all records. Yes, it is security through obscurity, but a random (e.g. UUID) scheme makes it harder.
- 2y ago
- daft_pink 2y agoAnyone that’s spent large amounts of time using vlookup in excel can totally relate. Mashing some fields together as a key works great until it doesn’t.
- projektfu 2y agoThe devil's advocate cases: 1. When duplication occurs and goes unnoticed because the natural key isn't being used, and then perhaps the problem is "corrected" by an administrator in a way that doesn't make sense. For example, someone sets up an account with an street address A, then forgets they had it and sets up an account with street address B. They call and complain that they can't find an old order, or whatever, and the duplication is discovered. The administrator later clobbers one of the addresses but both are in use. A natural key (behaviorally speaking) may have presented the duplication, assuming address isn't part of the natural key. This can be satisfied by uniqueness constraints, etc. 2. When you are browsing the database, you see a bunch of synthetic keys and have to perform various joins to be able to see the relevant data. The synthetic keys make joins easier to write, but make may make ad-hoc queries more time-consuming and difficult.
- roenxi 2y agoA synthetic key is adding an additional dataum - so any problem that could be solved with the original data can be solved by the new data too (worst case, ignore the primary key). That is one of the best reasons to add a proper index - there is almost literally no downside. We're talking maybe a few bytes/record and arguably a trivial amount of administrative overhead. Bearing that in mind: 1. Add a unique constraint on the natural key. 2. If the accuracy of joining by natural key is acceptable, join based on it. If that doesn't work, then a table design without a synthetic key wouldn't be fit for purpose either.
- kubanczyk 2y agoIn other words, don't mistake a primary key with a key or a constraint.
- azlev 2y agoI think somewhat different than the article in some ways. The first example is why `UPDATE CASCADE` was implemented. So it's possible to use natural keys as identity without the fear of children table. At least in most databases it works. The drawback of enumeration is real, so if you expose this key you'll need some authentication/ authorization mecanism. Another good thing in natural keys is that you can eliminate part of the joins. You don't have to join the father table because the key in child table is known. I think the biggest challenge is how to map logins to people. It's very common to interpret both as the same, but they are not.
- arthens 2y ago> The first example is why `UPDATE CASCADE` was implemented. So it's possible to use natural keys as identity without the fear of children table. At least in most databases it works. This is only true in a system using a single database, not replicating data in external services, and not offering APIs. And while it might work today when the service/website is still fairly small and self contained, the requirements might change at any time. So the title (You will [future] regret it) still applies to this solution
- RecycledEle 2y agoFor those who do not know, natural keys are when you have a primary key in a database table that is derived from the data itself. An example is using a car's chassis number as the key for the record describing that vehicle.
- rhelz 2y agoPerhaps a better example would be a fingerprint or a retinal scan instead of social security numbers as natural keys for a person.
- parpfish 2y agoThere another reason not mentioned — if your key is something like a UUID, it’s very easy to define the logic for joining and filtering based on that key. If you were using some sort of string like an email address or username, you have to think about case sensitivity and trimming white space and all sorts of preprocessing and then make sure you do it consistently EVERYWHERE
- Cyph0n 2y agoIdeally, you should aim to sanitize/normalize strings on the write side rather than resanitizing on every read.
- SoftTalker 2y agoYes but any kind of bug could later introduce tainted values. A foreign key contstraint might save you, but not always.
- parpfish 2y agoThat helps part of it, but there are still places for problems to pop up. - you need to do an adhoc query to look something up so you have to type in the key in a where clause - Or you used different sanitation methods in two different databases and you need to join things now - you try to join the table that had the sanitized email key to a different table that just so happens to have email but it wasn’t sanitized because it was an optional field and not the key
- phatskat 2y agoAnd then you get the other stuff like one-off migrations that are done by someone unaware of the business constraints, or a contractor who completely misses the ORM method, etc. These of course should be caught by checks and balances, and you can’t count on anyone knowing what they need to know ten years after an implementation is done
- sgarland 2y ago> you need to do an adhoc query to look something up so you have to type in the key in a where clause Having a surrogate key here doesn’t help. A WHERE predicate can be rewritten as an INNER JOIN quite easily. Or you could use a subquery, or a CTE. Many options. The other problems discussed are an engineering culture problem. Either you value correctness and consistency or you don’t.
- keepamovin 2y agoThe way I think of it is a key, in a database, is a record key. It is intended to identify a particular logical record, not the logical thing which is referred to by the record. An an footnote, I'll add this doesn't mean that you need to have complexities like record versioning, record history, or anything. But couched in those conceptual terms where those things are possible, is a happy and safe space to be. In this space, a database records entries, as if they were each a single paper form with boxes where you fill out, in pencil, to erase if you like or not, the particulars of the thing you're recording. This form comes pre-stamped with a number: the record key. In this cozy little world, you can be imperfect, and mistakenly (or deliberately, as your use case requires), file multiple slips that refer to the same logical thing, and yet all have different file ("record") numbers - or keys.
- jklowden 2y agoYou think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. If the unique identifier for your object — VIN, CUSIP, whatever — if it changes, the world will henceforth refer to it by both. You will need to track both. Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actually a choke point in your data throughput. The natural key forces you to think about what makes the row unique. What identifies it. Sometimes, it makes you go back to the SME and ask them what they mean. Sometimes it makes you reconsider time: it’s unique now, but does it change over time, and does the database need to capture that? In short, what are the boundaries of the Closed World Assumption? You need to know that too, to answer any "not exists" question. To use our professor’s car’s example, we actually do not know the database design. It could well be that the original identifier remained the primary key, and the "new id" is entered as an alias. The ID is unique in the Car table, identifying the vehicle, and is not in the CarAlias table, where the aliases are unique. Oh, you say, but what if the bad old ID gets reused? Good question. Better question: how will the surrogate key protect you? It will not. The reused ID will be used to query the system. Without some distinguishing feature, perhaps date, it will serve up duplicates. The problem has to be handled, and the surrogate key is no defense. Model your data on the real world. Do not depend on spherical horses.
- kayodelycaon 2y agoOf course they won’t save you from external data. The whole point is for your system to have a way to identify rows internally so you can deal with external systems getting wonky without corrupting your own data. All of your concerns are easily solved by a unique index. Using external keys as a forcing function to prevent people from representing data wrong is not great.
- crabmusket 2y agoThe surrogate key uniquely identifies a row in your database, which is an entity just as real and significant as the car or the employee or what-have-you. Don't confuse the two! I agree with you that having a surrogate key isn't going to save you from the reasons why natural keys can be difficult. The complexity has to go somewhere. But not having a unique identifier for each row is going to make things extra difficult.
- mythas 2y agoAnother example that happens surprisingly often in healthcare. A registration clerk will incorrectly enter a personal health number (PHN) into the system. Then the actuall holder of that PHN shows up. If this were the PK then the system just wouldn’t handle this case and the reg clerk would have a huge mess to sort out on the spot. A surrogate key on the Person table allows this registration to be made where 2 people have the same PHN in the system. Then cleanup can be handled after the fact to track down the first person, determine their correct PHN and update the record.
- jen20 2y agoThis sounds like it should probably be a workflow instead. Modelling the intent here is actually important.
- mythas 2y agoCare to expand on this thought? Curious what you have in mind!
- ttyprintk 2y ago(not parent) Think about these different intentions, each deserving an audit trail with different attached metadata: - brand new MRN for a newborn. The system should be able to provide info on the mother, at all points of care for the newborn. - unknown person arrives in the emergency department unconscious. You’ll see imaging named John Doe in this case. The system should be able to retrieve info if and when identity is established. - the machine-readable bracelet given at admission needs to be replaced. This is really two different cases: you intend to create a duplicate bracelet, or to correct an error in which all current info in the system needs to abruptly update.
- ethbr1 2y agoOne of the most surprising things I learned about the US healthcare system is how often everything is mutating or being retroactively updated (assume it applies to other countries too). Things you'd think would be constant after Step 1 often aren't, and processes are tolerant of their being corrected / re-entered after Step 52 has already been completed.
- Kon-Peki 2y ago> how about a personal identification number? In Denmark we have the CPR number, and I understand that the US Social Security Number is vaguely analogous. The US SSN is not guaranteed to be unique, the SSN assigned to a person could change, there is no guarantee that a person with an SSN assigned to them is a US citizen, and there is no guarantee that a US citizen has an SSN - they must be requested, and you don’t need one unless you do something that requires having one. There are also things called ITINs and ATINs that look like an SSN but are not, yet can be used in place of an SSN in a huge range of SSN-required situations! (Please don’t use the SSN as a database key!)
- selcuka 2y ago> The US SSN is not guaranteed to be unique The cases you listed do not mean SSNs are not unique, unless there are people who share the same SSN. You can still define a unique index for the SSN column. A column can be both nullable and unique as each null is different in SQL.
- ars 2y ago> unless there are people who share the same SSN That happens all the time. First of all people steal SSN's and use them (and you are not the police, so it's not your responsibility to do anything about that). Second people make up fake SSN's because they don't want to give you their SSN. People also make typo's, and you can end up with the same SSN. An SSN is not unique in the real world.
- Kon-Peki 2y agoEDIT - SSA claims that numbers are not recycled. But there are known cases where the same number has been assigned to multiple people. Note that in less than 100 years, more than half of all possible SSNs have already been used…
- bloopernova 2y agoSo we need SSNv6? ;)
- 2y ago
- rhelz 2y agoNatural keys are (quite literally) essential to defining entities. Quine had a great slogan for this: "No entity without identity!" Natural keys are how you determine the identity of an object, which is to say, if you have two different referring expressions, how can you tell whether they are referring to the same object, or different objects? Suppose you take the advice of this article, and use, say, social security numbers to identify people. (Let's ignore the fact that this only works for the U.S.) Suppose one fine day, somebody tries to enter in a record about a new person--but some typo has happened somewhere, and the new person's SSN clashes with somebody's SSN who is already in the system. Sure, the database will notify you that something went wrong. But how do you know which social security number is correct, and which is incorrect? You will have to find a set of fields which uniquely identifies each person ANYWAYS. I.e. you'll have to differentiate them by name, birthday, place of birth, etc etc. Now even worse!!! What if somebody attempts to add the same person TWICE to your database, but mistypes their social security number. Now your database can't even tell you that something has gone wrong. It will happily record duplicate or contradictory information about the same person--and in order to resolve the mess, again, you have to find out what uniquely identifies the people ANYWAYS. Now, even worser than worse--what if you have taken the advice of this article, and you haven't bothered to identify a set of fields which are genuinely unique to each person. You just have a social security number and a name. How are you going to even going to correct the fact that John Smith is in your system twice, when you have ten John Smiths? How can you possibly tell which two John Smiths are the same John Smith? Yeah, it take some time and careful thinking to properly come up with natural keys for your entities. But unless you do, you haven't actually specified your entities at all. This is one area where long years of experience really pay off. Expert data modelers spend years and decades honing their craft, observing the work of others, etc etc. Eventually they acquire the wisdom needed to be able to know what kind of information is really needed to nail down what kind of entity the database needs to know about.
- yuriks 2y ago> Suppose you take the advice of this article, and use, say, social security numbers to identify people. You seen to have misunderstood the point of the article: the author is recommending NOT using the SSN (a natural key) for primary keys, and instead to use an artificial, automatically generated key, so that the SSN is decoupled from the record and can potentially be updated.
- iblaine 2y agoIt’s funny that indexing used to be unavailable when processing big data on Hadoop. Now that we have options to do so we now care. As tech evolves we adapt.
- wavemode 2y agoAnother massive annoyance with natural keys - privacy. If your table's primary key contains personal information, that PII now infects every other table that holds a foreign key to that table.
- hehdhdjehehegwv 2y agoExtremely good point.
- whyever 2y agoAFAIK, even using a UUID is still considered PII if it uniquely identifies the user.
- gregw2 2y agoThank you for your comment. It spurred me to think about this issue more thoroughly in a way I hadn't, even though this comment may disagree -- to some extent -- with your perspective. I have not seen clear guidelines about whether an organization's surrogate keys for persons are considered PII. (And this ambiguity has frustrated me for some time as I am unclear whether to take an aggressive or conservative view on labelling PII where I work.) When I have read the guidelines, it seems ambiguous but on balance I think it disagrees with your implied claim (that PII is not present in a table that uses a surrogate foreign key to a person/user.) The definition of PII per NIST https://nvlpubs.nist.gov/nistpubs/legacy/sp/nistspecialpublication800-122.pdf https://nvlpubs.nist.gov/nistpubs/legacy/sp/nistspecialpubli... is: "Any information about an individual maintained by an agency, including (1) any information that can be used to distinguish or trace an individual‘s identity, such as name, social security number, date and place of birth, mother‘s maiden name, or biometric records; and (2) any other information that is linked or linkable to an individual, such as medical, educational, financial, and employment information." A surrogate key associated with a person is arguably "any other information that is linked or linkable to an individual" meaning that all tables containing the surrogate key remain PII and remain "infected". It's true the surrogate key only allows linkability within the context of the data ecosystem in which it resides, but such distinctions (of "internal" to the system vs "external from the system") are not made in the language of the definition. Additionally, from a pure risk and PII disclosure impact, usually the whole database gets dumped, not just the "non-person" tables. If you have financial/medical transactions in table "A" and a personal numeric ID linking data to a person table "B", both tables contain PII, right? From a privacy standpoint, if you can SQL JOIN the data to trace the person involved either within your system or even data reasonably obtainable outside your system, (or if an attacker can), it's PII. Intranet IP addresses are called "linked PII" in section 3.2.2 of the above NIST guidelines for example, and NIST does define some related terms that would seem to apply to surrogate keys like: * Distinguishable Information: Information that can be used to identify an individual. * Linkable Information: Information about or related to an individual for which there is a possibility of logical association with other information about the individual. * Linked Information: Information about or related to an individual that is logically associated with other information about the individual. As a data engineer, the above interpretation means PII is in a zillion tables and labeling a table with a boolean indicator yes/no isn't that helpful. But as a policy person, NIST seems to be recommending gauging PII more at the system (not table) level and with a PII Confidentiality Impact Level of low/medium/high that takes into account the context and overall risk and that seems sensible. From a data cataloging standpoint (gauging what's "infected" to use your term), I think it's probably helpful to identify particular transactional tables or personal table as having "high" PII disclosure impact vs "low"; the presence of "infection" from a virus ("PII") is mostly irrelevant in a sufficiently large system where viruses/some PII is inevitable but what matters is the severity/impact. A zillion tables will be "low" (e.g. if most tables have audit column saying which user last changed a particular record) but certain transactional or user tables may be "high" and should be recognized as such and the focus of any risk discussions with the business or legal or breach notifications to customers or what have you. Going back to your original point, I don't think the choice of natural vs surrogate key impacts the PII risk of the system or even its individual tables. I would slightly concede that a surrogate key (which in general I am in favor of) would make it easier to reduce a particular individual's PII from a system by concentrating it in one or a small number of tables with names, etc. which might be helpful for enabling GDPR right to be forgotten or something. But the degree of that PII elimination from a system by blanking out or archiving a particular user/person record is not necessarily reducing the PII for them to 0 at least definitionally unless the transactional records themselves are also removed as the AOL 2006 search data scandal demonstrated (where a woman identified solely by a surrogate key was able to be identified from her search term transactions alone.) (Legally there would appear to be carveouts around transactional deletion for some financial transaction records and backups, but IANAL...)
- wodenokoto 2y agoJust to add on to the CPR number. As mentioned it contains a birthdate and a gender identification as well as a checksum. Since many refugees don’t know (or can’t prove) their birthdate, they are given first of January. And enough first of Januaries are handed out that for some years there aren’t enough valid numbers, so numbers that fail the checksum are handed out too.
- knallfrosch 2y agoShows how the assumption that everyone has, or reveals, a known or at least approximate birth date cascades through the system.
- deleted 2y ago[deleted]
- ccppurcell 2y agoHere in Czech republic, everyone has an id number (rodné číslo) but as a foreigner, I have multiple id numbers. Further complicating matters, I was given a rč with the wrong gender (the gender is encoded in the number) and my nationality was at some point incorrectly listed as "Ireland". I do wonder if the Czech government thinks I am just two completely different people.
- Culonavirus 2y agoseems like a problem, you should get that czeched out
- jpnc 2y agoThe title could probably be extended to 'You'll regret using natural keys as primary keys' and it would be right in some ways. Personally, I've come to the conclusion that it's probably best to use both surrogate and natural keys. Surrogates (IDENTITYs, UUIDs what-have-you) as PK from a technical perspective and natural key as 'PK' from a business/data modeling perspective.
- __MatrixMan__ 2y agoNatural keys are often the best you can do if you're trying to be partition tolerant. With surrogate keys you end up with duplicate entities whenever a partition heals (supposing the entity appeared to both partitions while they were still separate).
- karmakaze 2y agoA simpler and more compelling reason in my experience is that relating tables is so much easier with synthetic keys. You can decide how large your synthetic keys are based on uniqueness requirements, natural keys have their own size and format that would then be copied to make relations. And if uniqueness depends on several values to be unique then dealing with compound foreign keys isn't fun or efficient. At this point, there would have to be a very compelling reason to want to use natural keys to offset this and I've rarely seen them. A case that may come up is for a miscellaneous table to use the synthetic key from another table with another natural value that uniquely identifies a row. I wouldn't choose this route if you ever think you want to relate anything to rows in this table, they should be leaves related only to the synthetic key being used.
- kijin 2y ago"Natural keys" just means that someone else can change them under your feet. People change names, their phone numbers, passport numbers. Governments change their numbering schemes all the time. Corporations merge and split. Heck, even governments merge and split, surprisingly often, at the municipal level. All without your knowledge or consent. And you're left wondering why you suddenly have duplicate key errors in your database. The only key that you can trust is one that you, and only you, control. That's the point of the surrogate key. Whether it's BIGINT or UUID v7 is beside the point.
- lorddr 2y ago[dead]
- tedunangst 2y agoEvery blog post about database schema should use as its example the most obvious example, a blog. People are pretty familiar with the domain, and it even has many of the same challenges. You probably have a postid field in there somewhere, but lots of blog software also uses url as a natural key. Which is why sometimes the old title appears in the url. Can't change that. Except you can. Some software will do redirects, by creating stub records for old URLs that map to new ones. So that's not an intractable problem. Etc.
- MikePlacid 2y ago> I already predicted that changing the number would prove to be either impossible, or have all sorts of cascading effects, ultimately terminating in official records no longer recognizing that the car is mine. Oh my, I can feel that pain. Here’s what happened last week, caused by a change in… SSN? no - in our home address. My wife earned some unemployment benefits three years ago, which were put on a plastic card issued by The Bank. She finally found time to access the funds (she’s a middle school teacher), but when she went to The Bank, they said they didn’t have the money anymore—they’d sent it back to California. So, she called California. They were like, “No problem, we’ll send you a check. Oh, you have a new address? Let’s change it. Wait, what is happening… oh, now your account is locked, it says: potential fraud”… They needed a supervisor to unlock it, which took 20 minutes. The supervisor unlocked the account, but because it was marked as potential fraud, they couldn’t mail a check anymore. Instead, they linked the account back to The Bank (20 more minutes), and she had to go there in person with her ID to get it checked. So, she went to The Bank. But you can’t just walk in The Bank and show your ID to get it checked; you need an appointment. And to get an appointment, you need an account with The Bank. But her California benefits account? Oh, it is marked as potential fraud - it didn’t count. So, they spent 20 minutes to open a new account for her. She got an appointment for later that day, in 4 hours, went back to The Bank, and had her ID checked (yes, the second time in one day - they need to check your ID to open an account too). Did she get the money then? Of course not—the account is still flagged as potential fraud. No cash possible. Call California again. California agreed to mail the check to the new address, probably by some oversight. So my question is: do these “potential fraud” flags in databases ever die a natural death? With some hope, sincerely, a Husband of a Potential Fraudster.
- knallfrosch 2y ago> Mailing a check to transfer money Real 1800 vibes here.
- the_gipsy 2y agoIn Spain the ID numbers are assigned at birth, carry no information, and cannot be changed. Each police comissary that registers birth gets a unique set of IDs to assign (per year or whatever). However. Mistakes still happen. A colleague had the same ID as someone else. He said he tried to change it, but it was impossible because it was such an impossible concept to any public servant involved. In the end he gave up and just lived with the fact.
- fcatalan 2y agoNot really assigned at birth, but at the time you first request the card. It's a very very very crappy database ID number: -There's a significant amount of duplicates, enough that if your database is big enough you will find one sooner or later. -Format is variable: There are older 7 digit numbers, modern 8 digit ones, some people pad the 7-digit with a 0, some don't, some consider the CRC letter part of the key, some don't, some just append the letter, some hyphen it. If you deal with manual data entry anywhere, you will suffer. -Foreign people exist: You start using their passport number, the format of your key is now completely arbitrary, can't validate it. If you were using 8 digit NIFs as keys now some countries use 8 digit passports, increasing your chances of duplicates. -Foreign people stay: They get a resident card and they use that. Format is almost like NIF but not really so you need to account for that. Someone you registered initially with a passport now has the card and registers for something else with it. Have fun cleaning their duplicated identity from the whole system. -Foreign people become Spanish: Now they get a NIF. If you have dealt with them for a long enough time, have fun again fixing their records for the second time.
- benhurmarcel 2y agoForeigners change from a NIE to a new DNI if they get Spanish nationality. So their ID number changes then.
- shkkmo 2y agoEven if this is true now (which it isn't entirely), that is no guarantee that it will be true in the future. Any time you use an external value as a key, you run the risk of a policy change. An internally generated artificial key is the only safe way to ensure immutability and lack of semantic content.
- EVa5I7bHFq9mnYK 2y agoHow is name and city a key for a restaurant? There are a dozen of mcdonalds in my city.
- layman51 2y agoThat combination of columns being a candidate key depends on the world or domain that your dataset is modeling. In this example, I think it was a list of the top 50 restaurants in the world and so it would make a fine key. But if that database was ever expanded then definitely you would run into collision issues.
- perlgeek 2y agoNatural keys have a tendency to change over time in what is considered unique. For example, you have a company, and every employee has a unique employee number generated by HR... until the company merges with another, that also has unique employee numbers, and suddenly the identifier becomes the tuple (organization, employee number) that becomes unique. If you've used the employee number as a foreign key in other tables, you have to change those too. This is a somewhat contrived example, but I've had enough real, annoying examples happen to me in my career that I avoid natural keys.
- deleted 2y ago[deleted]
- knallfrosch 2y agoAh, how I remember changing my name. People think it only happens now and only to 0.1% of people who decide to change their gender in a leftist Western society, but somehow they forget that people have been changing family names for centuries. And of course, my work email address also changed. Now THAT is fun.
- raverbashing 2y ago> Many were the times, earlier in my career, when I decided to use a 'natural key' as a key in my own database. As far as I recall, I've regretted it every single time. Correct. Infamously correct Every "natural key" will have some way of fumbling things down the line. Nothing is unique, not even joining your natural key with other deduplication info. Just save yourself the trouble.
- DeathArrow 2y agoSynthetic key is the easiest choice. Because the primary key in one table is going to be foreign key in another table. If I have an employee table I can use their email address as the key but then I will have to use the email in any other table that references employee table, such as salary table, days off table and so on. Most of the times using an auto incrementing integer or UUID is just fine. In NOSQL there's usually a synthetic ID used to uniquely identify a document, I've never seen people using natural keys or compound keys.
- webprofusion 2y agoAs a junior dev decades ago I was once asked to change a perfectly good database (with integer IDs) to use natural keys, so to look up (or SQL join to) a particular office for instance your query would filter on Country, City and Street with the expectation that you get one result. This was the dumbest technical decision I have ever been asked to be a part of.
- gpvos 2y agoIn the example given, the most logical natural key to me would be (year, rank). Why otherwise would they have the year in there?
- mathgorges 2y agoThat was my first thought too. But like TFA says, that still wouldn't prtect you from clerical errors.
- moi2388 2y agoI’ve just always considered natural keys public keys, and added a surrogate key as private key. From the outside you won’t see it, but internally it saves a lot of headaches. Space, speed, migrations, I have just never seen an actual downside to using a surrogate. And if you are absolutely sure, add some unique constraints. Easier to change when you inevitably have to. Mainly; I’ve learned that my initial assumptions are never correct.
- samtheprogram 2y agoI technically use a natural key, if you could call it that, when using Supabase. Having a `public.users` table with a primary key that’s the same as the key for the internal user auth table (from Supabase). Arguably not a natural key, or at least a contrived example, but: any downsides?
- mobiuscog 2y agoIt's also important to put as much thought into any synthetic key. It's easy to just use an auto-generated sequence... but then you start having to export/import or otherwise merge data, and other manipulations that often use the primary key and find there are collisions everywhere. There can also be problems when needing to support multiple databases, or update versions. UUIDs (or equivalent synthetic keys that are independent of the database itself) are often the best answer for this reason. Having been bitten by sequences so many times in the past, I find them to often be more trouble than natural keys in the first place - just a lazy approach.
- jonathan-re 2y agoWell, UUIDs bring their own challenges. Dropping that one here: Be Careful with UUID or GUID as Primary Keys https://news.ycombinator.com/item?id=14523523 https://news.ycombinator.com/item?id=14523523
- globular-toast 2y agoIt's pretty easy to migrate to UUIDs later if required, as long as you don't leak your keys. My work made their own uuid-like scheme, similar to UUIDv1 which incorporates several elements like machine ID and timestamp. The mistake was twofold: first they exposed them (so people started using them) but, worse, they made them easily reversible, so people started decoding the information in them. People would naturally see one and think "oh this is a record from place X". Of course that might not be true following subsequent data corrections, but the key can't change.
- akvadrako 2y agoUUIDs are only good when you don't care about ergonomics or performance. Snowflake IDs are much more reasonable. 41 bits timestamp (ms) + 10 bit machine id + 12 bit serial. But if you care about ergonomics and privacy the most then short and random IDs are really the best.
- otikik 2y agoOne of my first jobs involved an account management software which created an ID for each user based on first name and surname. So John Smith would get SMIJOH. It was the user natural key and it could not be changed once created. One woman got married and changed her name; se became really upset when she found out that we couldn’t change her login.
- canary-hill 2y agoWhat has worked best for us is to have a sequential integer as the primary key as well as UUIDv4 as a surrogate key for every row. The applications would expose the objects only using uuid and never the primary key. The primary key always remain internal at the database level and never gets referenced or used at the application layer. In this way the security and privacy of objects are maintained and having sequential integral primary key for the object ensures the database design remains robust. Essentially, separating out the key for the database and the application. Is there any potential issue with this approach?
- sigtstp 2y agoIf anyone works in bioinformatics, please, please, for the love of god, generate your own unique IDs. Database identifiers are not generally unique (the same id might get reused for e.g. protein variants). Even sequences are problematic: I've found the same seq with different ids (can't remember the db now), and they can change (sequencing or human error might've occured and they get updated).
- teaearlgraycold 2y agoWhat I’m doing right now is putting synthetic (auto incremented) ids on every table, but also adding lots of compound unique indices. We have a lot of long background processes that might need to be restarted in the middle. The natural key definition allows for easy idempotency in subsequent runs (the jobs only upsert with. collisions handled by a simple timestamp update). Most entities are tied to a version record (more or less a God object). But some span across versions. I’m able to have the entities that aren’t tied to a version float around because they reference the natural key of the versioned entities, minus the version’s synthetic key.
- strnisa 2y agoI've become a fan of unique, relatively short and "human-readable" IDs, such at the ones used by Stripe, e.g. `cus_MJA953cFzEuO1z` for an ID of a customer. Here's a Stripe dev article on the topic: https://dev.to/stripe/designing-apis-for-humans-object-ids-3o5a https://dev.to/stripe/designing-apis-for-humans-object-ids-3... If you use JavaScript/TypeScript, you can make them like this: function makeSlug(length: number): string { const validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"; const randomBytes = crypto.randomBytes(length); let result = ""; for (let i = 0; i < length; i++) { result += validChars[randomBytes[i] % validChars.length]; } return result; } function makeId(tableName: string): string { const idSpec = TABLE_NAME_TO_ID_SPEC[tableName]; const prefix = idSpec.prefix; const slugLength = idSpec.length - prefix.length - 1; return `${prefix}_${makeSlug(slugLength)}`; }
- metafunctor 2y agoType safe and unguessable IDs are what I've been using in my projects for the past, oh, 10 years maybe? Inspired by Stripe! In my databases, I often prefer integer primary keys for performance reasons. On the other hand, I don't want to expose my primary keys because they are easy to guess. Recently I've been playing with Rust, and ended up publishing a library to encrypt IDs in they way I like: https://crates.io/crates/cryptid-rs https://crates.io/crates/cryptid-rs
- vanderZwan 2y agoNice! For the record: the valid chars string is 62 characters, so naively using a modulo on a random byte will technically introduce a bias (since dividing 256 values by 62 leaves a remainder). I don't expect it to really matter here, but since you're putting in the effort of using crypto.randomBytes I figured you might appreciate the nitpick ;). Melissa E. O'Neill has a nice article explaining what the problem is, and includes a large number of ways to remove the bias as well: https://www.pcg-random.org/posts/bounded-rands.html https://www.pcg-random.org/posts/bounded-rands.html (in this case adding two more characters to the validChars string would be the easiest and most efficient fix, but I'm not sure if that is a possibility here)
- mkleczek 2y agoThe question is: if you have two restaurants having different scores but distinguishable only by their surrogate keys, how do you know which one to go to? In other words - using surrogate key is an attempt (and the wrong one!) to fix the problem of missing important information in the database.
- big_whack 2y agoIf you use a surrogate key, you still need a unique constraint in the table (probably the same columns you would otherwise call your natural PK). If your unique constraint isn't sufficient to capture the difference you mention, you need to add more columns. However, that's strictly better than the natural PK situation, where you would need to not only add new columns to the key, but also add those columns to all referencing tables.
- mkleczek 2y ago> However, that's strictly better than the natural PK situation, where you would need to not only add new columns to the key, but also add those columns to all referencing tables. Foreign keys referencing surrogate key has different semantics than fk referencing natural key - it is a can of worms actually and can lead to unexpected anomalies. Lets take the example from the article (with surrogate key): Restaurant(id, name, city) Now let's add a possibility to record visits: Visit(restaurant_id references Restaurant(id), user, date) We have a procedure to register visits to a restaurant: register_visit(restaurant_name, user_name, date_of_visit) { INSERT INTO visit SELECT id, user_name, date_of_visit FROM restaurant WHERE name = restaurant_name } I very much enjoy spending time in "Polish Kielbasa" restaurant in Warsaw and I visit it everyday - I don't visit any other restaurant at all. Now changes of a restaurant name will lead to the database containing misinformation: register_visit('Polish Kielbasa', 'mkleczek', 2024-6-4); update restaurant set name = 'Old Kielbasa' where name = 'Polish Kielbasa' and city = 'Warsaw'; insert into restaurant ('Polish Kielbasa', 'Warsaw'); register_visit('Polish Kielbasa', 'mkleczek', 2024-6-4); Question: what restaurants did I visit this year? This kind of anomalies are avoided using _natural_ keys and - first of all - defining proper _predicate_ for _each_ relation. The predicate of relation visit(restaurant_name, city, user, date) is quite obvious: "User [user] visited restaurant [restaurant_name] in [city] on [date]" Question: What is the predicate of relation visit(restaurant_id, user, date)?
- begueradj 2y agoOne of the most interesting part to me is the fact he is a university professor in a good country but rides a used/second hand car... in an era where status and success are highlighted by displaying what one buys and owns on social media.
- vanderZwan 2y agoHighlighted by whom? University professors?
- mobiuscog 2y ago> in an era where status and success are highlighted by displaying what one buys and owns on social media This is no different from the people who used to show off their rolex in the pub, or park their BMW on display thinking it was impressive. The 'show' may have moved, but it's still the same people who gain the same amount of 'respect' that they ever did (not much). Besides, I would guess that most professors are not so worried about their car (and in fact many may prefer a more sustainable mode of transport) as they are to their research and citations, and the advancement of their students.
- lordnacho 2y agoCars in Denmark carry a huge tax, it's not unusual that you can only afford a used car. Plus public transport in the cities is pretty ok.
- rsynnott 2y ago> in an era where status and success are highlighted by displaying what one buys and owns on social media. I mean, not for everyone? These things, pretty much regardless of era, matter a lot to some people and not at all to others; for still others it may be a negative signal of sorts. Anecdotally, I know a lot of very well-off people; well under half would have fancy cars.
- fuzzfactor 2y agoThis is an educated person who shows higher-class tendencies by riding a bicycle, compared to those who seek mere automotive eminence. Even if the bicycle is older than the car, perhaps even more so.
- iso8859-1 2y agoHow it is possible for an article from 2024 to have a comment from 2009?
- jimbobthrowawy 2y agoI think it's a joke about natural keys, based on the date added to the comment: https://github.com/ploeh/ploeh.github.com/commit/6149bcdf0e2316f3036c4df1f6c1cbb5a667b0c6 https://github.com/ploeh/ploeh.github.com/commit/6149bcdf0e2... I think the date was chosen because that's when the blog originated after the author left microsoft.
- lysecret 2y agoAgain, every time I have seen natural keys in a big long lived system I have seen duplication. This is something you will have to deal with.
- mootothemax 2y agoYou can get performance benefits from using natural keys, as many databases store rows in the order of a table's primary key (sometimes called the clustered index, though it may or may not have a unique constraint requirement depending on the DBMS and a few other bits). In the author's example, if the first column in the natural index was the city name (or city ID!), and locations are often pulled from the database by city, you'll see a read time performance benefit because each cityName's restaurants will be stored together. This is why UUID-based systems can suffer worse read + write performance; their rows will be stored in the order of its UUIDs (that is, randomly spread around), making read, insert, and update performance lower. What to do? I favour a mixed approach: have a unique integer ID column used internally, expose a unique UUID to the public where necessary and - with a BIG DO NOT OPTIMIZE PREMATURELY warning - really think about how the data is going to be queried, updated, and inserted. Ask - does it makes sense to create a clustered index based on the table data? Is there enough data to make it worthwhile? Where can the index fail if changes need to be made? Under some circumstances, it might even make sense to use a natural key with the integer column included right at the end! The only hard rule I have is using UUIDs for clustered indexes. Unless the tables are teeny-tiny, the system is most likely suffering without anyone being aware of it.
- williamdclt 2y ago> many databases store rows in the order of a table's primary key (sometimes called the clustered index [...]) Note for readers: Postgres doesn't do that
- mootothemax 2y ago>> many databases store rows in the order of a table's primary key (sometimes called the clustered index [...]) >Note for readers: Postgres doesn't do that And I am climbing out of the rabbit hole that is Postgres' CLUSTER keyword :) Really funky, looks like it's a one-time operation that isn't persisted? Looks like Postgres stores its index entries in ascending order by default (NULLs at the end), and if so, the point's worth keeping in mind on that front too. I really need to do a deep dive on Postgres internals one of these days, it's an amazing - and different! - system its developers have created.
- usrbinbash 2y agoThe problem is: As soon as a synthetic key is used by an EXTERNAL system, it stops being synthetic; for all intents and purposes it is now an integral part of the record, because if you change it, the external system may not be able to find the record any more.
- kstenerud 2y agoIn 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 agoI 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 agoEvery 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!"
- zer0tonin 2y agoYes, except sometime whole industries come together already has developed a system of unique identifier, and I can just use this as a natural key. For example, the Nasdaq stock exchange is identified perfectly fine by the string 'XNAS', I'm not going to generate an ID on top of it.
- canterburry 2y agoI don't think I have seen natural keys used in a database since the 90s. While I too was taught natural keys, or combined keys, were the "intended" way to identify data, I was corrected very quickly that artificial primary keys were much more reliable and more convenient due to all the auto increment features etc. I am surprised this is even talked about anymore.
- jbverschoor 2y agoThe amount of inexperience is staggering
- deleted 2y ago[deleted]
- nuancebydefault 2y agoI'm a bit confused. I've never programmed databases, though i have a lot of programming experience. The student made a structure that can be seen as a unit or table in a database. Is the database not clever enough to synthesize a unique id? Why does the programmer need to care about keys? It is clear that the end user will not know anything about a key, it might be a byte offset in a file or a memory pointer. Why does the programmer need a key?
- alanhaha 2y ago~~You'll regret~~ I regretted
- contingencies 2y agoMy traditional schema was always to have id in every table and to make it an integer, then for pretty much anything it'd be two tables, nouns for the thing and a one-to-many relationship with noun_names for the names, where noun_names was comprised of id/noun/language/name, keyed on noun and language, with the language column FK'd to languages.id. I'd also have a languages.code column for ISO or IANA code (sometimes both columns or aliases), and a language_names table with language_used/language_named. So you could name English in Frisian, and Chinese in Tibetan. Haven't needed to build SQL in ages, though that schema works great. Certainly never had an issue with it once UTF-8 became standard. If you build schemas without i18n, you're asking for trouble. One good thing about using a standard integer key is the cost of indexes is low (low memory use). One good thing about using id everywhere is that it's short and self-explanatory to programmers from any culture. Lots of casual queries built of subqueries like... select * from noun_names where noun=(select id from nouns where ...) and language=(select id from language where code='en'); Always felt this was the most readable. Always felt that LEFT/RIGHT JOIN stuff was bonkers. Onboarded a lot of serious junior devs, never had an issue.
- praptak 2y agoModeling identity doesn't end with synthetic keys though. "Generate own keys" only solves the problem of identity ownership. You now own the identity but it doesn't mean your identities are as they should be. Say a customer used a different government-issued ID to re-register with your bank. A year down the line you notice that you have two identities for the same person. It might be a meh for an online game but if you are a bank this can make you run afoul of regulations. Can you handle the merge of all the relevant data? And merging is usually the easier of the two glitches - can you handle a split? The point is that identity just like security requires thought from the start of the design. For a domain where identity is really hairy (although admittedly with less consequences for screwing up) see https://news.ycombinator.com/item?id=4493959 https://news.ycombinator.com/item?id=4493959 "The music classifying nightmare". Also https://en.wikipedia.org/wiki/Identity_(philosophy)#Metaphysics_of_identity https://en.wikipedia.org/wiki/Identity_(philosophy)#Metaphys... for some philosophical perspective.
- meekaaku 2y agoI am firmly in the control your primary key, preferably uuid. Quick question for database gurus here. Is it ok to have a currency table using the currency code as primary key?
- brabel 2y agoI think long term, that is a bad idea. In Brazil in the 80's and early 90's, the currency changed name several times. It was called "cruzado", then "cruzeiro", then "cruzado novo" (yep, "new" <old-name>, very creative) and then I think it went back to just "cruzado" :D before finally becoming Real (which I believe was the name of the currency also during Monarchy, 100 years earlier).
- kassner 2y agoLikely no, there are currencies without an ISO 4217 code, i.e.: https://en.wikipedia.org/wiki/Faroese_kr%C3%B3na https://en.wikipedia.org/wiki/Faroese_kr%C3%B3na
- zarzavat 2y agoThis is interesting but it doesn’t really seem like a separate currency. It’s more like Scottish pound vs English pound, it’s the same currency but different banknotes for different regions in the same state.
- bjourne 2y agoHard disagree. You create tables for them to be queried by applications. For the Restaurants example the application clearly wasn't designed to handle Restaurant entries whose only field differs is rank. Consequently, you shouldn't allow data the application can't handle. Whenever the time comes and you think "ah, wouldn't it be great if multiple people could review the same restaurant in the same year?" you change the natural keys. You can always relax the rules. Tightening the rules is much harder because garbage data may already be present. Keys are about the integrity of your application(s) and preventing corner cases by making them impossible.
- konschubert 2y agoYou can use a UUID as a primary key and still enforce uniqueness on a combined index of city and name.
- nkozyra 2y agoIf they conflict and are user facing identifiers aren't you then forced to add uniqueness to the city and name, ala /chicago/chipotle_s4, potentially bleeding some business details? I don't love uuids as public identifiers for a number of reasons but not hinting details about your data is one nice thing about them.
- konschubert 2y agoHmm, I am not sure if I get you. The cool thing about enforcing uniqueness on a secondary index is that you can just change or remove the uniqueness constraint anytime without breaking foreign key relations.
- nkozyra 2y agoI'm not sure why you want a uniqueness index then in this case. If it's not publicly visible (effectively a secondary index / reference), then you can't use it as an identifier. This means you need something else (a slug, as discussed herein) or just use the ID (bad practice in general) or UUID (bad for humans). If you use - in this example - city and company name - you still have to enforce uniqueness, so you have a pseudo-slug anyway.
- fiatjaf 2y agoSuch a long article, no convincing examples. Why did he waste so long on the stupid restaurant case that clearly makes no sense? Natural keys are good as long as you think one minute before deciding to use them.
- bdcravens 2y agoAnother surprising type of data that people think are unique are tracking numbers. Some carriers (like UPS) do recycle them.
- shireboy 2y agoIn the example from the article I think the table should be named RestaurantRank(year,rank,restaurantid), with a natural key year,rank. It would have a fk to Restaurant(Id,name,etc). Choose a natural key if there is some natural unique field or combination. In this case unless restaurants can tie in their ranking year,rank can be unique
- noisy_boy 2y agoYou'll have to modify the natural key if later it is decided to add rankCategory ("best overall", "best ambience", "best value" etc) via rankCategoryId foreign key to the RestaurantRank table because a given restaurant can have the same rank in the same year under multiple categories. A synthetic key avoids you having to mess with the key to handle such cases.
- tigerBL00D 2y agoThis is a bit contrived to illustrate a problematic case. Every business has a unique ID that's assigned to it before it can operate. EIN or even a state id together with state would be a fine natural key for most situations.
- d--b 2y ago> what if we had a restaurant named China Wok in Springfield, USA? Hardly unique. Add the state, you say? Probably still not unique. Ok sure, but then you have 2 restaurants which are indistinguishable from one another in your database. It doesn't matter that the thing has a unique id next to it. You can't know which is which. That's not useful.
- sashokbg 2y agoI disagree with this article. The author takes some very edgy cases such as "undergraduate student" and "transgender CRP number". Even the example for the restaurant and "time based id number" are bad because they all indicate that you just badly identified the entities (in DDD terms) and their identity. So being bad at DDD doesn't mean that you can't use natural keys (although I myself have arguments against them)
- nostrademons 2y agoThere'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 agoIt'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.
- stiiv 2y agoThere is still something to be said for "real world uniqueness" (GIS coordinates) or deferring to a third party to establish identity (license plate numbers). Identifiers like these aren't always available, but within many domains will be sufficient. The idea here is not that these keys can't be somehow "invalid," but rather that it isn't our system's problem -- it belongs to some other authority.
- jonstewart 2y agoTFA author cites many examples from human-oriented systems, which perhaps are more common and traditional domains for RDBMS design, where there often several layers of exceptions to the rules - his argument that synthetic keys work well for these domains lands. I personally work far more with computer-oriented systems and their data, and natural keys work well for me. When well-chosen they allow me to do an initial load of the source data for analysis, and then aggregate such databases together later on for historical analysis without fear of conflict. The data are often immutable in these domains, too.
- hateful 2y agoAfter reading many threads here, I think the final ruling may be: - If your record represents a physical being or object: Use a surrogate key. People change. Unrelated - if you have a list of emails or SSNs or license plates or VINs - we can think of these as foreign keys to a database we don't control.
- fl0ki 2y agoI inherited a project where a dozen different entity kinds were all given UUIDv4s. Users mostly see namespaced strings, but actually searching by those strings is janky and unreliable for other reasons (including the fact that they can change), so whenever I'm asked to debug something I insist on reducing to UUIDs first so at least we're definitely looking at the same entities. I didn't like the UUIDs at first, but it ended up being an unexpected boon for generic code to use the same key type for different entity kinds. What was less of a boon was that the string identifiers also have to be unique, but can change at any time, and depend on three-level namespacing (yes really) so there's far more that has to be tracked and enforced. The names are very important for UI use cases, but can never be the way that records reference other records, because that would just make them much harder to change with confidence. The essay seems to assume you'll have either unique natural keys or unique synthetic keys, but having now worked on a project that does both at the same time for many entities, I think it's a third option worthy of its own analysis. My experience was negative but I can't deny that the end result ticks a lot of functional boxes.
- thatjoeoverthr 2y agoOne may notice this has something to do with mutability. If there isn’t a surrogate key, the record isn’t mutable. The database may let you change it, but the new record has a new identity. Mutability as a concept requires a common identity across time. Languages permitting mutability are using a pointer or reference as an implicit surrogate identity. A typical database can’t offer this, hence the need to put explicit surrogate keys into the schema. You cannot say “this changed” unless you can refer to both samples as a common “this”.
- bjourne 2y agoWhile that is true, for correctness appends should supplant updates. On HN, many users (like myself) have posted comments for over a decade. Suppose I changed my username from "bjourne" to "SpongeBob"... Should the comments I wrote in 2010 show up as having been authored by "SpongeBob" or "bjourne"? I'd strongly argue in favor of the latter since the former would constitute falsifying history. The "change" in username should be viewed as the creation of a new personae rather than a "change".
- theelous3 2y agoAnother counter example is when the thing you are recording literally _is_ the natural key. Phone numbers are a good example of this. There is no situation in which some data entry error will break a number. That is the creation of a new distinct (possibly) broken number. A phone number doesn't care about any externality in the world. It is what it is.
- banish-m4 2y agoUse db-generated UUID "id" primary keys. Add a bigserial "order" field to tables that require stable temporal ordering, and then either create a view to "order by" it or always add "order by" to stable-required statements.
- TexanFeller 2y agoOne downside is UUIDs take twice the space of a BIGINT which is minor on the surface, but makes a huge difference when doing lots of joins on that key. I used to see 3-4x difference in some queries even using postgres’ uuid type(not the string column of fools). Doubling the space used by your keys also means less of your indexes stay locked in RAM.
- dwheeler 2y agoI've found that using a simple unique integer id as the key usually works well. Integers are easy to explain and are simpler to enter than uuids. There are always ttade-offs, but if there are no other compelling issues, simplicity is best.
- branko_d 2y agoThe author seems to suggest that the choice is between natural and surrogate key. In fact, the choice is between natural and natural+surrogate key. - If you have a natural key, you have to enforce it, otherwise you risk data corruption. The question is do you also need a surrogate key? Sometimes you do, sometimes you don’t. - If you don’t have an obvious natural key, then your surrogate becomes meaningful. You have to use something to distinguish between two “equal but not identical” rows, so you end-up showing the surrogate in the UI etc. In other words, it is no longer “pure” surrogate.
- JackSlateur 2y agoHe is right: natural keys are difficult to handle I'd just say this is not so right: As it turned out, though, whoever made that piece of software knew what they were doing, because the mechanic just changed the chassis number, and that was that. Because, yes, everybody can just reprogram your car and change its VIN and stuff. The manufacturer software have protection against that. But nothing prevents you from using another software. At the end, you can directly program whatever you want into the car : change the VIN, change the odometer etc.
- duped 2y ago> At the end, you can directly program whatever you want into the car : change the VIN, change the odometer etc. And you can wind up with an undriveable car this way. Particularly the odometer if you roll it back and try and sell it, your state's DMV will probably reject the title transfer.
- JackSlateur 2y agoBah ? Got a new car, used it for 150k km Tweak the odometer and set it to 50k, sell the car How could the government know I cheated ? Especially for an old car which are not connected (so even the manufacturer cannot tell) (btw, this is used in thief networks to refurg stolen cars)
- duped 2y ago> How could the government know I cheated You would have to perjure yourself on your yearly registration, when you get emissions testing you would need the tech to perjure themselves, and if you go to any mechanic that reports the mileage to the state (which some do, depending on the state) you would need them to perjure themselves too. > Tweak the odometer and set it to 50k, sell the car If the most recent registration says 150k and then you tweak it to 50k and try and transfer the title, the state is going to reject the application for title transfer outright. You may be charged with odometer fraud.
- 2y ago
- mikl 2y agoIf you use foreign keys in PostgreSQL, it is actually possible to update natural keys with little trouble via the “ON UPDATE CASCADE” mechanism on such keys.
- DontchaKnowit 2y agoYep. My last company had unique id columns for every table in the DB. Everythibg worked flawlessly as far as associating data. My current company uses natural keys all over the place and its a total clusterfuck trying to associate data at times.
- hosh 2y agoThere's another gotcha I found out at my workplace -- for Postgres, tables without a primary key has trouble when you want to use logical replication. The times you might want to use logical replication includes using AWS's database migration service to shrink allocated storage.
- cynicalsecurity 2y ago> From following the course curriculum, it'd be natural, however, to define a key for the Restaurants table as the combination of restaurantName, cityName, and year. The assumption is that name and city uniquely identifies a restaurant. That's a strange assumption.
- teknopaul 2y agoDon't see the problem with ssn as I'd. People have two, get two rows in your database. Not the end of the world often. database it's a tool thus limitation is not an error. E.g. maybe trans peeps want to forget their old ID, maybe not. your own key has no chance of reflecting any reality in the world.
- hathawsh 2y agoThe numerous problems with SSNs were identified years ago. https://www.gao.gov/products/gao-05-1016t https://www.gao.gov/products/gao-05-1016t In plain text form: https://www.gao.gov/assets/a112177.html https://www.gao.gov/assets/a112177.html I remember when the school I went to changed from using SSNs for all student records to using no SSNs at all. They had to notify everyone about the changes, repeatedly. It was obviously very expensive. Don't use SSNs as keys.
- deleted 2y ago[deleted]
- khalidx 2y agoYeah, this works fine unless you’re using something like DynamoDB or any system which requires you to have knowledge of the key. For relational setups this is the way to go though. I prefer the combo approach though - autoincrementing numbers plus a UUID in another column.
- deepsun 2y agoJust use both natural and surrogate keys at the same time.
- slamus 2y agoMade this mistake on a few collections on my app. Still regretting to this day.