Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

marked as duplicate by gnat, Martijn Pieters, MichaelT, Jimmy Hoffa, 9000 May 15 '13 at 1:26

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer 1

I would say you could very easily justify putting the logic in the database layer. It really is about encapsulation. If you are calling stored procedures to access your data then you are encapsulating that business logic the same as if you had an ORM layer.

I think people sometimes get bent about scalability and portability when you really don't have any need for that. If your applications do not have a requirement to run on different database systems (like an open source library might, or something you sell to clients), the I don't see a problem with that.

While there are a lot of tools for ORM and the sort, if you are comfortable with stored procedures and your client (whether that's the company you work for or a paying client) is comfortable with the support of that, then it's a good idea. You can then put a very light UI web layer in place and for that matter could probably stand up a webservice or other API pretty easy too, since you have encapsulated that business logic.

share|improve this answer
1  
The n-tier (3-tier) architecture is both physical and logical. Business logic in a stored procedure is still logically part of the business rules tier. Don't underestimate your database server. Most ORM's come with pretty heavy performance penalties. Most ORM's are also able to call stored procedures if you tell 'em to. I once replaced nearly 1,000 lines of loopy line-oriented business layer code with a single SQL query in a stored procedure that cut an 8 minute operation to a second or two. Did it put a bigger load on the database server? Maybe... but I'll take that any day. –  Craig Apr 17 '13 at 15:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.