Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We do not use foreign keys (2016)

github.com

Post reply on HN
Print view View original

331–337 of 337 posts

Re: We do not use foreign keys (2016)

#331

Post by ngrilly »

evanelias wrote: post #325
ngrilly wrote: post #311

Earlier quoted context omitted.

> For example, while Facebook's largest db tier goes through a dao / writethru cache, there's plenty of other use-cases that are direct MySQL usage. I didn't know about that. That's interesting! > And in any case, why does it matter if there's another layer involved? I was writing that in the context of the parent comment about "disabling constraints". I can see why disabling constraints makes sense in a sharded envi…

To explain more re: FB and having another layer on top of MySQL, there are a bunch of separate sharded MySQL tiers there. It's split by workload -- for example, the access pattern, schema, and sharding key differs completely between the main social graph, Messenger data, ad market, financial transaction data, etc. And then there's also the internal MySQL database-as-a-service, which allows any engineer to provision o…

Thanks for sharing your experience here!

> Without that feature, these tools would need to construct a dependency graph and create tables in a specific order (and/or defer FK creation until after the tables)

This problem alone justifies the ability to temporarily disable constraints. I notice PostgreSQL, which is a toy too ^__^, offers something similar.

> Go with Postgres for use-cases where solid geospatial, OLAP, or fulltext are core requirements and/or there's a desire to minimize the number of different data stores

I agree that PostgreSQL is a really good match for these use cases.

Great point about Linux and FreeBSD ;)

Re: We do not use foreign keys (2016)

#332

Post by yellowapple »

Ma8ee wrote: post #329
yellowapple wrote: post #326

Earlier quoted context omitted.

That all seems pretty reasonable to me, and darn well should be included when "test[ing] invoicing": - You surely want to make sure invoice creation depends on a valid billing address at the very least, right? If that breaks, you're gonna have a lot of rather irate AR clerks. - You surely want to make sure the Contact is aware of the new invoice on the order, right? In fact, that might very well be part of the Contac…

I don't think we disagree about much. My only objection was to > then it shouldn't be hard to generate the dependent data while you're at it which makes in sound like somewhat light work. My point was only that it isn't. And of course we script a lot of this test-data-creation, but then you need different kind of data for different tests, and need to add some flexibility. And after a while, just generating test data…

One trick here is that if you're integration-testing, your tests are hopefully already generating this data for you (if not, then you should probably be writing more tests, lol), in which case you'd make the tests you do want to run dependent on the tests which would generate that data (for example, the Invoice tests would be dependent on the Sales Order and Customer and Product and Address and Contact and Sales Rep and etc. tests, so those tests will generate the requisite data, and the Invoice tests will use that data to create and test Invoices).

Re: We do not use foreign keys (2016)

#333

Post by munk-a »

spookthesunset wrote: post #112
munk-a wrote: post #99

Earlier quoted context omitted.

Oh I absolutely agree and even when a DB is properly configured with references all cleared defined and constrained it's absolutely a good UX thing to pre-check as much as possible. But, beyond that, it is quite possible to remove FK checks and still have strong guarantees about data integrity. It is stupidly expensive and unless you have a few billion in the bank there is absolutely no reason to even consider it, bu…

Being pedantic in this case doesn’t help the cause. Too many developers don’t understand database theory at all and will read this > But, beyond that, it is quite possible to remove FK checks and still have strong guarantees about data integrity And not the rest of your post. Yes technically you are right but it is stupidly expensive and nobody should do it. The problem with being technically correct is, again, peopl…

I feel like reading HN should come with a warning on the tin that "If you're reading a long technical comment, taking away just part of it is dangerous" - were I speaking to someone in the business side of a corp that asked "Hey do we need these FK things, some developers have been saying they're slow" I'd say 'Yes, we absolutely do need FKs' then go on to talk to the developers, double check I wasn't at one of the about dozen of companies with data at a scale that FKs as implemented in RDBMSes (especially postgres, mysql tends to drop off in performance much easier without heavy tweaking) is insufficient, and then tell them that FKs do work and they probably really just need to read up a lot on indexes and stop throwing around table locks like it's christmas.

Re: We do not use foreign keys (2016)

#334

Post by Lord_Zero »

trhway wrote: post #282
Quekid5 wrote: post #215

Earlier quoted context omitted.

Like you, I've found the general idea of CQRS/ES[0] incredibly valuable because it both enforces a functional approach to state (e.g. 'current state' is a fold over events), and it forces people to really think about the domain. (E.g. which bits are really atomic units, or what can be fixed up if it goes wrong in some way.) It also forces people to think about something that's usually glossed over: Consistency. If yo…

> any responsible application should track when that data was read and reject updates if the backing data has changed since it was read... but very few applications even attempt to do this (it's really hard and a huge amount of boilerplate with most APIs). it is done very simple by versioning. Usually implemented transparently for the data layer API clients.

SQL Server has a built in ROWVERSION type which plays really nicely with EF Core. Made implementing concurrency checks easy.

Re: We do not use foreign keys (2016)

#335

Post by Lord_Zero »

redact207 wrote: post #229
rhinoceraptor wrote: post #144

Earlier quoted context omitted.

I had a job where the database was intentionally lacking FKs so that users could input data out of order, for example create a shipping route Foo that goes to a warehouse Bar, before the warehouse existed. Let's just say it was a suboptimal design.

this just sounds like eventual consistency which can form part of a perfectly optimal design

Or "eventual" never comes and you end up with a bunch of inconsistent data.

Re: We do not use foreign keys (2016)

#336

Post by Quekid5 »

trhway wrote: post #282
Quekid5 wrote: post #215

Earlier quoted context omitted.

Like you, I've found the general idea of CQRS/ES[0] incredibly valuable because it both enforces a functional approach to state (e.g. 'current state' is a fold over events), and it forces people to really think about the domain. (E.g. which bits are really atomic units, or what can be fixed up if it goes wrong in some way.) It also forces people to think about something that's usually glossed over: Consistency. If yo…

> any responsible application should track when that data was read and reject updates if the backing data has changed since it was read... but very few applications even attempt to do this (it's really hard and a huge amount of boilerplate with most APIs). it is done very simple by versioning. Usually implemented transparently for the data layer API clients.

It's simple. I'm just saying that (for most frameworks) it's a lot of work and boilerplate. It's also easy to miss individual cases during code review, etc.

Re: We do not use foreign keys (2016)

#337

Post by lensopra »

Quekid5 wrote: post #336
trhway wrote: post #282

Earlier quoted context omitted.

> any responsible application should track when that data was read and reject updates if the backing data has changed since it was read... but very few applications even attempt to do this (it's really hard and a huge amount of boilerplate with most APIs). it is done very simple by versioning. Usually implemented transparently for the data layer API clients.

It's simple. I'm just saying that (for most frameworks) it's a lot of work and boilerplate. It's also easy to miss individual cases during code review, etc.

You kind of miss the part where he says "Usually implemented transparently for the data layer API clients." Transparently as in no work, no boilerplate.

I guess your mileage might vary, but Java has JPA/Hibernate, and .NET has Entity Framework, and they both make it easy, so I'm going to be surprised if any major framework or language doesn't make this easy.

The concept is also called optimistic locking, if that makes Googling it easier. Using that term, I easily found that Node.js's Sequelize supports it too https://sequelize.org/v5/manual/models-definition.html#optim...

Post reply on HN