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

101–110 of 174 posts

Re: SQL Keys in Depth

#101

Post by slg »

qaq wrote: post #69
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.

Which is what your sibling comment mentioned. The author does touch on that approach but never touches on the drawbacks, some of which I mentioned in my other comment and some which are directly in conflict with the benefits of UUIDs. My point is merely that there are other details that a MSSQL user should take into consideration before simply following the author's suggestion. A novice MSSQL user might be better off with auto incrementing bigints in a way a novice user of a different RDBMS might not.

Re: SQL Keys in Depth

#102

Post by xiconfjs »

Sean1708 wrote: post #88
laumars wrote: post #75

Earlier quoted context omitted.

That's not the only point of the article I really disagree with: > Here are some values that often work as natural keys: > login names This makes user names static. Which would annoy people if they change name (eg marriage, gender change, nationalization change (Chinese name vs English name)) or they just want to update their online handle. > email addresses Same problem as above. What happens if someone wants to cha…

>> login names > This makes user names static. Does it? I can understand why making it a foreign key would make it static, but why would making it a normal key make it static? It seems to me that making login names unique would be preferable, same with emails.

if you are linking the login name to other tables, author of a forum/board post, it gets complicated, after the login name changed...

Re: SQL Keys in Depth

#103

Post by manigandham »

After a decade of large systems relying on RDBMs, we now use 64-bit integers for all primary keys with a global Hi/Lo id generation system (app reserves a range of numbers on startup to assign to records automatically).

This means plenty of ID space, maintains rough numeric ordering, allows ID creation without a roundtrip for every insert, is easily portable across different databases, and produces unique IDs for every row in the database which greatly simplifies everything from caching to replication.

Re: SQL Keys in Depth

#104

Post by arundelo »

laumars wrote: post #75
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…

That's not the only point of the article I really disagree with: > Here are some values that often work as natural keys: > login names This makes user names static. Which would annoy people if they change name (eg marriage, gender change, nationalization change (Chinese name vs English name)) or they just want to update their online handle. > email addresses Same problem as above. What happens if someone wants to cha…

RFC 2392 Message-ID values are supposed to be unique, but they may not be if the software generating them is buggy. I remember reading somewhere about some email program that assumed they were unique (in effect using them as a natural key). If you were unlucky enough to get messages with identical Message-ID values, all but one would disappear.

Re: SQL Keys in Depth

#105

Post by Jwarder »

laumars wrote: post #93
Sean1708 wrote: post #88

Earlier quoted context omitted.

>> login names > This makes user names static. Does it? I can understand why making it a foreign key would make it static, but why would making it a normal key make it static? It seems to me that making login names unique would be preferable, same with emails.

If i understand the article correctly it's discussing using natural keys as primary keys. Eg if you're using login_name as a natural key then that's presumably replacing a more traditional user_id. So it would be hard not to use login_name as a foreign key in that scenario. Where as if you still had a user_id as your primary key, you could still have user_id as a unique key (most RDBMS I've used support "unique keys"…

> If i understand the article correctly it's discussing using natural keys as primary keys.

I'm not sure about the author's intent here. In the prior section they dismiss the necessity of primary keys. However, the author rails on about unique keys. I'm not familiar with PostgreSQL, but I assume every reference to "key" is effectively an index with a unique constraint. If that is true then all of the discussed issues with unique names are the same even if the author doesn't care about primary key in particular.

Re: SQL Keys in Depth

#106

Post by qaq »

slg wrote: post #101
qaq wrote: post #69

Earlier quoted context omitted.

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.

Which is what your sibling comment mentioned. The author does touch on that approach but never touches on the drawbacks, some of which I mentioned in my other comment and some which are directly in conflict with the benefits of UUIDs. My point is merely that there are other details that a MSSQL user should take into consideration before simply following the author's suggestion. A novice MSSQL user might be better off…

I think article was pretty focused on PG. :) unlike MSSQL it does not cluster data on an index (you can run cluster command but it will not be preserved as data is inserted updated) so that concern is fairly specific to MSSQL.

Re: SQL Keys in Depth

#107

Post by [deleted] »

xiconfjs wrote: post #102
Sean1708 wrote: post #88

Earlier quoted context omitted.

>> login names > This makes user names static. Does it? I can understand why making it a foreign key would make it static, but why would making it a normal key make it static? It seems to me that making login names unique would be preferable, same with emails.

if you are linking the login name to other tables, author of a forum/board post, it gets complicated, after the login name changed...

[deleted]

Re: SQL Keys in Depth

#108

Post by dragonwriter »

openasocket wrote: post #96
Sean1708 wrote: post #88

Earlier quoted context omitted.

>> login names > This makes user names static. Does it? I can understand why making it a foreign key would make it static, but why would making it a normal key make it static? It seems to me that making login names unique would be preferable, same with emails.

I agree, I can't think of any system off the top of my head that lets you change your handle, or why you would really want to. If it's a forum or has commenting capabilities, your handle is your absolute identity. If it's not a forum, no one ever really sees your username, so there's little motivation to change it.

> I can't think of any system off the top of my head that lets you change your handle, or why you would really want to.

Pretty much any system with a real name policy both does and needs to let you change your handle, because preferred real (and legal) names change. Now, these systems often use email addresses as login names, but they sometimes also support changing the primary email address as well. In effect, identity is most likely managed by a surrogate key that isn't exposed.

Re: SQL Keys in Depth

#109

Post by wvenable »

dizzystar wrote: post #12

It's an interesting article with interesting ideas. I'm squarely in the camp of using natural PKs until there is good reason to use surrogate PKs. I generally disagree with the notion of using a combination of surrogate and natural keys. In SQL, a PK isn't just another unique key: a PK an important block of communication. As a rule of thumb, when a PK is attached to a semantic value, it is saying that this is the ide…

> If a PK is on a surrogate key, it is saying that there is no good unique identifying value in the table.

Almost always there is no good unique unchanging identifying value on a table. Take something as simple as "Person" -- there is literally no unique unchangeable value for such an entity. And that's the rule more than the exception.

I take exactly the the opposite approach; nearly all entities should be identified with a surrogate key. You pretty much cannot go wrong with this approach.

In the US state names are pretty stable, but in other countries state/province names have changed so even that value is poor choice for a primary key.

Re: SQL Keys in Depth

#110

Post by navium »

"There’s no need to manually create indexes on columns already declared unique; doing so would just duplicate the automatically-created index." Is the last part real? Makes me feel like forking postgesql just to save the world from accidental duplicate expensive indexes.
Post reply on HN