> On Sun, 8 Apr 2018 14:39:49 -0700 > Guyren Howe <guyren@xxxxxxxxx<mailto:guyren@xxxxxxxxx>> wrote: > > When it comes to databases, I have universally encountered the > attitude that one should treat the database as a dumb data bucket. > There is a *very* strong aversion to putting much of any business > logic in the database. I encounter substantial aversion to have > multiple applications access one database, or even the reverse: all > abstraction should be at the application layer. That seems un-pragmatic to me. IMHO if any business logic needs access to lots of data, it's best implemented by code that resides in the database itself, close to the data. I once had a job where, one night a week, I couldn't go home until a certain program had been run and completed successfully. That meant I wasn't going home until midnight. I realised that the person that wrote it was (at least for the purposes of that program) treating the database like "a dumb data bucket". Millions of records were being selected, transferred over a network to another host, effectively grouped and summarised, then each resulting summary record was inserted into another table, one stored function call at a time (with all the network round trip times that that implies). It took 2-3 hours to complete. I replaced it with a stored function that took two minutes to run and I was able to start going home hours earlier. So, as you can imagine, it would take an incredibly good argument to convince me that business logic shouldn't reside in the database. :-) I've always assumed (perhaps incorrectly) that not wanting business logic in the database (and not using all of the features that databases provide) was just a way to justify programmers not having to learn SQL but it's just another language and paradigm and programmers know so many languages and paradigms anyway that I never understood why knowing SQL was a problem. My assumption is probably wrong. And I guess as long as the resulting software runs as quickly as it needs to, it probably isn't an issue. If it's a widely held view, then it must be workable. I use stored functions exclusively, partly so that the business logic is close to the data it needs to be close to for efficiency, but also for security reasons. Users have no permissions to select, update, insert or delete anything. All they can do is execute stored functions that have been previously created by the database owner who does have those permissions. For a system that's accessible to the public, It's a great way to guarantee that SQL injection can't do any harm (even if all the other protections fail to work or are bypassed). For a system that's only accessible to internal staff, it's a great way to defend against their workstations being infected by malware that goes looking for databases to attack. cheers, raf
