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

SQL Keys in Depth

begriffs.com

Post reply on HN
Print view View original

21–30 of 174 posts

Re: SQL Keys in Depth

#21

Post by hug »

Terr_ wrote: post #19
hug wrote: post #14

Earlier quoted context omitted.

Let's be honest: If you could make these choices more natural then you would have done so by now. If you could make SQL better than it is, you already would have done that as well. In fact, if most people who are interested in SQL could make SQL better than SQL is, we wouldn't be using SQL anymore. We're not using it because we have some weird tradition we enforce. We're not using it because we like the way it looks,…

I think you're discounting network-effects and historical inertia. Even things which are functionally very simple to implement won't necessarily catch on, ex: "If you could make keyboard layouts more practical than QWERTY, then you would have done so by now."

I mean, maybe I am, maybe I'm not. How many other languages from the '70s haven't been replaced in a domain where they should have been?

And, I mean, maybe your example isn't really the best example you could have given. Name a layout quote-unquote more practical than QWERTY that should have caught on but didn't. Dvorak? No faster. Colmac? Nah. Any, y'know, studied benefits? None I can find, at least not with a P-value worth mentioning.

I'm not saying SQL is the definitive end of database query languages, of course, because that would be silly. There is, almost certainly, a more objective truth. What I'm saying is good luck finding it; It's only semi-cynically that I say I wish you luck finding a better language than SQL. I honestly, with all my heart, wish that SQL could be replaced with something better, because I hate SQL.

When some guy comes along and is like "we should do a better SQL!" the answer is yes, we should, but I'm not exactly going to hold my breath,

Re: SQL Keys in Depth

#22

Post by dullgiulio »

ofirg wrote: post #18
dullgiulio wrote: post #15

Earlier quoted context omitted.

I think the author fell for falsehood 21: http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-b...

I do not want to belong to any club that would not need to drop a database constraint in order to accept me.

Yes, that's the problem. I understand what he wants to say (basically YAGNI), but imagine the reception desk employee having this problem, escalating it until it reaches a developer just to register John Doe.

Re: SQL Keys in Depth

#23

Post by olavk »

simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

Yeah I was surprised by this in an otherwise great article.

Re: SQL Keys in Depth

#24

Post by planetjones »

simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

Completely agree. Adding a proper primary key e.g. from a sequence involves very little extra overhead and protects against this. Asking them to drop the constraint in the future sounds a bit silly as that may mean two members get deleted when you intended only one. Plus your foreign keys now have to be maintained. So if a member changes their name you have to update all foreign keys. Also if the hobbyist club then builds a website to view members the URLs can use the primary key e.g. a long / bigint, rather than names. The names may introduce further complexity with having to escape the URL etc. I am all for keeping it simple, but all relations should have a guaranteed unique primary key.

I know the author is talking about a small scale project here, but shortcuts like this (in my experience) just lead to a mess later on; irrespective of the complexity of the problem being solved.

Re: SQL Keys in Depth

#25

Post by KC8ZKF »

dullgiulio wrote: post #15
simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

I think the author fell for falsehood 21: http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-b...

No he didn't. The "first name/last name" constraint is a straw man for didactic purposes. He's explaining why it never made sense.

Re: SQL Keys in Depth

#26

Post by onion2k »

simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

It's also worth noting that the same is true for email addresses. People share them, so two people can have the same address.

Re: SQL Keys in Depth

#27

Post by pizza234 »

dullgiulio wrote: post #22
ofirg wrote: post #18

Earlier quoted context omitted.

I do not want to belong to any club that would not need to drop a database constraint in order to accept me.

Yes, that's the problem. I understand what he wants to say (basically YAGNI), but imagine the reception desk employee having this problem, escalating it until it reaches a developer just to register John Doe.

I wonder if the parent's reply is actually a joke based on paraphrasing Groucho Marx (the statement itself doesn't have much logical sense).

Re: SQL Keys in Depth

#28

Post by dragonwriter »

simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

> Expecting a club administrator to be able to drop a uniqueness key from their database in order to resolve this situation is not a reasonable solution!

Especially since if you have any FK relationships, and that was the only candidate key for the members relation (and why have such a bad key if it's not the only one), you then need to add new attributes to create a new key, populate it to all existing roles, change all existing FK relationships and associated queries, on top of the easy part, which is dropping the old unique constraint.

Re: SQL Keys in Depth

#29

Post by bullen »

There are two things this article could point out:

The difference between centralized, distributed and decentralized primary keys.

And how public/private key encryption should be a natural part of every primary key.

The problem is all SQL databases uses sync clients which make them completely useless in any distributed or decentralized setting.

The real tension here is that there is no guarantee that a large UUIDs are unique, and we have not embraced the random occurrence of a collision.

Re: SQL Keys in Depth

#30

Post by lathiat »

simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

For extra fun there was talk today on twitter about several people that have to file Japanese tax returns but have originally english names. There are many ways to convert the name to english, and different systems actaully enforce you to convert it in a different way with various limitations.

This results in them having the system believe they are two people, one of who hasn't filed returns and no easy way to fix it (apparently the easiest fix is to possibly create a company?).

This despite there being some kind of unique ID submitted in both systems, but that ID is not currently reconciled.

Not sure 100% on the details myself but I guess the moral of that story is, duplicate checks on the name itself may not even be a guarantee of uniqueness - even assuming there was only 1 person with that name!

More information in the Twitter threads: https://twitter.com/patio11/status/947821990810869762 https://twitter.com/marcan42/status/947862901725024257

Post reply on HN