3 ms·

What would be the advantage of implementing it in the application? I only see disadvantages: - no data integrity check in the database - more complicated def

What would be the advantage of implementing it in the application? I only see disadvantages: - no data integrity check in the database - more complicated definition of foreign relation, or none at all, leaving possibility for deviant data - scatteted schema. Cant look at the db table and understand the entire model. Have to hunt in code an potentially across a lot of code. It just seems like a data integrity issue that needs to be enforced at the db level.

randomdata 3y ago
> What would be the advantage of implementing it in the application? It is always implemented in the application. The question is about whether you do it in your own application or use someone else's application. The disadvantage of doing it in your application is that you have to do the work that someone else has probably already done, and are likely not as great of a programmer as that other person, thus more likely to screw it up.
endisneigh 3y ago
It’s faster, you can do online schema migrations, but it’s hard to say unless a specific database is used as an example.
cogman10 3y ago
The main advantage is it's WAY faster if you are writing a lot. The big disadvantage of foreign keys is they do verify integrity. That means making lookups on every write/update which can be very costly especially as the model becomes more complex. If you have a read heavy application with low levels of writes then by all means put in foreign keys to you hearts content. But if you are at a point where you have millions or billions of records in multiple tables, they simply aren't feasible.
- mavelikara 3y ago
  But you pay the cost in checking it in the application, as GP said. If so, it simply is moving the cost from db to application layer. Is there a reason the checks can be implemented more efficiently in the application than the DB can?
  - endisneigh 3y ago
    The reason is because the application knows what’s actually happening with the data.
    - LtWorf 3y ago
      It's supposed to know, but it actually doesn't. At my first job they used mongodb for no reason other than it was the fad at the moment, with the big data and so on. There were often crashes in the application because our records followed several different schemas, because of bugs in the application that were later fixed, behaviour that got changed, ORM got replaced with hand written code that was much faster… Basically, what happens with over confident developers and CTO that think they are very good developers and aren't.
      - endisneigh 3y ago
        There are plenty of counter examples. It’s simply a fact that your application can be faster without foreign keys.
    - mavelikara 3y ago
      Your argument, if I understand it correctly, is that the constraints can be implemented in the application layer more efficiently - either in dev time or CPU time - than it can be in the DB layer. Why is that so, and under what circumstances? (Asking to learn, not to argue)
  - majkinetor 3y ago
    Yes there is although way more complex. U could use caching so not to hit db often and also app scales way easier than db.
  - butlerm 3y ago
    Referential integrity problems usually happen due to missing deletes, improper deletes, or references that should be cleared. The overhead of checking for the existence of referred to records in ordinary inserts and updates in application code is unnecessary in most cases, and that is where the problem is. Either you have to check to have any idea what is going on, because your key values are being supplied from an outside source or you should be able write your application so that it does not insert random references into your database. If you actually need to delete a row that might be referred to, the best thing to do is not to do that, because you will need application level checks to make the reason why you cannot delete something visible in any case. 'Delete failed because the record is referred to somewhere' is usually an inadequate explanation. The application should probably check so that delete isn't even presented as an option in cases like that.
- dkjaudyeqooe 3y ago
  This is your typical false economy trading a very small performance gain for strongly degraded data integrity. If the indexes are too much to ask you're basically saying that the referred to foreign records are never looked up (they have no key index!) and/or that the foreign records are never gathered up for referring table. If that's the case the problem isn't having superfluous indexes it's that you have superfluous data in your database.
  - cogman10 3y ago
    > a very small performance gain for strongly degraded data integrity. It's not very small. Doing a lookup on every write can be hugely detrimental to performance especially with large numbers of writes. > If the indexes are too much to ask you're basically saying that the referred to foreign records are never looked up (they have no key index!) That does not follow. The lookup for foreign records is not free so avoiding doing it when you don't need to will gain faster performance vs doing it all the time. Indexes make lookups faster, they don't make them free. Further, you have to consider the impact of locks on such a system. Writes to a table that references another will lock the contents of the second table while the write is in flight. So if I wanted to update the foreign record in any way, that task now gets blocked until data integrity check finishes. In MSSQL, that lock is held until the end of the transaction.
- ndriscoll 3y ago
  Batch processing is also WAY faster (like 1-2 orders of magnitude), but for whatever reason people don't talk about it much (cynically, I guess because it's too straightforward and old-hat). As far as I can tell, Rails doesn't have multithreading/async, so the OP is presumably not batching requests. That seems like a much better place to start than giving up referential integrity.
  - cogman10 3y ago
    We are batch processing. I don't know why you assume we aren't. FK constraints are simply too much burden for us. I'm not the OP, however, my company has experienced the performance headaches of FKs.
    - ndriscoll 3y ago
      I didn't assume anything about you; I said OP uses a framework that (as far as I know) makes it difficult to batch requests, so they're probably not batching requests. And they mention doing a `before_create` hook, which again suggests they're operating on individual models. If you're already batching and still running into a wall, then yeah you might need to remove constraint validation or shard. But batching alone can probably take most people far past the scale they'll ever see.

