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

51–60 of 568 posts

Re: You'll regret using natural keys

#51

Post by tadfisher »

jklowden wrote: post #27

You 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 actuall…

In that case, using this natural datum as an ID is a contradiction, because we are now removing the uniqueness constraint. It's fine to model the real world, but the real world also includes your data, and you may want to identify unique _records_ as it is not a universal truth that data can be corrected on entry.

Re: You'll regret using natural keys

#52

Post by parpfish »

Cyph0n wrote: post #33
parpfish wrote: post #24

There 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

Ideally, you should aim to sanitize/normalize strings on the write side rather than resanitizing on every read.

That 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

Re: You'll regret using natural keys

#53

Post by wodenokoto »

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

Re: You'll regret using natural keys

#54

Post by selcuka »

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…

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

Re: You'll regret using natural keys

#55

Post by stouset »

crabmusket wrote: post #40
jklowden wrote: post #27

You 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 actuall…

The 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 diffic…

Precisely. And if I have a surrogate key to identify “a car”, I get to define what makes a car unique for my purposes. Maybe that really is the VIN. Maybe today it’s the VIN, but tomorrow it’s whatever vehicle is currently being driven by its owner. Maybe it’s something else.

Some day I may need to track multiple VINs for a vehicle (maybe it’s got parts from multiple VINs and I want to track that). I can still always decompose that table and have an n-to-1 relationship between cars and VINs without migrating the rest of my data model.

Re: You'll regret using natural keys

#56

Post by ars »

selcuka wrote: post #54
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…

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

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

Re: You'll regret using natural keys

#57

Post by Kon-Peki »

selcuka wrote: post #54
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…

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

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

Re: You'll regret using natural keys

#58

Post by selcuka »

eddd-ddde wrote: post #47
bongodongobob wrote: post #15

Earlier quoted context omitted.

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

Why is knowing someone's ID an issue? They are meant for identification, if anything it should be a benefit that they are easily guessable.

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

Re: You'll regret using natural keys

#59

Post by dqv »

selcuka wrote: post #54
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…

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

> It’s not as uncommon as you might think. In fact, some 40 million SSNs are associated with multiple people, according to a 2010 study by ID Analytics.

https://www.pcworld.com/article/424392/a-tale-of-two-women-s...

Post reply on HN