We do not use foreign keys (2016) • Hacker News Board

Re: We do not use foreign keys (2016)

#71

Post by corebit » Fri, Nov 08, 2019, 9:40 PM UTC

taurath wrote: post #25

conroy wrote: post #5

When posts like these come up, I'd like to remind people that context matters when making technical decisions. What works for large companies with huge scale (GitHub, Google, Facebook) may not work for you. As a counter point to the linked issue, I operate a few small applications. Foreign-keys (and constraints in general) are great at ensuring that invalid data doesn't find its way into your database. Yes, they have…

In the same vein, I'd like to remind people that you are probably not a "temporarily low-scale big-data company", in the same vein as a temporarily embarrassed millionaire. In lots of cases going for the very long term scalable solution will be an impediment to your growth, and I'd suggest dealing with those issues when the chance that you need them is on the horizon, rather than across the globe. CQRS is one of the…

The biggest issue with CQRS I've seen is people thinking CQRS means you need multiple, duplicate data structures, mappers, a few Kafka topics and a PhD, when IN REALITY all it means is you put methods that return data without modifying it in one interface/class and methods that have side effects in another interface/class - which is really just a good application of interface segregation.

Moreover, you now have a great rule of thumb for your brand new junior developers: "If you're handling a GET request, use this class, and if not use this other class" making code reviews easier to do and helping to enforce better structure in your code. Plus tons of other benefits.

The rest of that stuff that isn't CQRS you might eventually need if you scale, but is easy to add once you have the bones in place.

Re: We do not use foreign keys (2016)

#72

Post by Hamuko » Fri, Nov 08, 2019, 9:40 PM UTC

fdr wrote: post #9

There seems to be a certain mysql tinge to this value calculation. I use foreign keys and like it (I use Postgres). I do not encounter the problems the author does. I omit them when they are a problem for some reason. This is rare in routine work.

I'm gonna wager a guess and say that most MySQL users do use foreign keys without a problem. Most MySQL users are not running Github scale.

Re: We do not use foreign keys (2016)

#73

Post by emergie » Fri, Nov 08, 2019, 9:40 PM UTC

PopeDotNinja wrote: post #22

Because I've never thought about this, I'll ask the dumb question... A shopping cart has many items. An item belongs to a shopping cart. In a relational database, without foreign keys, how do you associate the shopping cart with the items?

you are talking about relation, in this context fk is an abstract concept

linked article describes fk as an dbms construct - a schema constraint

relations and conceptual foreign keys may exist without dmbs constraints, in such scenario dmbs does not guard validity of conceptual "foreign keys" and treats them as usual data

for example consider schema:

user{id,name}; book{id,title}; library_borrow{id,user_id,book_id,date}

if you want you may define a constraint:

ALTER TABLE library_borrow ADD CONSTRAINT FOREIGN KEY (user_id) REFERENCES user(id) CONSTRAINT fk_library_borrow_user_id;

if you do not define such constraint then dbms will not verify existence of records in `user` table when you insert data in library_borrow

absence of constraints affects also indexes - if you do not define a constraint you usually have to manually define an index on fk column

Re: We do not use foreign keys (2016)

#74

Post by edlebert » Fri, Nov 08, 2019, 9:41 PM UTC

Also, to be noted, Github was a Rails app since long before there was native support for adding foreign keys. I've worked on a lot of large monorails that were created in Rails' early days and none of them have foreign keys.

Re: We do not use foreign keys (2016)

#75

Post by whalesalad » Fri, Nov 08, 2019, 9:42 PM UTC

spookthesunset wrote: post #27

The number of times I’ve seen serious data corruption because “foreign keys are bad and we can just enforce it in code” is amazing. There is zero excuse to not use FK’s Any database that doesn’t use FK’s is almost guaranteed to have crap in it that didn’t get cleaned up, resulting in data corruption (and yes, dangling stuff in tables count as data corruption). Developers aren’t perfect. Shit will slip through even wi…

Eh. The issue arises when you have a very poorly designed schema and missing application-level operations to perform cleanup and deletion.

You don’t want to be caught in that situation because then you are handcuffed and cannot clean the database properly. For example, from the perspective of business operations you might have something that creates 1 single thing where under the hood 5+ DB objects are all created in a transactional manner and a very specific order. So how do you unroll that? You gotta carefully construct the rollback commands or just get rid of the bowling lane bumpers and slash and burn.

So you gotta commit and go all-in. If you make the choice to do it all within the app later, at least you keep that flexibility.

It all depends on your processes and your team. If you don’t design things properly and institute the correct procedures for designing and operating the DB, having or not having constraints at the DB level is inconsequential to the other issues you will face.

Re: We do not use foreign keys (2016)

#76

Post by chase-seibert » Fri, Nov 08, 2019, 9:42 PM UTC

This decision makes sense from the perspective of the person who owns doing database migrations, or who owns the performance of the database.

However, the other 99% of the engineers in the company end up dealing with the fallout of not having FKs -- more bugs, and slower development velocity.

