13

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.

0

1 Answer 1

Reset to default
10

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.

15
  • 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 Tullis
    Commented Apr 17, 2013 at 15:34
  • @Craig: Can you provided a representative example? I find it hard to believe that you can accurately represent 1000 lines of loopy, line-oriented, non-trivial code in a single SQL Query of any length.
    – Robert Harvey
    Commented Jun 2, 2015 at 21:08
  • 1
    @RobertHarvey You want me to mock up an example with 1,000 lines of loopy, line-oriented code? ;-) So, for example, imagine some code that opens a cursor over a large dataset then iterates over that cursor, checking for certain values in each record and depending on conditions, performs a sub-select to grab additional records, then checks for particular conditions in those detail records. Each time it finds what it wants, it writes to a temp table, and at the end it closes the cursor and selects all the records back from the temp table, then drops the temp table. Etc.
    – Craig Tullis
    Commented Jun 2, 2015 at 22:28
  • 1
    When I was working on a project for the consulting arm of a fairly large software (and now hardware) company in Redmond years ago, I was talking to one of the consultants about this general type of issue, and he talked in generic terms about another consulting outfit that talked a company into developing a system using Oracle on powerful hardware, and a Java middle tier, and convinced the customer that the load on the database server should always be kept at or below something like 2%, that all the heavy lifting should be done in the middle tier. Performance was beyond abysmal.
    – Craig Tullis
    Commented Jun 2, 2015 at 22:31
  • 2
    @RobertHarvey "I'm fairly interesting in where tools like MongoDB are going" oops. Fairly interested. :-) I agree that not all business logic belongs in the database, but I'm definitely not afraid to put some of it in there, and I do like stored procedures. I also really like letting the database protect itself through appropriate check constraints and foreign key relationships--all of which are actually business rules, if you think about it. ;-)
    – Craig Tullis
    Commented Jun 3, 2015 at 0:32

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