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

Ask HN: Do you use foreign keys in relational databases?

news.ycombinator.com

Post reply on HN
Print view View original

191–200 of 251 posts

Re: Ask HN: Do you use foreign keys in relational databases?

#191

Post by sgjohnson »

habibur wrote: post #175
sgjohnson wrote: post #138

Earlier quoted context omitted.

And how do you differentiate between an absence of value and empty value? it sounds like you're just using the database wrong.

That's the fundamental argument supporting the use of NULL. Extending this one will require you to use "infinite types of NULLs". Decades ago there was an article written explaining it, and that turned into a meme for a while. For example : Guess you set up a "collector database" that collects data from other databases. It might don't know what the other database's field value is, (call this situation "NULL", the cla…

> NULL = Program haven't read the database yet and doesn't know. NULL1 = Program have read the database and it's null. NULL2 = it was null from where this collector database read this data.

It's not a valid computing problem. In your example there's no difference whatsoever between NULL1 and NULL2, because it doesn't matter where it came from, and as for the difference between NULL and NULL1 - it's irrelevant, because if we know that the field exists, we have the database.

> "decades ago"

I'm fairly sure nobody needed more than 1 type of NULL decades ago, and certainly nobody needs more than 1 type of NULL now.

Re: Ask HN: Do you use foreign keys in relational databases?

#192

Post by NoSorryCannot »

AdamN wrote: post #125
sonthonax wrote: post #108

Earlier quoted context omitted.

> Another classic is the “joins are slow” argument The only person I knew who died on that hill would insist on doing two queries to the database, and then would insist on doing a client side cartesian join.

I remember getting beers with somebody in the aughts who claimed that he saw an entire website where the url was the key and the webpage was the value in an Oracle database. Any code was SQL operations inside the value field.

That's amazing!

Re: Ask HN: Do you use foreign keys in relational databases?

#193

Post by skyde »

radicality wrote: post #184
mtts wrote: post #31

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

facebook data model is a Graph where each row store one object “comment” or one association “comment is with post id” between objects .

They made an query and indexing system on top of it to make it fast called TAO.

Without it you need to send a distinct SQL query pet parent object to get list of associated child object which would be awfuly slow.

Re: Ask HN: Do you use foreign keys in relational databases?

#194

Post by Spivak »

pif wrote: post #180
Spivak wrote: post #164

Earlier quoted context omitted.

Eh, there are a lot of people who don't like using FK constraints, calling them all idiots is just bad faith and ignores the reasons they did it. Just because you can enforce a constraint at a specific layer doesn't mean you have to. DB people love shoving all sorts of application logic into the DB and there are good arguments to do it as well as downsides. App people sometimes prefer to do everything in the app and…

> DB people love shoving all sorts of application logic I agree that application logic goes into the application, but data integrity is NOT application logic.

Sure it is! As a thought experiment consider evil dba whose job it is to crash your application or make it do wrong things just by manipulating the data in the DB but following the constraints. Totally trivial, right? So data integrity is at all times the responsibility of both the app and the db. And the set of constraints you can enforce with the app will always be a superset of what can be enforced by the db. And for some cases (usually when the db is private to the application) it's easier to build that logic into the app and for other cases (multiple apps sharing a db) it's easier to build that logic into the db.

Re: Ask HN: Do you use foreign keys in relational databases?

#195

Post by fabian2k »

pc86 wrote: post #165
Antshockey wrote: post #105

Earlier quoted context omitted.

Funnily, one of my pet peeves is people worrying about the SQL Generated from EF Linq. If you care that much about it I think you should just be writing the SQL by hand.

I might even go a step farther - you shouldn't care . It's the equivalent of caring whether or not your generated HTML or compiled IL or Assembly "looks nice." If the SQL is performant and it returns the expected data, that is good enough for 99.9% of cases.

There are cases where you have to care, e.g. the difference between AsSingleQuery() and AsSplitQuery() in EF Core. This option only affects what kind of queries EF Core will create to perform the same job, but it can have pretty significant implications on performance in some cases, and it can affect the consistency guarantees you get for your results.

Re: Ask HN: Do you use foreign keys in relational databases?

#197

Post by habibur »

sgjohnson wrote: post #191
habibur wrote: post #175

Earlier quoted context omitted.

That's the fundamental argument supporting the use of NULL. Extending this one will require you to use "infinite types of NULLs". Decades ago there was an article written explaining it, and that turned into a meme for a while. For example : Guess you set up a "collector database" that collects data from other databases. It might don't know what the other database's field value is, (call this situation "NULL", the cla…

> NULL = Program haven't read the database yet and doesn't know. NULL1 = Program have read the database and it's null. NULL2 = it was null from where this collector database read this data. It's not a valid computing problem. In your example there's no difference whatsoever between NULL1 and NULL2, because it doesn't matter where it came from, and as for the difference between NULL and NULL1 - it's irrelevant, becaus…

Indeed.

Lesson learned.

Thanks.

Re: Ask HN: Do you use foreign keys in relational databases?

#198

Post by Gordonjcp »

mtts wrote: post #31

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

> in fact RDBMSes are highly optimized pieces of software that are especially good at combining sets of data. Much better than ORMs, anyway,

ORMs are just a wrapper around RDBMSes. If your ORM is producing incredibly stupid SQL to query the DB with, you might want to check that you're not modelling your data in a stupid way.

I am by no means an expert, but in general I have found that if the ORM is doing something particularly crazy, it's because my underlying assumptions about the data model is wrong.

Re: Ask HN: Do you use foreign keys in relational databases?

#199

Post by lupire »

sonthonax wrote: post #108
mtts wrote: post #31

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

> Another classic is the “joins are slow” argument The only person I knew who died on that hill would insist on doing two queries to the database, and then would insist on doing a client side cartesian join.

To be fair thata a reasonable approach if the database is at its monolithic scaling limit in CPU but not IO, while the clients can scale horizontally to more machines.

Unlikely in practice, though.

Re: Ask HN: Do you use foreign keys in relational databases?

#200

Post by saint_abroad »

bartread wrote: post #155

My other beef with no foreign keys is that it makes a database a lot harder to understand. There are any number of tools that will generate me a pretty and useful database schema diagram if I point them at a relational database. This is incredibly handy when you're new to a database and need to figure out which tables to query and update, and which (gasp) sprocs you need to call. I've been on projects where people ha…

> There are any number of tools that will generate me a pretty and useful database schema diagram if I point them at a relational database.

I'd even go so far as to design database schema with these ER diagram tools in mind: if the automatic diagram is messy then that's more-often-than-not a code-smell in need of refactoring (before being hit with production data).

Post reply on HN