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

51–60 of 174 posts

Re: SQL Keys in Depth

#51

Post by simonw »

MarHoff wrote: post #34
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 agree with you, but I think you did overthink his metaphorical example a little. I think the point was that for a 40 people club odd are very very poor that two people would have the same name. And even if so a club manager could still differentiate by adding a middle name or a nickname (in a 40 people scenario). Of course IF your neighborhood club expand and you need to manage a lot of people you'll have to switch…

Two people in my class at high school had the exact same first and last names, and they were not common names like "Matthew Smith".

The thing that upsets me here is the idea that dropping the constraint later on is easy. It's only easy if you are a software engineer!

Re: SQL Keys in Depth

#52

Post by cup-of-tea »

stickfigure wrote: post #3

On Postgres I prefer to use a single sequence to generate ids across all tables. This reduces the chance of accidents (eg accidentally deleting the wrong thing with id #123) and reduces information leakage ("oh, I see I'm customer #5, you must only have 4 other customers").

What is the advantage over UUIDs? The author says that the surrogate keys should never be exposed outside of the database anyway, so no problems with data leakage. If an artificial key is to be exposed then it can be obfuscated to prevent information leakage.

I think it should be obvious that it is obfuscated, though, like Youtube video IDs, for example. If you generate integers then your users might assume they are not obfuscated. Eventually a customer will get some low value integer and might assume that they are "customer #5" even if they are not.

Re: SQL Keys in Depth

#53

Post by rotten »

foreigner wrote: post #33

At what scale does all this stuff start to actually matter? I have an database with ~100 tables and ~500M rows driving a medium-traffic web app and various back-end systems. We use auto-incrementing integers as primary keys and try not to expose them externally. Indexes are added as necessary to enable specific queries. We don't enforce any other constraints (e.g. not-null or foreign keys) at the database level. ...…

You can get some real headaches with auto-incrementing integers if you have to go to a multi-master or some other distributed system for data inserts.

How do you know which integer to insert next for a table that is replicated across systems? One system could do even numbers and one could do odd. One could do every other 100 integers. Or you could use UUIDs and not worry about it.

My argument is that auto-incrementing integers are inherently not scalable. They can scale to a certain extent, and then you have to switch key systems.

Re: SQL Keys in Depth

#54

Post by bastih »

olavk wrote: post #36
MarHoff wrote: post #34

Earlier quoted context omitted.

I agree with you, but I think you did overthink his metaphorical example a little. I think the point was that for a 40 people club odd are very very poor that two people would have the same name. And even if so a club manager could still differentiate by adding a middle name or a nickname (in a 40 people scenario). Of course IF your neighborhood club expand and you need to manage a lot of people you'll have to switch…

I'm not convinced that the odds are "very very poor". Some names (like Robert Smith or Maria Garcia) are quite common. And a hobby club is not a random selection of people, members would typically tend towards a specific geographic area, social class or culture depending on the subject. Some cultures have a very limited pool of names, depending on tradition.

Just from looking at a 200 member club I'm part of, I can see at least two name collisions in our current club roster. So absolutely agree with your assessment.

Re: SQL Keys in Depth

#55

Post by jeltz »

lifepillar wrote: post #7

The most important property of surrogates, which I never see mentioned, is that two tuples, anywhere in the database , have the same surrogate key if and only if they are (believed) to refer to the same real-world entity. This is a crucial difference wrt auto-incrementing or randomly generated values that are independent in different relations (which is a common practice). Among the rest, with surrogates defined as a…

I would argue that the support is there. You can either use random UUIDs or a global shared sequence for this. Nothing requires you to have a sequence per table other than some synthactic sugar in the SQL standard. PostgreSQL also has its own OIDs, but I think that is more of a legacy from before they implemented sequences.

Re: SQL Keys in Depth

#56

Post by onion2k »

yeukhon wrote: post #37
onion2k wrote: post #26

Earlier quoted context omitted.

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

True people do share. But you have to believe it isn’t. If your system already assumes email must be unique, then your system will make email field as a unique constraint. Otherwise what will you considered as unique? Phone number, however, should be avoided as an ID. Far more likely to have phone number ownership changed than Google reclaiming your email address and give it to someone else realistically.

If your system already assumes email must be unique...

...then you're not building a system that works with the way users work. Some (very small) percentage of your users will have problems.

I first encountered this problem when I asked my parents to test my first startup. They shared an email account. When they signed up to things they just used the shared account. It was fine until they wanted separate accounts on the same service and it failed. It's not OK to assume everyone has a unique email address and that works as a reference to an individual. The assumption is incorrect. Email addresses are not unique to individuals.

A lot of services fail at this. If you're targeting older people then you shouldn't use a users email address as a username, and nor should you use it as a security factor.

Re: SQL Keys in Depth

#58

Post by ThrowAwayFSp »

pizza234 wrote: post #27
dullgiulio wrote: post #22

Earlier quoted context omitted.

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

wonder no more

Re: SQL Keys in Depth

#60

Post by kdeldycke »

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…

Might be a feature though, as in "the Ancient Mystic Society of No Homers", where it is not allowed to have more than one Homer member: https://en.wikipedia.org/wiki/Homer_the_Great#Plot
Post reply on HN