5 ms·

> for managing relationships between data than a foreign key. You're conflating the concept of a normalized database with insanely slow DB-enforced referential

> for managing relationships between data than a foreign key. You're conflating the concept of a normalized database with insanely slow DB-enforced referential integrity/foreign keys. Toy example? Sure. Take a well-formed 3NF schema and disable foreign key constraints.

vecter 4y ago
Sorry, assume I'm dense. > Take a well-formed 3NF schema and disable foreign key constraints. I'm familiar with 3NF, but can you expand on how 3NF enables you to remove foreign keys? Or feel free to point me to an article/blog, I don't want to waste your time if it's too much to explain. I did some googling but wasn't sure where to proceed from your post.
- berkle4455 4y ago
  Say you have a super basic setup: user = {user_id, email} order = {order_id, user_id} order.user_id is a foreign key to user.user_id. That's a perfectly valid and reasonable way to organize things. Enabling RDBMS-enforced foreign key constraints is the issue. It slows everything down dramatically.
  - vecter 4y ago
    I see, thanks. Basically just store the foreign keys yourself as columns in relevant tables and perform the joins in SQL without having the DB enforce FK integrity for every insertion/update/delete. Would it be fair to say that read speeds are unaffected by this?
    - berkle4455 4y ago
      Yes. Quite literally the only difference is not enabling foreign key constraints. Read speeds unaffected correct.
  - kgeist 4y ago
    >It slows everything down dramatically How slower is it really? MySQL automically creates indexes for foreign keys so I don't think it slows down "dramatically", just an additional indexed retrieval? Do you know of any benchmarks which show the difference?
- Philip-J-Fry 4y ago
  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
How does this stay correct in the presence of concurrent activity?
- berkle4455 4y ago
  Using transactions or UUID/ULIDs though maybe I’m misunderstandingyour question. How do foreign key constraints help with concurrency?
  - AlisdairO 4y ago
    Table User: userid, etc Table Resources: resourceid, userid, etc If I want to restrict deletion of a user to only be possible after all the resources are deleted, I'm forced into using higher-than-default isolation levels in most DBs. This has significant performance implications. It's also much easier to make a mistake - for example, if when creating a resource I check that the user exists prior to starting the transaction, then start the tran, then do the work, it will allow insertion of data into a nonexistent user.
    - edf13 4y ago
      Add your user check as a where clause on the resources insert?
      - AlisdairO 4y ago
        Can you give an example? I’m not aware of a mechanism like that that will protect you from concurrency artifacts reliably - certainly not a general one.
        drwiggly 4y ago
        start transaction; select id from users where id = ? for update; if row_count() < 1 then raise 'no user' end if; insert into sub_resource (owner, thing) values (?, ?); commit; ??

vecter 4y ago

Sorry, assume I'm dense. > Take a well-formed 3NF schema and disable foreign key constraints. I'm familiar with 3NF, but can you expand on how 3NF enables you to remove foreign keys? Or feel free to point me to an article/blog, I don't want to waste your time if it's too much to explain. I did some googling but wasn't sure where to proceed from your post.

berkle4455 4y ago
Say you have a super basic setup: user = {user_id, email} order = {order_id, user_id} order.user_id is a foreign key to user.user_id. That's a perfectly valid and reasonable way to organize things. Enabling RDBMS-enforced foreign key constraints is the issue. It slows everything down dramatically.
- vecter 4y ago
  I see, thanks. Basically just store the foreign keys yourself as columns in relevant tables and perform the joins in SQL without having the DB enforce FK integrity for every insertion/update/delete. Would it be fair to say that read speeds are unaffected by this?
  - berkle4455 4y ago
    Yes. Quite literally the only difference is not enabling foreign key constraints. Read speeds unaffected correct.
- kgeist 4y ago
  >It slows everything down dramatically How slower is it really? MySQL automically creates indexes for foreign keys so I don't think it slows down "dramatically", just an additional indexed retrieval? Do you know of any benchmarks which show the difference?
Philip-J-Fry 4y ago
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

How does this stay correct in the presence of concurrent activity?

berkle4455 4y ago
Using transactions or UUID/ULIDs though maybe I’m misunderstandingyour question. How do foreign key constraints help with concurrency?
- AlisdairO 4y ago
  Table User: userid, etc Table Resources: resourceid, userid, etc If I want to restrict deletion of a user to only be possible after all the resources are deleted, I'm forced into using higher-than-default isolation levels in most DBs. This has significant performance implications. It's also much easier to make a mistake - for example, if when creating a resource I check that the user exists prior to starting the transaction, then start the tran, then do the work, it will allow insertion of data into a nonexistent user.
  - edf13 4y ago
    Add your user check as a where clause on the resources insert?
    - AlisdairO 4y ago
      Can you give an example? I’m not aware of a mechanism like that that will protect you from concurrency artifacts reliably - certainly not a general one.
      - drwiggly 4y ago
        start transaction; select id from users where id = ? for update; if row_count() < 1 then raise 'no user' end if; insert into sub_resource (owner, thing) values (?, ?); commit; ??