3 ms·

"Write good code and you won't have bugs" :) I think it's good practice to enforce consistency rules both in the DB and in code. If you make a mistake in your

by kgeist 4y ago

"Write good code and you won't have bugs" :) I think it's good practice to enforce consistency rules both in the DB and in code. If you make a mistake in your code, the DB won't allow it, and vice versa.

vp8989 4y ago
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.
- kgeist 4y ago
  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.
  - sroussey 4y ago
    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.
- erik_seaberg 4y ago
  > 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.
baronvonsp 4y ago
The exact analogy that came to mind for me. Plus, relational databases don't just sit under a single application. There's usually multiple applications/services talking to them. Worse, humans connect to them an do all sorts of things they shouldn't do. That's the whole point of managing referential integrity in the DBMS, since you can only control "just write good code" across so many application domains. Of course whether the performance tradeoff is worth it is a complicated decision for many of the reasons people have mentioned. But in 20 years of working with relational databases at big companies, I've seen few examples where the performance win exceeded the business risk.

by kgeist 4y ago

vp8989 4y ago

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.

kgeist 4y ago
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.
- sroussey 4y ago
  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.
erik_seaberg 4y ago
> 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.

baronvonsp 4y ago

The exact analogy that came to mind for me. Plus, relational databases don't just sit under a single application. There's usually multiple applications/services talking to them. Worse, humans connect to them an do all sorts of things they shouldn't do. That's the whole point of managing referential integrity in the DBMS, since you can only control "just write good code" across so many application domains. Of course whether the performance tradeoff is worth it is a complicated decision for many of the reasons people have mentioned. But in 20 years of working with relational databases at big companies, I've seen few examples where the performance win exceeded the business risk.