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

141–150 of 174 posts

Re: SQL Keys in Depth

#141

Post by WorldMaker »

int_19h wrote: post #20
eropple wrote: post #13

Earlier quoted context omitted.

That's not a bad option, but I think I'd find locally-unique integer IDs spread across tables to be a little confusing. In that case I think I'd lean towards UUIDs, which may be a little easier overall, or use separate sequences but expose IDs via Hashids in my API/frontend: http://hashids.org

In Postgres, having multiple tables share a single ID sequence is trivial thanks to SEQUENCE/NEXTVAL.

Sure, but Postgres doesn't to my knowledge support a cross-table UNIQUE index to insure your sequences are followed in all cases. I imagine that all it takes is one confused junior developer to add a bunch of manually IDed data by hand to create some weird and confusing corner cases in this scheme.

Re: SQL Keys in Depth

#142

Post by dragonwriter »

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.

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

#143

Post by WorldMaker »

danielovichdk wrote: post #42

Funny example in regards of a card-deck. How would you have unique keys for the 3 jokers in the deck ?

Decks with four jokers are often suited (just as the most common decks with two jokers are red/black), so a deck with three jokers you could treat as suited jokers that are missing a suit.

Re: SQL Keys in Depth

#144

Post by WorldMaker »

LeonM wrote: post #44
danielovichdk wrote: post #42

Funny example in regards of a card-deck. How would you have unique keys for the 3 jokers in the deck ?

A joker does not have a suit, nor a number, so it would not fit in the example table anyway.

Arguably Joker is the number, and like Ace is 1 you might use 0 or 14 depending on math preferences as a surrogate number (or stick to the character designations like A, K, Q, J).

Decks with four jokers typically have suited jokers. Decks with two are typically (though not always) red/black and could be given arbitrary suits (say hearts/spades).

Re: SQL Keys in Depth

#145

Post by mannykannot »

gxs wrote: post #139
mannykannot wrote: post #98

Earlier quoted context omitted.

You might not have any observable effects until the day you do, at which point you may be faced with an arbitrarily large problem. What you are doing is passing up on the opportunity of catching various errors (you are also passing up the opportunity for some optimizations, but that is probably a secondary issue.) In particular, you are passing up on some opportunities to catch inconsistencies in how different applic…

I think OPs question was exactly asking the question of when these problems do arise, not how large they are when they do. I'd be curious to hear when they do, would be great if you had some real world examples.

My point is that you cannot know when problems will show up. If you do everything right, they will never happen, but why pass up an opportunity to reduce the risk?

Re: SQL Keys in Depth

#146

Post by dizzystar »

wvenable wrote: post #109
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 identifie…

Yes, each set of data should reflect the data you want to use. That's why I said I default to natural keys unless there is good reason to use a surrogate key.

If I have to compare databases I've worked on over the years, the databases that are all surrogate will have many more data errors than a database that is predominantly natural keys. I could write a book on why that happens, but if you don't know relational theory cold (not saying you don't), you should just use natural keys. The short answer is that using all surrogate keys is equivalent to not using unique constraints at all.

Data is bound to change at some point in the future. The entire point of having constraints is to ensure that your data keeps its integrity, now and later on, no matter how the data needs to be updated.

Re: SQL Keys in Depth

#147

Post by MaxGabriel »

tzahola wrote: post #38
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…

Ah, this brings me back to the time when I was trying to convince a product manager that it was a bad idea to "validate" email addresses with regular expressions. I failed, and the product was rolled out with the following regex: ^[a-z0-9.-]+@[a-z0-9.-]+\.[a-z]{2,4}$ I quit shortly thereafter.

I used to hold this opinion, but this Stack Exchange post https://dba.stackexchange.com/a/165923/34006 changed my mind. In short, HTML5 defines its own specification of an email address here [2], and notes:

> This requirement is a willful violation of RFC 5322, which defines a syntax for e-mail addresses that is simultaneously too strict (before the "@" character), too vague (after the "@" character), and too lax (allowing comments, whitespace characters, and quoted strings in manners unfamiliar to most users) to be of practical use here.

> The following JavaScript- and Perl-compatible regular expression is an implementation of the above definition.

> /^[a-zA-Z0-9.!#$%&'+\/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)$/

To quote the DBA Stack Exchange post, "if it's good enough for HTML5, it's probably good enough for you". And if you're using inputs with `type="email"` in HTML5, you already are constraining your emails to that format.

(That said, it sounds like the regular expression you were asked to use was not a good one)

[2] https://html.spec.whatwg.org/multipage/input.html#valid-e-ma...

Re: SQL Keys in Depth

#148

Post by wvenable »

dizzystar wrote: post #146
wvenable wrote: post #109

Earlier quoted context omitted.

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

Yes, each set of data should reflect the data you want to use. That's why I said I default to natural keys unless there is good reason to use a surrogate key. If I have to compare databases I've worked on over the years, the databases that are all surrogate will have many more data errors than a database that is predominantly natural keys. I could write a book on why that happens, but if you don't know relational the…

I'm working with a 3rd party database right that used more than a few natural keys and it's awful. The primary key for "users" is username so we can't rename our users to improve our company's username security policy. And that's just one of the problems. We'll be offline for a full 24 hours in April to handle changing the value (for necessary business reasons) of the natural primary key in the client table.

I'm all for constraints, especially uniqueness constraints, where appropriate. You can use unique indexes; you don't have to use natural keys as primary keys. Data is bound to change, so make that change possible.

Re: SQL Keys in Depth

#149

Post by greymeister »

RobertRoberts wrote: post #129
extrapickles wrote: post #91

Earlier quoted context omitted.

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…

We had an application specific reason we needed to use them instead of numerics and used this method which helps somewhat: https://www.percona.com/blog/2014/12/19/store-uuid-optimized...

Re: SQL Keys in Depth

#150

Post by dahauns »

mickronome wrote: post #122
sparkie wrote: post #116

Earlier quoted context omitted.

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…

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.
Post reply on HN