It depends absolutely on the type of the project, the size, the technologies that you use, the type of organization and, of course, the preferences of the programmers. I've been in both of situations, and i will try to give you some advices:
Benefits of logic in DB:
Stored procedures are a lot faster, specially if you need to do many querys in a single action. If you are dealing with a controller and other layers, there are latency between the database and the webserver itself. The thing gets worse if the webserver and database are in different locations.
Better managment of transactions. You write the BEGIN, the END, and maybe the COMMIT sentences. And that's it. No need of unit of work, and some other strange patterns that may confuse you about the scope.
In some organizations, is faster to compile a PL/SQL package rather than ask for a migration of a .dll (or whatever type of component that you use in your webserver)
Cons:
You lose object oriented design (i mean, you have tables if you are working with SQL database, but thats not the point of OOP)
You lose single responsability principle.
You are much more attached to the database technology. If tomorrow you must migrate Oracle to MSSQL, it will be much difficult if all you logic is on the database. If the logic were in the controller/bll layer, you only need to change the db connector and thats it.
Generally, it's a lot more difficult to make unit testing of stored procedures rather than a .dll. There are less tools, and almost always deppends of the data itself, wich is not good.
As anecdote, currently i'm working with a acess control software, wich operates with an electronic device that validate the user RFID card and check if its authorized to open the door. We tried everything, but the only solution that we find, is to use a stored procedure. When you pass the card, the device triggers a query, and if the query returns "true", the door opens. There was no way to do that on the webserver side, so stored procedure was the only way to go. We change the default "select ..." default query to a "call sp_validateaccess(param1,param2,etc.)", and works pretty well.
Could not agree more with you. If you check this post, almost all replies agree to the same ideas, pros and cons, yet, there is so much variety out there! I think what happens is that "real life problems" hit you so hard that you need to throw all your principles and just figure out a way to solve it fast and efficient as possible.
PS: that door probably opens really fast, thanks to the SPs!
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
It depends absolutely on the type of the project, the size, the technologies that you use, the type of organization and, of course, the preferences of the programmers. I've been in both of situations, and i will try to give you some advices:
Benefits of logic in DB:
Cons:
As anecdote, currently i'm working with a acess control software, wich operates with an electronic device that validate the user RFID card and check if its authorized to open the door. We tried everything, but the only solution that we find, is to use a stored procedure. When you pass the card, the device triggers a query, and if the query returns "true", the door opens. There was no way to do that on the webserver side, so stored procedure was the only way to go. We change the default "select ..." default query to a "call sp_validateaccess(param1,param2,etc.)", and works pretty well.
Could not agree more with you. If you check this post, almost all replies agree to the same ideas, pros and cons, yet, there is so much variety out there! I think what happens is that "real life problems" hit you so hard that you need to throw all your principles and just figure out a way to solve it fast and efficient as possible.
PS: that door probably opens really fast, thanks to the SPs!