Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

You'll regret using natural keys

blog.ploeh.dk

Post reply on HN
Print view View original

491–500 of 568 posts

Re: You'll regret using natural keys

#491

Post by mkleczek »

big_whack wrote: post #229
mkleczek wrote: post #152

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

If 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 refe…

> 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)?

Re: You'll regret using natural keys

#492

Post by hathawsh »

teknopaul wrote: post #488

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

The numerous problems with SSNs were identified years ago.

https://www.gao.gov/products/gao-05-1016t

In plain text form:

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.

Re: You'll regret using natural keys

#493

Post by Tangurena2 »

madcaptenor wrote: post #475
ceejayoz wrote: post #317

Earlier quoted context omitted.

I like to nix vowels and things that look like them, i.e. 0, to avoid random b00bs sort of tokens.

Sure, but I can see people still getting offended if "fck" showed up.

This is why an open source project is now "CK Editor". It was the author's initials, but too many people saw an extra vowel in the name of the project.

https://en.wikipedia.org/wiki/CKEditor

Re: You'll regret using natural keys

#494

Post by bjourne »

konschubert wrote: post #486
bjourne wrote: post #289

Earlier quoted context omitted.

But what's the point? You're complicating the data for no apparent gain. Only terrible RDBMSes which don't support multi-column keys require surrogate keys.

Because you can remove or change the uniqueness constraint without having to worry about foreign key relationships.

Eh, that's a feature of natural keys. If you try to fuck up your model your RDBMS will complain.

Re: You'll regret using natural keys

#495

Post by michaelt »

cimnine wrote: post #367
strnisa wrote: post #151

I'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-3... If you use JavaScript/TypeScript, you can make them like this: function makeSlug(length: number): string { const validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcde…

Yes, and I like to combine two established concepts instead of rolling my own: URI and UUIDv7. So my IDs become `uri:customer_shortname:product_or_project_name:entity_type:uuid`. An example ID could be `uri:cust:super_duper_erp:invoice:018fe87b-b1fc-7b6f-a09c-74b9ef7f4196`. It's even possible to cascade such IDs, for example: `uri:cust:super_duper_erp:invoice:018fe87b-b1fc-7b6f-a09c-74b9ef7f4196:line_item:018fe882-43…

> It's even possible to cascade such IDs, for example: `uri:cust:super_duper_erp:invoice:018fe87b-b1fc-7b6f-a09c-74b9ef7f4196:line_item:018fe882-43b2-77bb-8050-a1139303bb65`.

Let me guess - you're a Java developer, right?

Re: You'll regret using natural keys

#496

Post by codeulike »

datavirtue wrote: post #388
codeulike wrote: post #184

Earlier quoted context omitted.

Except the Stripe ones are case sensitive which can be annoying with some databases

You know what's annoying? People storing GUIDs in a case-insensitive database.

Classic RFC9562 GUIDs were often represented as hex like {550e8400-e29b-41d4-a716-446655440000} and so were case insensitive

Re: You'll regret using natural keys

#497

Post by Tangurena2 »

mikedelfino wrote: post #268
Kon-Peki wrote: post #30

> 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 ne…

How to uniquely identify an American citizen?

You cannot.

I used to work for one state's Department of Motor Vehicles.

Part of why REAL ID is such a charley foxtrot is because there exists no such identifier and since every state counts approximately as a sort-of-almost sovereign nation (this is basically what the 10th Amendment guarantees), there is and can be no possible nationwide citizen identifier.

There are about 3500 different counties in the US. All of which may or may not issue their own birth certificates. States are supposed to do that now.

Re: You'll regret using natural keys

#498

Post by Tangurena2 »

cmiles74 wrote: post #335
selcuka wrote: post #54

Earlier quoted context omitted.

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

People also type them in wrong, so there's another thing: where is the data coming from.

> The most misused SSN of all time was (078-05-1120). In 1938, wallet manufacturer the E. H. Ferree company in Lockport, New York decided to promote its product by showing how a Social Security card would fit into its wallets. A sample card, used for display purposes, was inserted in each wallet. Company Vice President and Treasurer Douglas Patterson thought it would be a clever idea to use the actual SSN of his secretary, Mrs. Hilda Schrader Whitcher.

> The wallet was sold by Woolworth stores and other department stores all over the country. Even though the card was only half the size of a real card, was printed all in red, and had the word "specimen" written across the face, many purchasers of the wallet adopted the SSN as their own. In the peak year of 1943, 5,755 people were using Hilda's number. SSA acted to eliminate the problem by voiding the number and publicizing that it was incorrect to use it. (Mrs. Whitcher was given a new number.) However, the number continued to be used for many years. In all, over 40,000 people reported this as their SSN. As late as 1977, 12 people were found to still be using the SSN "issued by Woolworth."

https://www.ssa.gov/history/ssn/misused.html

As late as the 1970s, the first 3 digits of your SSN told what office issued your number to you. And the next 2 digits told workers at that office what filing cabinet held your application.

https://www.ssa.gov/policy/docs/ssb/v45n11/v45n11p29.pdf

Re: You'll regret using natural keys

#499

Post by hathawsh »

randomdata wrote: post #438
gregw2 wrote: post #264

Earlier quoted context omitted.

Thanks for the reply. I will concede or defer to you in regards to PHI and HIPAA... it seems the philosophy behind HIPAA/PHI is very different than PII or GDPR. HIPPA is prescriptive. PII/GDPR are principle-based. HIPAA, it seems, has some text that it's not PHI if the risk is "very small" based on the opinion of someone with statistical expertise documents that it could be de-identified OR if the person avoids an ex…

> SSN ... which are definitely not surrogate keys. Surely SSN is a surrogate key? They are not naturally derived. The early ones were serial (i.e. an auto-incrementing field) and more recent ones are randomly generated (i.e. a UUID).

Conceptually, any information created or consumed outside your organization is not valid as part of a surrogate key, so SSNs are not a surrogate key. Furthermore, any time you reveal the primary key, that key can become the thing that people come to depend upon to find that database row, which leads to the possibility that someday, someone will have an important need for some primary keys to change, even if the primary key was supposed to be a surrogate key. The longer a database lives, the more likely that surrogate keys morph into natural keys. That's what happened to SSNs.

Re: You'll regret using natural keys

#500

Post by bjourne »

zarzavat wrote: post #341
bjourne wrote: post #275

Earlier quoted context omitted.

I think you are confused about the terminology because a primary key is a uniqueness constraint. They can be changed in any RDBMS worth its salt. Keys should not be random and your URL example is a case in point. The url /germany/berlin/2023/mcdonalds contains no surrogate key and is immensely more useful than the url /review?uuid=1kjksdhh3244ygdvvgdd2345.

A primary key implies a uniqueness constraint. But you can have a uniqueness constraint without a primary key. You can change primary keys within a database but you can’t change them outside the database. A database is a map but keys are part of the territory. The second URL with the UUID is more secure and is preferred in many situations where information leakage is a concern. The first URL is more descriptive but m…

A key is any set of unique columns. The primary in primary key just hints to the RDBMS how to store the data. Both URLs encode queries on the data. The first says "give me the 2023 review(s) of McDonald's in Berlin" (i.e., it has semantics) and the second "give me the row with uuid 1kjksdhh3244ygdvvgdd2345". I don't think the second URL is more robust because, for example, if the row has to be recreated it will fail to work.
Post reply on HN