SQL Keys in Depth • Hacker News Board

Re: SQL Keys in Depth

#121

Post by dboreham » Tue, Jan 02, 2018, 6:14 PM UTC

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…

We adopted a similar (well, I think probably identical) approach some years ago. No regrets -- it is simple and works well.

Re: SQL Keys in Depth

#122

Post by mickronome » Tue, Jan 02, 2018, 6:15 PM UTC

sparkie wrote: post #116

walshemj wrote: post #112

Earlier quoted context omitted.

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.

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…

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

#123

Post by surrogatekey » Tue, Jan 02, 2018, 6:23 PM UTC

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…

The bit about duplicates likely being "unintentional at this scale" is an important part of the use context too though. There are many cases where allowing duplicate names increases errors and confusion for users -- not only because of unintentional duplication, but because users themselves often think of names as unique.

To me, surrogate keys are just about always preferable over natural keys, but it's also important to think about the "naturalness" of natural keys from a user's pov. In some contexts, using a surrogate key while also enforcing unique names (with the rare possibility of a case where an admin has to go in and do something weird like add a genuinely identical name with a "2" after it, say) can be a better trade-off.

Re: SQL Keys in Depth

#124

Post by pasta » Tue, Jan 02, 2018, 6:25 PM UTC

sparkie wrote: post #116

walshemj wrote: post #112

Earlier quoted context omitted.

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.

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…

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

#125

Post by Gustek » Tue, Jan 02, 2018, 6:50 PM UTC

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

From how I understand it the author does not make any suggestions to use natural keys as primary key. He just defines what is natural key. And he does mention that they may change and it has to be considered.

Actually in the summary he suggests to create column with uuid in each table and use it as primary key.

Re: SQL Keys in Depth

#126

Post by cheapsteak » Tue, Jan 02, 2018, 6:53 PM UTC

jimktrains2 wrote: post #114

navium wrote: post #110

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

Yes, in PG at least an index is created, otherwise the unique check would become prohibitive.

I think the question is more on whether a duplicate index would be created instead of having the unique constraint and primary key designation on the same column share an index

Re: SQL Keys in Depth

#127

Post by default-kramer » Tue, Jan 02, 2018, 6:57 PM UTC

hug wrote: post #14

Ericson2314 wrote: post #5

This is well done. And yet many people get this stuff wrong. As a language person, I think about how we could make these choices more natural by rewriting SQL; make them the path of least resistance rather than requiring much pondering and wisdom.

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 share your opinion that SQL is a very flawed language but it is still the best option I know of.

I am absolutely sure someone could make a better SQL from scratch. (But you are correct that most people won't be able to.) I am not a language designer but I've been working on an SQL generator (almost DSL) in Racket. Although it's still very rough, I like it better than SQL for writing views and stored procedures. Imagine if a world-class language designer developed a new query language for your RDBMS of choice. It would probably fix most of the things you loathe.

Re: SQL Keys in Depth

#128

Post by SatvikBeri » Tue, Jan 02, 2018, 7:03 PM UTC

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…

> This makes more sense than above, but MAC addresses can be spoofed or even just used as a proxy, so you would need to be careful that the information you're uniquely storing against the MAC address is intended to be unique. Most use cases that immediately springs to my mind wouldn't follow his unique rule but I'm sure there will some examples that do.

As one example, there was a bug in one version of CyanogenMOD that caused many phones to broadcast the same MAC, even when connected to Wifi. In some cases, this would cause different devices to kick each other off of a connection. See e.g. https://forum.xda-developers.com/tmobile-lg-g3/help/2x-lg-g3...

Re: SQL Keys in Depth

#129

Post by RobertRoberts » Tue, Jan 02, 2018, 7:19 PM UTC

extrapickles wrote: post #91

adambatkin wrote: post #73

Earlier quoted context omitted.

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…

UUIDs are the best choice when developer time is more important than space usage. Also, they can be generated and used by the client when the connection to the db is frequently down (eg: clients store/query data locally in SQLlite and replicate to master).

I am not a db expert, which is why I like read these kinds of comment threads. But what about this?

https://en.wikipedia.org/wiki/Universally_unique_identifier#...

"The random nature of standard version 3, 4, and 5 UUIDs and the ordering of the fields within standard version 1 and 2 UUIDs may create problems with database locality or performance when UUIDs are used as primary keys. For example, in 2002 Jimmy Nilsson reported a significant improvement in performance with Microsoft SQL Server when the version 4 UUIDs being used as keys were modified to include a non-random suffix based on system time. This so-called "COMB" (combined time-GUID) approach made the UUIDs non-standard and significantly more likely to be duplicated, as Nilsson acknowledged, but Nilsson only required uniqueness within the application."

Re: SQL Keys in Depth

#130

Post by pletnes » Tue, Jan 02, 2018, 7:29 PM UTC

pc86 wrote: post #72

onion2k wrote: post #26

Earlier quoted context omitted.

It's also worth noting that the same is true for email addresses. People share them, so two people can have the same address.

While technically accurate, this is non-standard to such a degree, and represents such a minority percentage of the population, that "don't do this" really is an appropriate response to the user.

Mailing lists and shared mailboxes might be used by several people (support@foo.com). Noone uses them to sign up to online services I guess, but the address might be stored in a database and be assocoated with several people somehow.