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

61–70 of 174 posts

Re: SQL Keys in Depth

#61

Post by yeukhon »

onion2k wrote: post #56
yeukhon wrote: post #37

Earlier quoted context omitted.

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…

> It was fine until they wanted separate accounts on the same service and it failed.

When it comes to designing a system, I try my best to prioritize this way: security, privacy, and finally usability. A product with the best user-experience may have the most vulnerable design. I say this because we need to balance between security and UX. Pick some constraints which make sense to your system.

If we have to accommodate every corner case, the system will become extremely insecure and unreasonable. In your case, perhaps solve it with "sub-account" if that makes sense for your system. To Netflix, sub-account seems reasonable so Netflix gives that as an option for user to share his/her account with his/her family/buddies.

Actually, sorry if being a little out of bound, you probably should help your parents setting up unique email address for them. They want a separate account for some specific reasons, I assume, so why not help them?

Email was created TO be unique. It is we the users who decide how to use that email address, BUT it is not the system/service designer TO accommodate every use case.

Re: SQL Keys in Depth

#62

Post by daenney »

txutxu wrote: post #35

Great article. But MAC address cannot be considered unique, as soon as software like keepalived is running.

OK. But how does that relate to this article? If you're using keepalived what you're doing is moving the connection from one instance to another if the current one is detected as being dead. Even if only the MAC was used to generate the UUID (which it is not, timestamp is factored in too) I struggle to find a scenario in which this would be a problem. Even if you time it down to the nanosecond, if keepalived is used as intended you'd never generate a UUID at the same time on a number of machines sharing the MAC.

And from a privacy concern, if the MAC cannot be considered unique, that's only a bonus?

Re: SQL Keys in Depth

#63

Post by vinceguidry »

olavk wrote: post #23
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.

I'm guessing he put that in just to annoy people that are easily annoyed. Kind of like how every woodworking video on YouTube is invariably slammed with hundreds of commenters accusing the maker of being literally Hitler over some very small safety detail. After awhile they stop trying and just poke fun.

It does make me appreciate the one guy who can rise above all that, Paul Sellers.

Re: SQL Keys in Depth

#64

Post by jeltz »

cup-of-tea wrote: post #52
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…

Using a global sequence means 8 bytes less storage per row (including index entries and all foreign keys which refer to the PK), and more importantly typically mostly sequential inserts into B-tree indexes which means less bloat and faster insert and access.

Disadvantages are data leakage and, theoretically, minor contention on accessing the sequence.

Re: SQL Keys in Depth

#65

Post by danso »

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…

But why would a database in the scale of 1-100 records need a key constraint to begin with?

Re: SQL Keys in Depth

#66

Post by barrkel »

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

I've seen auto_increment fail to scale on MySQL. Large, long-running insert statements (e.g. using insert into ... select from, or load data infile) can lock the the table resource and block concurrent inserts. Pretty easy to work around: have a table containing the next available key for every table, and use a separate small transaction to bump it before bulk inserts.

Another reason not to use auto-inc is if you need to insert lots of data into several separate tables that have foreign key relationships. If you know what you're inserting up front, and you need to insert millions of rows quickly, you're better off allocating the primary keys ahead of time and supplying them directly with the right keys for the relationship at insert time.

Separately, another argument against compound keys: if you're in a situation where you're trying to optimize joins, sorts and filters over moderately large tables, you want to minimize the number of columns you touch. Every column measurably increases query time; more data to shuffle, sort, compare, bigger indexes, etc. You won't see this if you're doing simple key / row lookups, but you will see it if you're presenting user-interactive slices over million+ row data sets.

Re: SQL Keys in Depth

#67

Post by jeltz »

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 think there is some network-effect and inertia, but I also think it is hard to design a query language which is significantly better than SQL. All attempts I have used have instead been clearly worse.

Re: SQL Keys in Depth

#69

Post by qaq »

slg wrote: post #9

>Modern SQL is supposed to abstract from the physical representation. Tables model relations, and should not expose an implicit order in their rows. However even today SQL Server creates a clustered index by default for primary keys, physically ordering rows in the old tradition. Which is why the UUID (or GUID in SQL Server speak) can have other drawbacks there in comparison to auto incrementing bigints, namely delay…

The solution to your concern is using " UUID generation algorithms (like Twitter’s “snowflake” or uuid_generate_v1() in the uuid-ossp extension for PostgreSQL) produce monotonically increasing values per machine." and is discussed in the article.

Re: SQL Keys in Depth

#70

Post by nerdponx »

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

A lot of these are not performance considerations. I read them as mostly for architectural ergonomics, security, and to some extent "idiot-proofing".
Post reply on HN