One alternative is to have FKs in smaller, sharded databases, where you don't have to migrate between shards. For example, all the database rows related to one repo could be co-located in a shared with strong FKs between them.

Re: We do not use foreign keys (2016)

#77

Post by mdavidn » Fri, Nov 08, 2019, 9:43 PM UTC

munk-a wrote: post #68

spookthesunset wrote: post #46

Earlier quoted context omitted.

FK’s don’t just maintain “stronger” data integrity, they are the only way to maintain relational data integrity. Application code cannot maintain that relational integrity, period. Any developer who thinks application code can enforce relational integrity is naive and does not understand relational database systems. It is impossible for any layer above the database itself to keep things from getting corrupt.

I agree with your general gist, but it's not impossible, you just, essentially, are writing the engine yourself. I've seen a few applications that keep some data hot-loaded into a shared slice of memory and have tight controls over the altering and saving of that data - and that's sort of one of the easier ways to partially avoid a full reliance on FKs, if you want to use write-through caching then you're essentially…

Unless you're working with immutable primary keys (i.e. the values can neither be updated nor deleted), it is actually not possible to reliably enforce a foreign key constraint outside the database.

Re: We do not use foreign keys (2016)

#78

Post by sturgill » Fri, Nov 08, 2019, 9:43 PM UTC

DaiPlusPlus wrote: post #20

conroy wrote: post #5

When posts like these come up, I'd like to remind people that context matters when making technical decisions. What works for large companies with huge scale (GitHub, Google, Facebook) may not work for you. As a counter point to the linked issue, I operate a few small applications. Foreign-keys (and constraints in general) are great at ensuring that invalid data doesn't find its way into your database. Yes, they have…

As someone currently fighting a battle with an LoB application written without foreign-key constraints with hundreds of thousands of rows of corrupted data because of bugs in sprocs that assigned the wrong value to the wrong foreign key column because they were similarly named - THIS! The reply in the GitHub thread we’re talking about makes it clear that they still perform FK validation - it’s just performed in the a…

I’m pretty confident that GitHub doesn’t use foreign keys because it was built as a Rails app. And the “Rails Way” is to create these constraints in the model. Foreign key constraints weren’t a first-class member in Rails until v4 (if memory serves correctly).

I was once a full-time Rails dev and really loved the framework (I don’t write as many user facing applications these days). Most of the Omakase trade offs didn’t bother me. But I never understood the disdain for foreign keys. For 99.99% of web apps, you want them (dare I say, need them).

Even in Rails 3 I would add them by hand in the migration files. Very few applications will ever actually care about sharding. We were pulling in millions at the last Rails company I worked for and were just fine with a master and a single replica.

If you get to a point where sharding is best for your company you hopefully have enough revenue coming in to fund a data transition. Your goal should be to outgrow what MySQL (or Postgres) can do for you in master/replica mode. If you do, you’ll likely be independently wealthy...

If data integrity matters at all, model-based checks (or periodic queries for orphaned data) will not suffice. Just put a foreign key in the table where it belongs and let your DB do what it does best. ACID is an amazing thing...

Re: We do not use foreign keys (2016)

#79

Post by gamblor956 » Fri, Nov 08, 2019, 9:44 PM UTC

aeyes wrote: post #42

PopeDotNinja wrote: post #22

Because I've never thought about this, I'll ask the dumb question... A shopping cart has many items. An item belongs to a shopping cart. In a relational database, without foreign keys, how do you associate the shopping cart with the items?

There is no difference when not having foreign key constraints. In both cases you can't delete items from your catalog or you'd break old carts. So you just don't do it. Another way is to copy the product from the product table to the cart table. This saves you from making references to versions of products.

An item should never be deleted from a catalog. That would break a lot more than carts.

If you need to track the inventory status/availability of an item, then you store that in the database. The specific implementation (field, list of current items, etc) depends on your needs.

Re: We do not use foreign keys (2016)

#80

Post by mixedCase » Fri, Nov 08, 2019, 9:44 PM UTC

spookthesunset wrote: post #62

dkhenry wrote: post #44

Earlier quoted context omitted.

Every time I have seen a database use foreign keys there has been data corruption, because everyone thought foreign keys were declarative and not procedural. Just because you have a foreign key doesn't mean it was always there or that it applied on every transaction. You can turn them off at the connection level and you in fact must turn them off for almost any kind of bulk data load. You should read shlomi's post he…

Maybe shitty toy database systems like MySQL let you disable constraints on a per session basis. A real database system might let you defer them until the end of a transaction—which is the only correct way to operate. Once you commit that transaction, what you put into the system better fucking make sense and it is the job of the database system (and only the database system) to enforce that. Like I said before if yo…

> Maybe shitty toy database systems like MySQL

While appropiate to define in few words some of MySQL's colossal mistakes, this isn't the kind of language that will sway heads that have been comfortably using MySQL because those defects are just "what DB's do".