I can’t imagine not using FKs. Data integrity is more important than smooth migrations and if that’s the issue then he needs to learn more.
Ask HN: Do you use foreign keys in relational databases?
181–190 of 251 posts
Re: Ask HN: Do you use foreign keys in relational databases?
#182> He rather have a smooth data migration process than having an unexpected error and abort
Personally, I would prefer errors to become immediately obvious, while I'm active and there's a roll-back ready to be applied.
Re: Ask HN: Do you use foreign keys in relational databases?
#183Earlier quoted context omitted.
I think the author is talking about 'foreign key constraints' - You could have foreign keys without enforcing a constraint. Personally, I don't use foreign key constraints because: 1. It makes schema migrations and other data-management operations more difficult. 2. On insertion, the database needs to perform an additional check to verify that the record exists at the foreign key; this carries a performance cost; IMO…
> I prefer to assemble data on the front end as much as possible because it allows my REST API calls to be granular It's clear you have never work with a lot of data. > The real reason people use joins is because they want to pack a lot of details onto the user's screen I hate this illusion that web programming is the whole of software development.
> It's clear you have never work with a lot of data.
Sure, I only wrote an open source distributed pub/sub system with channel-based sharding which has been used by thousands of companies to support hundreds of thousands of concurrent users, but I guess 'lots of data' is a relative term.
Re: Ask HN: Do you use foreign keys in relational databases?
#184Fear of RDBMSes is quite common. I used to suffer from it too. It’s just so annoying to have to switch your brain to a different programming paradigm every time you need to do something with the database that you start to make up all sorts of excuses as to why it’s really just better to “do it in the code”. Your coworkers argument about FKs making data migrations difficult is one of them. Another classic is the “join…
A lot of it depends on the use case. For example, Facebook - one of the largest (if not the largest) deployments of mysql does not allow any FK constrains. There’s multiple reasons, but one of those is better predictability of db operational perf - a row delete should delete just the row and not potentially trigger N cascading deletes.
Re: Ask HN: Do you use foreign keys in relational databases?
#185I prefer relational databases for most cases unless I really need to use a non-relational store. I also use foreign keys whenever possible. From what I have seen, anything to enforce referential integrity is a benefit in most cases. It is more comforting to have a delete fail than have many other queries mysteriously fail down the line.
In most cases, a well designed database covers a multitude of sins and makes life for future you and your future team much easier. Some of the biggest dumpster fires of code I have seen started as a smoldering dumpster from terrible db choices that were made far harder to fix when it had been in production for a few years.
If someone put a gun to a developer's head and threatened to pull the trigger if the database was crap, sadly they would be forced to pull the trigger most of the time.
Re: Ask HN: Do you use foreign keys in relational databases?
#186Earlier quoted context omitted.
> Personally, I typically would rather have the application layer do the validation and even the joining of data, a lot of the time, when the application is high-volume. At the volumes my organization works with, it is very difficult to write performant SQL queries that use JOINs and other relationships as a developer - even as a DBA! - and often much easier, for me, to write performant application code. There is bas…
We are absolutely talking about billions of records. But when you say "return excess data to the application" I'm not sure what you mean. Not doing lots of complex JOINs doesn't mean not filtering the queries at the DB at all. Nobody is pulling back a billion records at a time. Here's an example of what I'm talking about - read the first comment on https://www.brentozar.com/archive/2015/05/do-foreign-keys-ma... , ano…
> We are absolutely talking about billions of records.
> But when you say "return excess data to the application" I'm not sure what you mean. Not doing lots of complex JOINs doesn't mean not filtering the queries at the DB at all. Nobody is pulling back a billion records at a time.
The question then becomes "Are you gaining anything by not using foreign keys on the database?" What is the additional speed impact of JOINs actually costing you? How denormalized is your database already, if JOINs are costly?
If you want raw speed at billions-of-records scale, you want as flat a schema as you can get and good indexes are actually going to fit into RAM.
By that point though, you should be able to recognize your use case is not the 90% (or even 95%) case, and your specific requirements are driving doing something different. That's very different than the vague "high-volume" statement you made at first.
My experience has been in assisting clients in migrating to data warehouses and specifically during the heyday of Hive/Hadoop/Spark years ago in seeing clients mistakenly believe they were "big data" and go down the rabbit hole of trying to scale out before it was actually necessary. The problem I have with the vague notion of "high-volume" is that I saw clients with 500m records believe they fit the bill, as well as clients with as few as 20m records who thought the same. The reality is neither of them did and they wound up wasting a lot of money in pursuit of slower systems.
> Here's an example of what I'm talking about - read the first comment on [...], another venue for this same debate.
That's not an example though, that's just some vague statements about needing to profile your query and evaluate the costs for yourself, which should be rather obvious.
Re: Ask HN: Do you use foreign keys in relational databases?
#187prohibit using JOIN statements is really cargo cult :-)
Join is simply an index scan. And is guaranteed to be cheaper then the ORM having to send many request to the server.
Re: Ask HN: Do you use foreign keys in relational databases?
#188Fear of RDBMSes is quite common. I used to suffer from it too. It’s just so annoying to have to switch your brain to a different programming paradigm every time you need to do something with the database that you start to make up all sorts of excuses as to why it’s really just better to “do it in the code”. Your coworkers argument about FKs making data migrations difficult is one of them. Another classic is the “join…
> Your coworkers argument about FKs making data migrations difficult is one of them. Got any arguments to back up this bald assertion? In particular, I'd love to hear more about how to manage schema migrations on large tables with FK's without incurring lengthy locks or downtime. Betting the answer is going to involve some variation on "well, don't do that" which is when I'll rest my case.
There are tools for live migrations for most popular databases. Also a lot of Postgres DDL is very fast and/or capable of happening live.
Re: Ask HN: Do you use foreign keys in relational databases?
#189Fear of RDBMSes is quite common. I used to suffer from it too. It’s just so annoying to have to switch your brain to a different programming paradigm every time you need to do something with the database that you start to make up all sorts of excuses as to why it’s really just better to “do it in the code”. Your coworkers argument about FKs making data migrations difficult is one of them. Another classic is the “join…
A lot of it depends on the use case. For example, Facebook - one of the largest (if not the largest) deployments of mysql does not allow any FK constrains. There’s multiple reasons, but one of those is better predictability of db operational perf - a row delete should delete just the row and not potentially trigger N cascading deletes.
I don't understand “a row delete should delete just the row and not potentially trigger N cascading deletes”. If you want that to not happen, then define that in the database definition. It sounds like you're saying that a core piece of functionality is somehow ‘wrong’, even though that same functionality can be used to make the desired bahviour for this exact use case explicit?
Re: Ask HN: Do you use foreign keys in relational databases?
#190from https://thedailywtf.com/articles/directive-595
Dear Database Architect,
Directive 595 Part 2 is as follows.
"Foreign and Primary Key constraints give lack of flexibility, more
costly evolution, inhibit the use of the database acting as a
service to applications and make it an inhibitor to evolution."
Sincerely, Chief Architect Gerald