Hacker News Reader

Comment by vp8989 | Hacker News Reader

In theory it is good practice, but in practice enforcing referential integrity is not free and it makes satisfying other non-functional requirements harder. Engineering is about tradeoffs. Foreign keys are overkill in modern architectures where DBs/tables are typically only written to by a single application. They solve a problem that lots of people don't really have anymore.

View on HN

In the legacy project our team inherited, there's a lot of data consistency issues stemming from the lack of foreign keys (we're adding them back now). Data consistency is important because we deal with financial data. Typically, code and data change more often than DB schemas, so every release has a chance that someone will forget to update all relations manually in code, introducing dangling references, for example. Foreign keys help as an additional safety measure, because they don't change as often. I think you should care about the performance of foreign keys only when speed is more important than data consistency. It's a good default.

Personally, I thinks foreign keys are great in dev and bad at any scale that you have many db servers.

And as you point out, there are exceptions, like financial data. But not marketing funnels where you might throw everything away.

You should probably have different types of engineers working on such different projects as well.

> DBs/tables are typically only written to by a single application

I’ve seen several teams regret making this assumption. Unless you enforce draconian access control over a database, you’re going to discover that customer support and accounting and biz dev have been quietly relying on it.

Reply on news.ycombinator.com