randomdata 3y ago

> What would be the advantage of implementing it in the application? It is always implemented in the application. The question is about whether you do it in your own application or use someone else's application. The disadvantage of doing it in your application is that you have to do the work that someone else has probably already done, and are likely not as great of a programmer as that other person, thus more likely to screw it up.

endisneigh 3y ago

It’s faster, you can do online schema migrations, but it’s hard to say unless a specific database is used as an example.

cogman10 3y ago

The main advantage is it's WAY faster if you are writing a lot. The big disadvantage of foreign keys is they do verify integrity. That means making lookups on every write/update which can be very costly especially as the model becomes more complex. If you have a read heavy application with low levels of writes then by all means put in foreign keys to you hearts content. But if you are at a point where you have millions or billions of records in multiple tables, they simply aren't feasible.

mavelikara 3y ago
But you pay the cost in checking it in the application, as GP said. If so, it simply is moving the cost from db to application layer. Is there a reason the checks can be implemented more efficiently in the application than the DB can?
- endisneigh 3y ago
  The reason is because the application knows what’s actually happening with the data.
  - LtWorf 3y ago
    It's supposed to know, but it actually doesn't. At my first job they used mongodb for no reason other than it was the fad at the moment, with the big data and so on. There were often crashes in the application because our records followed several different schemas, because of bugs in the application that were later fixed, behaviour that got changed, ORM got replaced with hand written code that was much faster… Basically, what happens with over confident developers and CTO that think they are very good developers and aren't.
    - endisneigh 3y ago
      There are plenty of counter examples. It’s simply a fact that your application can be faster without foreign keys.
  - mavelikara 3y ago
    Your argument, if I understand it correctly, is that the constraints can be implemented in the application layer more efficiently - either in dev time or CPU time - than it can be in the DB layer. Why is that so, and under what circumstances? (Asking to learn, not to argue)
- majkinetor 3y ago
  Yes there is although way more complex. U could use caching so not to hit db often and also app scales way easier than db.
- butlerm 3y ago
  Referential integrity problems usually happen due to missing deletes, improper deletes, or references that should be cleared. The overhead of checking for the existence of referred to records in ordinary inserts and updates in application code is unnecessary in most cases, and that is where the problem is. Either you have to check to have any idea what is going on, because your key values are being supplied from an outside source or you should be able write your application so that it does not insert random references into your database. If you actually need to delete a row that might be referred to, the best thing to do is not to do that, because you will need application level checks to make the reason why you cannot delete something visible in any case. 'Delete failed because the record is referred to somewhere' is usually an inadequate explanation. The application should probably check so that delete isn't even presented as an option in cases like that.
dkjaudyeqooe 3y ago
This is your typical false economy trading a very small performance gain for strongly degraded data integrity. If the indexes are too much to ask you're basically saying that the referred to foreign records are never looked up (they have no key index!) and/or that the foreign records are never gathered up for referring table. If that's the case the problem isn't having superfluous indexes it's that you have superfluous data in your database.
- cogman10 3y ago
  > a very small performance gain for strongly degraded data integrity. It's not very small. Doing a lookup on every write can be hugely detrimental to performance especially with large numbers of writes. > If the indexes are too much to ask you're basically saying that the referred to foreign records are never looked up (they have no key index!) That does not follow. The lookup for foreign records is not free so avoiding doing it when you don't need to will gain faster performance vs doing it all the time. Indexes make lookups faster, they don't make them free. Further, you have to consider the impact of locks on such a system. Writes to a table that references another will lock the contents of the second table while the write is in flight. So if I wanted to update the foreign record in any way, that task now gets blocked until data integrity check finishes. In MSSQL, that lock is held until the end of the transaction.
ndriscoll 3y ago
Batch processing is also WAY faster (like 1-2 orders of magnitude), but for whatever reason people don't talk about it much (cynically, I guess because it's too straightforward and old-hat). As far as I can tell, Rails doesn't have multithreading/async, so the OP is presumably not batching requests. That seems like a much better place to start than giving up referential integrity.
- cogman10 3y ago
  We are batch processing. I don't know why you assume we aren't. FK constraints are simply too much burden for us. I'm not the OP, however, my company has experienced the performance headaches of FKs.
  - ndriscoll 3y ago
    I didn't assume anything about you; I said OP uses a framework that (as far as I know) makes it difficult to batch requests, so they're probably not batching requests. And they mention doing a `before_create` hook, which again suggests they're operating on individual models. If you're already batching and still running into a wall, then yeah you might need to remove constraint validation or shard. But batching alone can probably take most people far past the scale they'll ever see.