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

161–170 of 174 posts

Re: SQL Keys in Depth

#161

Post by rotten »

adambatkin wrote: post #73
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 disagree that UUIDs are generally preferable over integers. For one, they take up more space (on disk and in memory). And for something like a key, it is likely that there will be multiple copies of that value stored, since it will exist in the table itself, at least one index (possibly more) and foreign keys. More space means fewer records per page on disk, more I/O and more memory usage (potentially leading to mo…

The uuid data type in PostgreSQL _is_ an integer.

Re: SQL Keys in Depth

#162

Post by brlewis »

walshemj wrote: post #112
manigandham wrote: post #103

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

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.

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

#163

Post by brlewis »

dahauns wrote: post #150
mickronome wrote: post #122

Earlier quoted context omitted.

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…

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…

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

#164

Post by brlewis »

wvenable wrote: post #140
pasta wrote: post #124

Earlier quoted context omitted.

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.

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.

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

#165

Post by brlewis »

wvenable wrote: post #156
dizzystar wrote: post #152

Earlier quoted context omitted.

A badly built database isn't the reason to toss out good practices. It's actually quite obnoxious to see how bad many databases are, and this often causes a blow-back of tossing the baby out with the bathwater. It's a trap. I'm not really sure what all the problems are, but if changing username to something else is causing a 24 shutdown, there are many deeper issues than a PK on username.

> A badly built database isn't the reason to toss out good practices. It's a good reason to toss out bad practices like, for example, using natural keys as primary keys. Which is, almost universally, considered a bad practice. It is, in fact, the bad practice that causes the most pain in this bad database. > if changing username to something else is causing a 24 shutdown, there are many deeper issues than a PK on use…

I'm curious what database this is. Even mysql supports ON UPDATE CASCADE back to 5.5 (or earlier?).

Re: SQL Keys in Depth

#166

Post by bmm6o »

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.

Stack Overflow lets you change your name.

Re: SQL Keys in Depth

#167

Post by dahauns »

brlewis wrote: post #163
dahauns wrote: post #150

Earlier quoted context omitted.

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…

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…

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

#168

Post by lulmerchant »

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

My background is in financial applications, where an inconsistent state can literally be a multimillion dollar problem, so maybe my perspective is skewed a little. But FKs and other constraints are simply used to outright enforce consistency, in my experience at least. If you rely on your custom business logic to maintain consistency, then you leave yourself open to the possibility of breaking that in some way, and if you do, who says you'll notice right away? What if you don't notice for 6 months, and then realise that you have a billion transactions that have some form of integrity compromise?

Complexity obviously makes this problem more serious, but something like this can easily occur at any scale of complexity or transaction volume. If you try to commit some code that breaks the integrity of an important relationship in some way, or even if you just want to run an arbitrary statement directly against the database, then you want the DB to throw an error.

Re: SQL Keys in Depth

#169

Post by mikst »

hey, is it me being far from expert, or:

why use UUIDv1 if you can just use timestamp?

wouldn't time-based solution be prone to time misconfig across servers? Or that little shuffle doesn't really matter?

Re: SQL Keys in Depth

#170

Post by jmts »

needusername wrote: post #155
manigandham wrote: post #103

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

> 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"?

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.

Post reply on HN