This question already has an answer here:
Once upon a time it was common practice for both the business logic and database logic to be written in the same language (eg PL/SQL, Transact SQL, etc), more recently the practice is to separate the application into layers/tiers such as database, business logic, and presentation, with each tier written in a different language. Typically an ORM is used to help facilitate communication between the business logic and the database.
From time to time however, especially when the application is relatively small, I still write applications with the business logic pushed down into the database layer, only the user interface is outside the database. I am aware of the various arguments for and against this, however I'm wondering as to whether there are any contemporary best practices if one was to write a database application in this manner? There are quite a few books on data models etc, but none really talk about how to structure the actual application code in terms of stored procedures, functions and so on. Also there are a number of books on database refactoring, or database anti-patterns, but again they broadly assume that the database is just being used to persist data.
I did find this one paper by Blaha, "Object-oriented design of database stored procedures". Are there any other books, papers, or sample databases that illustrate contemporary database application design best practices?
It actually seems relatively common to still build applications in this way, however it appears to be one of those things that people have accepted is not best practice so they want to avoid talking about it.
Is it OK for a business stored procedure to access a table directly ? Or should all table access be through CRUD methods? Are there any code generation tools people recommend? Thanks.