News for hackers. Stuff that matters.
Re:SQL Keys in Depth by manigandham on Tuesday January 02, 2018 @11:15AM (#16052722)
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 by walshemj on Tuesday January 02, 2018 @12:03PM (#16053225)
does seem to me that the author is a hobbyist or a junior developer with little real word experience.

Literally the first Colum of almost every SQL table I have written has had a column called id with auto increment.

Re:SQL Keys in Depth by sparkie on Tuesday January 02, 2018 @12:19PM (#16053350)
The trouble with "natural keys" is that they're rarely actually unique. The barcode is a typical example. A naive developer might use a barcode as a primary key, but will soon be in for a world of pain when he realizes that products often use the same barcodes for different configurations (packaging etc), which usually need different SKUs. The same product from different origins may have the same barcode, which often matters.
Re:SQL Keys in Depth by mickronome on Tuesday January 02, 2018 @01:15PM (#16053870)
Not critique, but some additional perspectives for those less well versed in designing databases.

It's also important to realise that almost every time a duplicate key on an assumed unique key would trigger a constraint violation error, absence of this constraint would lead to some non sensical effects elsewhere in the system if ignored.

Barcodes are a typical example also for this, as unless the entire dataflow from warehouse to cash register is equipped to handle duplicate EAN codes, all kinds of nonsense results could be the result, potentially with significant economic risk if duplicates are allowed to enter into the system unchecked.

Everytime a constraint is removed from something that 'obviously' is/needs to be unique, the next step should be to add validation, duplicate and/or alias handling code everywhere that column is touched. Especially important at input and output.

Good database design is really hard, if at all possible, have other people try to come up with ways to break your scheme.

Re:SQL Keys in Depth by dahauns on Tuesday January 02, 2018 @07:38PM (#16057322)
Your last sentence can't be emphasized enough.

It's funny that while I fully agree with your whole post, I still lost count of the times a unique constraint that needed to be removed made for much more of a headache than if it hadn't been there from the beginning - because it turned out to never have been neccessary, but you still had to care for the uniqueness assumption in all associated code.

That's why I wouldn't recommend the authors rule of thumb:

  The rule of thumb is to add a key constraint when a column is unique for the values at hand and will remain so in reasonable scenarios.
I'd consider that a premature optimization. Add a uniqueness constraint if and only if there is a clearly defined need for it, because assessing reasonable scenarios quickly descends into a lesson about hubris.
Re:SQL Keys in Depth by brlewis on Wednesday January 03, 2018 @10:17AM (#16061609)
I still lost count of the times a unique constraint that needed to be removed made for much more of a headache than if it hadn't been there from the beginning - because it turned out to never have been neccessary, but you still had to care for the uniqueness assumption in all associated code.

Where you work do people never make uniqueness assumptions in application code unless there's an explicit unique constraint in the database? That's impressive. Are you hiring?

Re:SQL Keys in Depth by dahauns on Wednesday January 03, 2018 @11:28AM (#16062324)
Where did I say that they never do that?

It's just an observation based on experience (two decades in different companies of all sizes btw.) about the most blatant cases of this kind: the constraint was just set based on that rule of thumb, and since it was there in the schema, it was taken as gospel and any code throughout all the layers written with it in mind and shortcuts taken accordingly, all while there never was any requirement for it nor was the uniqueness actually assured (cf. those posts about names) and the breaking only a matter of time.

Mabye I've become jaded, but I've come to see those as being among the most annoying and unnecessarily time-consuming classes of issues.

Re:SQL Keys in Depth by pasta on Tuesday January 02, 2018 @01:25PM (#16053979)
And a product might get multiple barcodes.

I was a fan of natural keys but it is just too much trouble. For example you have to url encode everything when a key is used in the url.

But sometimes I still use natural keys for tables with for example ISO standards like country codes.

Re:SQL Keys in Depth by sparkie on Tuesday January 02, 2018 @03:17PM (#16055171)
ISO Country codes, language codes and currency codes are so common (and don't change very often) that they should really be part of the databases's standard distribution as types (eg, enums), so that everyone is singing from the same hymn book and that not every developer needs to implement the same tables in their DB.
Re:SQL Keys in Depth by 9034725985 on Tuesday January 02, 2018 @03:23PM (#16055231)
Re:SQL Keys in Depth by wvenable on Tuesday January 02, 2018 @03:59PM (#16055565)
I feel like an ISO country code is more of a surrogate key anyway; it's a mnemonic made-up value specifically designed to be used as key.
Re:SQL Keys in Depth by dragonwriter on Tuesday January 02, 2018 @04:07PM (#16055638)
An ISO country code isn't a surrogate key; it's not db-specific, and it has domain meaning (assuming you are using it for it's intended purpose). It's ususlly, when used, a representation chosen for natural domain information, so if it's a key, it's a natural key.
Re:SQL Keys in Depth by brlewis on Wednesday January 03, 2018 @10:18AM (#16061630)
From the article:

The naturalness or artificiality of unique properties in a database is relative to the outside world. A key which was artificial at birth in some standards body or government agency becomes natural to us because it’s generally agreed upon in the world at large, and/or imprinted on objects.

Re:SQL Keys in Depth by brlewis on Wednesday January 03, 2018 @10:13AM (#16061574)
does seem to me that the author is a hobbyist or a junior developer with little real word experience

Advocates of natural keys are aware that many people spend their careers creating auto increment primary keys everywhere. When combining disparate systems within a single organization, natural keys help. The biggest headache being difficult-to-identify duplicate data. However, this headache can be avoided if you have a natural candidate key. So just be sure to add unique constraints where appropriate, and the auto increment primary key (e.g. to make ActiveRecord happy) won't cause problems.

Re:SQL Keys in Depth by needusername on Wednesday January 03, 2018 @01:13AM (#16058900)
> global Hi/Lo id generation system

Can you elaborate a bit? Are you using an ORM, eg. Hibernate? If so have to considered other strategies like pooled-lo, IDENTITY or recursive CTEs that offer the same benefits but make the sequence values match the database values and reduce "id loss"?

Re:SQL Keys in Depth by jmts on Thursday January 04, 2018 @03:12PM (#16073653)
I'm also interested in some elaboration of this scheme. While I'm less interested in ORMs etc, the advantages/disadvantages would be useful to compare to some recent thoughts I've had regarding the use of databases.

Professionally I have little use for RDBMSs, however recently I've been working on some side projects with a focus on flexibility and modularity that have changed my perspective on how databases should be used. Formerly, I (and I assume many newcomers) had a database driven approach where the database structure came first, and code grew around that. This reduces flexibility because your data is all coupled together, which will force refactoring of data when your storage configuration changes. Data refactoring sounds like a thing of nightmares, but ability to easily migrate data between a RDBMS-backed module to a redis-backed module with minimal fuss is the kind of flexibility I'd like to have.

The alternative that I've settled on is to begin development with the notion of a generic 'data store' and later fitting a database around that. Though I am yet to see the scheme come to reality, I believe this will reduce data coupling, increase testability and debuggability for both code AND data associated with a given module, and increase migratability of the data. This has also pushed me toward the exclusive use of integers for IDs as they can be considered universally supported with minimal effort, which has me curious about further benefits of different ID generation schemes.

Re:SQL Keys in Depth by manigandham on Friday January 05, 2018 @11:40AM (#16079639)
Answered in the sibling comment: https://news.ycombinator.com/item?id=16079576

Anything with atomic increments works so we've also used a completely separate system for just IDs before like dynamodb or google's cloud datastore.

Re:SQL Keys in Depth by manigandham on Friday January 05, 2018 @11:30AM (#16079576)
Anything with atomic increments can do this, we use a simple table called idsequence with a single column and row. When an application starts up, it upserts 100000 and gets the new value. This gives both the min and max values that the app can then use as ids for any entities with no overlap with anything else.

It's actually not Hi/Lo but similar. You can also add another column to that table if you want multiple "sequences" but we like to keep it simple and with bigints, you'll never realistically run out.

If you're using an ORM, you might need some extra work but most of the time they are smart enough to use an existing ID if the object already has one rather than try to get one from the database.

Re:SQL Keys in Depth by needusername on Tuesday January 09, 2018 @02:47AM (#16104324)
What was your reason for not using database sequences?
Re:SQL Keys in Depth by manigandham on Monday January 22, 2018 @02:16PM (#16207042)
It was in my original post:

>> 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 by dboreham on Tuesday January 02, 2018 @01:14PM (#16053865)
We adopted a similar (well, I think probably identical) approach some years ago. No regrets -- it is simple and works well.