3 ms·

They're not saying 3NF enables you to remove foreign keys. They are talking about removing foreign key constraints from your RDBMS of choice. Something like SQ

They're not saying 3NF enables you to remove foreign keys. They are talking about removing foreign key constraints from your RDBMS of choice. Something like SQL Server can enforce foreign key constraints if you explicitly tell it your relationships between tables. The downside is that having this referential integrity costs you performance as the database has to check your relations when inserting/updating/deleting rows. E.g. checking that a foreign key is pointing at a valid primary key, checking that you aren't leaving invalid foreign keys when deleting a primary key, etc. This is to prevent you inserting bad data into the database. You can delete these constraints and still have the exact same behavior so long as your code is correct. It just means that the database isn't going to stop you writing bad data.

vecter 4y ago
That makes sense. For workloads where write performance isn't very important but read performance is, this sounds like it may still be a worthwhile tradeoff to have that extra level of data integrity. My sense is that many typical CRUD apps aren't writing gargantuan volumes of data or making very complex edits, and if they do, it's ok if it takes a second longer. Usually read speed is more of a bottleneck for user-facing applications, but I'm sure there are probably some examples where this tradeoff is worth it.
AlisdairO 4y ago
> so long as your code is correct This is a pretty tough definition of correct, though. Without foreign key constraints you'll have a really tough time dealing with concurrency artifacts without raising your isolation levels, which generally brings larger performance concerns.
- remram 4y ago
  Why do you think that? My experience is that if you have a moderate amount of foreign keys, a lot of DBMS (not Postgres) will refuse the `ON DELETE CASCADE` (in the diamond case), and you have to do it "manually" anyway (from your query builder).
  - AlisdairO 4y ago
    I think it because a significant fraction of my career has been spent fixing db-concurrency-related mistakes for people once they hit scale :-). I’m not talking about using cascade - this applies perfectly well to use of on delete restrict. FKs are more or less the only standard way to reliably keep relationships between tables correct without raising up the isolation level (at least, in most dbs) or doing explicit locking schemes that would be slower than the implicit locking that foreign keys perform.

vecter 4y ago

That makes sense. For workloads where write performance isn't very important but read performance is, this sounds like it may still be a worthwhile tradeoff to have that extra level of data integrity. My sense is that many typical CRUD apps aren't writing gargantuan volumes of data or making very complex edits, and if they do, it's ok if it takes a second longer. Usually read speed is more of a bottleneck for user-facing applications, but I'm sure there are probably some examples where this tradeoff is worth it.

AlisdairO 4y ago

> so long as your code is correct This is a pretty tough definition of correct, though. Without foreign key constraints you'll have a really tough time dealing with concurrency artifacts without raising your isolation levels, which generally brings larger performance concerns.

remram 4y ago
Why do you think that? My experience is that if you have a moderate amount of foreign keys, a lot of DBMS (not Postgres) will refuse the `ON DELETE CASCADE` (in the diamond case), and you have to do it "manually" anyway (from your query builder).
- AlisdairO 4y ago
  I think it because a significant fraction of my career has been spent fixing db-concurrency-related mistakes for people once they hit scale :-). I’m not talking about using cascade - this applies perfectly well to use of on delete restrict. FKs are more or less the only standard way to reliably keep relationships between tables correct without raising up the isolation level (at least, in most dbs) or doing explicit locking schemes that would be slower than the implicit locking that foreign keys perform.