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

31–40 of 174 posts

Re: SQL Keys in Depth

#31

Post by B-Con »

> One thing to avoid in publicly exposed numbers is sequential order. It allows people to probe for resources (/videos/1.mpeg, /videos/2.mpeg etc) and also leaks cardinality information. Add a Feistel cipher on top of a sequence. This hides the ordering while maintaining uniqueness.

> The PostgreSQL pseudo encrypt wiki gives an example cipher function: > [...]

I get that they want to leave the key value in the schema, but practically this kind of thing feels worth pulling out into the storage engine. Just generate a random string as the ID upon row insertion and force uniqueness on that column. If insertion fails, generate another random string. (And it should pretty much never fail because otherwise you're still suspect to the enumeration attack you're trying to prevent.)

Although given that Postgres can read from a file, could the above be done in the schema by reading from /dev/urandom? If so that seems like the better approach.

Re: SQL Keys in Depth

#32

Post by ProblemFactory »

dragonwriter wrote: post #8
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…

> Unfortunately, current DBMSs provide little to no support for properly implemented surrogates. UUIDs instead of per-table autoincrement keys seem to be a solution which covers the part of the problem that involves DB support as opposed to data model design, and several DBs have adequate support for UUIDs.

UUIDs (and other mostly random IDs) also have the important advantage that they can be generated offline - in mobile apps, client-side javascript or elsewhere.

With autoincrement keys, you need to either contact the database server for each data object you create, or implement complex logic to replace temporary IDs with database IDs later.

Re: SQL Keys in Depth

#33

Post by foreigner »

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.

... and it all works and performs just fine? The considerations the author mentions all make some sense to me in theory, but when do they actually matter in practice in a modern system?

Re: SQL Keys in Depth

#34

Post by MarHoff »

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 to a better technique. His point was that you must fit to you business case. (But hey some people change name when they marry so... problems can happen fast, but that's mutability issue not uniqueness).

Overall very good article but keeping critical mind as you did is needed.

Re: SQL Keys in Depth

#36

Post by olavk »

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…

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.

Re: SQL Keys in Depth

#37

Post by yeukhon »

onion2k wrote: post #26
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.

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.

Re: SQL Keys in Depth

#38

Post by tzahola »

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…

Ah, this brings me back to the time when I was trying to convince a product manager that it was a bad idea to "validate" email addresses with regular expressions. I failed, and the product was rolled out with the following regex: ^[a-z0-9.-]+@[a-z0-9.-]+\.[a-z]{2,4}$

I quit shortly thereafter.

Re: SQL Keys in Depth

#39

Post by tzahola »

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…

>I think the point was that for a 40 people club odd are very very poor that two people would have the same name.

Wrong. For example I have exactly the same name as my father and we're members of the same squash club.

Re: SQL Keys in Depth

#40

Post by tome »

hug wrote: post #21
Terr_ wrote: post #19

Earlier quoted context omitted.

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

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

Do you believe that all keyboard layouts are of indistinguishable practicality? That seems to be the inescapable conclusion of your claim.

Post reply on HN