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

151–160 of 568 posts

Re: You'll regret using natural keys

#151

Post by strnisa »

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 = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
        const randomBytes = crypto.randomBytes(length);
        let result = "";
        for (let i = 0; i

Re: You'll regret using natural keys

#152

Post by mkleczek »

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.

Re: You'll regret using natural keys

#153

Post by zer00eyz »

globular-toast wrote: post #124
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…

How do you deal with the Ship of Theseus/Trigger's broom? There's literally nothing that defines said object apart from its history .

Your database starts to look like git...

Think about a jet engine. Let's say you figure out that a part is defective and will cause a failure. You want to identify every plane that has one of those parts in it. What if you find that some of them had a bad oil pump that shortened the life of some bearings. You want to know every engine that had one of those pumps, so you can replace other parts.

I dont know if they do this with jets but there are quite a few places that take thee concept much further than this.

Re: You'll regret using natural keys

#154

Post by unchar1 »

mkleczek wrote: post #149
crabmusket wrote: post #40

Earlier quoted context omitted.

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…

> 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! But the DBMS already maintains a row identifier (called rowid or ctid or whatever depending on the DBMS). Why do you need an explicit one?

It may be useful if you have data that originates from another source or if something outside of our system references your entity. In that case you need to keep some form of an externalRef, so it's usually easier to just use an id that you can control, for referencing both internally and externally.

Re: You'll regret using natural keys

#155

Post by winternewt »

delusional wrote: post #122
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…

We have the same problem in Denmark, most people just don't realize it. At my dayjob we get at least one person every year who changes gender and consequently gets a new SSN (the final digit is supposed to signify gender). Most people don't store SSNs so they never realize, but it does happen fairly frequently.

If Denmark is anything like Sweden there's also:

- SSN from different ID space gets assigned to immigrants; when they become citizens they are assigned a new permanent SSN.

- SSN:s have a long and a short form; the short form which cuts off century information can be the same for someone who is 5 years old and someone who is 105 years old.

- When an unconscious patient comes in to the E.R. you don't know their SSN, so a temporary one is assigned for use in patient records. Such temporary SSN:s are not coordinated nation-wide so multiple patients may have the same SSN. In some hospitals they don't even have a local standard for ID:s. The staff just makes something up on the spot. It happens that the SSN they made up collides with a valid SSN for another person.

Re: You'll regret using natural keys

#156

Post by begueradj »

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

Re: You'll regret using natural keys

#157

Post by vanderZwan »

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…

Nice!

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

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

Re: You'll regret using natural keys

#158

Post by magicalhippo »

mkleczek wrote: post #149
crabmusket wrote: post #40

Earlier quoted context omitted.

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…

> 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! But the DBMS already maintains a row identifier (called rowid or ctid or whatever depending on the DBMS). Why do you need an explicit one?

In the DB we use[1] the internal row id is not stable:

The value returned by the function is not necessarily constant between queries as various operations performed on the database may result in changes to the row identifiers of a table.

So, users should refrain from using the ROWID function in ordinary situations; retrieval by primary key value should be used instead.

[1]: https://infocenter.sybase.com/help/index.jsp?topic=/com.syba...

Re: You'll regret using natural keys

#159

Post by vanderZwan »

begueradj wrote: post #156

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

Highlighted by whom? University professors?
Post reply on HN