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

541–550 of 568 posts

Re: You'll regret using natural keys

#541

Post by JackSlateur »

duped wrote: post #426
JackSlateur wrote: post #423

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

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

Bah ?

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)

Re: You'll regret using natural keys

#542

Post by Izkata »

mootothemax wrote: post #182
williamdclt wrote: post #173

Earlier quoted context omitted.

> 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

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

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

Correct. If you're in a situation that needs it, you'll need to run it at some interval. I have it running once a week on one of our databases.

Also, postgres uses this to solve a problem that clustered indexes on other databases don't solve - a clustered index isn't necessarily on an index you care about for querying. It still has to go back to the table data to get fields not in that index, which could have a random-access performance penalty if you're getting a lot of rows. Postgres's CLUSTER changes the order of the table data to match an index, which allows a sequential read on the disk, avoiding that performance penalty.

This is one of the situations where people complain the postgres query planner is doing the wrong thing and they want hints to tell it to use the index, but the query planner is actually protecting them from a huge random-disk-access performance penalty they don't realize they'd hit if they did that. The right thing to do is either use CLUSTER or, if they're on an SSD or the data can all fit in memory, change the random-access-penalty config value so the query planner no longer takes this into account.

Re: You'll regret using natural keys

#543

Post by ltbarcly3 »

aaronbrethorst wrote: post #403
ltbarcly3 wrote: post #338

Earlier quoted context omitted.

I don't think you read it carefully.

I did. I just hate encountering databases designed by people who take your advice to heart.

You hate encountering databases where people know how to do schema design, and don't just follow bad patterns by rote? What?

Re: You'll regret using natural keys

#544

Post by naasking »

praestigiare wrote: post #540
naasking wrote: post #539

Earlier quoted context omitted.

Sure, I agree. I'm not sure what point you're trying to make though.

Because - and _ break text selection in existing systems you do not have control over, if you use those characters your ids will become harder to select.

Someone already made this point, and once again, "break" is completely undefined. It is not at all impossible or even difficult to select text with - or _, so what's "broken" exactly? At worst it takes one extra step to extend the default selection. These are such weird objections.

Re: You'll regret using natural keys

#545

Post by duped »

JackSlateur wrote: post #541
duped wrote: post #426

Earlier quoted context omitted.

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

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

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

Re: You'll regret using natural keys

#546

Post by samspot »

nostrademons wrote: post #340

There'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 tha…

>> That's perfectly fine as a natural key; its definition is the first CPR number assigned.

I don't know about CPR's, but in the US SSN's get recycled. So two people can have the same "first SSN" assigned.

Re: You'll regret using natural keys

#547

Post by hathawsh »

randomdata wrote: post #523
hathawsh wrote: post #499

Earlier quoted context omitted.

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

> 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. SSNs are created within the organization. Maybe not within your organization, but nobody is talking about you. They are a surrogate key.

SSN = social security number. What else does SSN stand for?

Re: You'll regret using natural keys

#548

Post by sevenseacat »

ourmandave wrote: post #232
yashap wrote: post #97

Earlier quoted context omitted.

This is bad advice. Say I have a user table, and the email is unique and required, and we don’t let users update their email, and we don’t have user deletion. If I’m going natural PK, I make email the primary key. But … then we add the ability for users to update their email. But it should still be the same user! This is trivial if we have a surrogate primary key, a nightmare if we made email the natural primary key.…

If I’m going natural PK, I make email the primary key. Welcome to the Mr. Cooper mortgage provider website. Your logon is your email and you can't change it. If you used your cable provider email you're stuck with them for the life of your 30 year mortgage.

Then your cable provider shuts off its email service and you lose that email address. Ooooops

Re: You'll regret using natural keys

#549

Post by isaacremuant »

coooolbear wrote: post #522
vsuperpower2020 wrote: post #420

Earlier quoted context omitted.

I don't care what they want. A lot of people are tired of playing these language games.

Then “a lot of people” (lol) shouldn’t complain when they piss someone off when someone already warned them

What you're saying very well seems like a threat. A threat of violence for speech.

That attitude defaults to better at violence in a particular context gets to impose their will. Or whoever has the security forces to back them out of an inferiority situation.

You miss the part when I can arbitrarily warn you about a lot of things myself and then use any interpretation of rule breaking on your part to attack you.

I know it may sound harsh but this is where many end up going so let's make it explicit.

Re: You'll regret using natural keys

#550

Post by isaacremuant »

coooolbear wrote: post #416
isaacremuant wrote: post #311

Earlier quoted context omitted.

I'd say those articles show Wikipedia's political bias and a tendency to overly politically correct instead of portraying reality. Many in real life do refer to themselves as gitano or Gypsy and would ask for others to refer to them as such. Of course it's very easy to find an article saying otherwise and then using that as the end of discussion for Wikipedia editors.

By ‘find an article’ you mean find ~10 real citations including the resolution of an authority a long time ago and to tell the reader it is not clear or definitive? Better to be careful and let any individuals or communities tell you what they want. I have Roma connections in my family and at one point the word we’d use is ‘gypsy’. But, because I’m not Roma myself, if I came across some other group I wouldn’t assume…

Your first paragraph missed the point. Your second is how you deal with it. I've just told you I had a different experience. Your experience doesn't supersede mine. Your "be careful" (or else) doesn't sit well with people who don't like to be threatened.
Post reply on HN