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.

Antonio2011a
  • 1,209
  • 10
  • 16

1 Answers1

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.

Bill Leeper
  • 4,113
  • 15
  • 20
  • 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 Apr 17 '13 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 Jun 02 '15 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 Jun 02 '15 at 22:28
  • I've seen it more than a few times. That example, though contrived, is NOT different from the same thing in middle tier C# or Java. You can indeed replace a lot of that kind of garbage with single (albeit possibly moderately complex) SQL queries that will perform orders of magnitude faster. I can't fathom why the current generation has gotten it into their heads that relational constraints, database joins and a filter clause with more than one condition is evil, or that SQL is the devil incarnate. Databases are elegant, powerful tools. Respectfully. :) – Craig Tullis Jun 02 '15 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 Jun 02 '15 at 22:31
  • Until they got all the cruft out of the middle tier and moved a whole heap of it into the database, and performance became quite snappy. I'm totally aware of transactionless, queue-driven architectures like what eBay has today. I'm also totally convinced that 99% of applications don't need anything like that. And eBay didn't have it either until they hit that barrier and had grown to the point where they had the millions to invest in engineering the solution. – Craig Tullis Jun 02 '15 at 22:32
  • @Craig: Thanks for the detailed explanation. So if I read you right, your example is really a treatise on SQL vs NoSQL, and that the use of Stored Procedures is mostly a matter of load distribution, is that correct? Also, I would note that any code you run on a database server is very tightly coupled to that database server, an inevitable tradeoff. Do you agree? – Robert Harvey Jun 02 '15 at 22:50
  • @RobertHarvey Not necessarily. There's a current trend in schools and among a non-trivial subset of working programmers to avoid teaching database theory and to dismiss databases as if they're the equivalent of simple file storage. I'm saying that a well-designed database and decent SQL queries can in many cases blow the doors clear off of imperative middle tier code in terms of performance, that there are still real advantages to using the database for what it was designed for, but more to the point that SQL isn't automatically less performant. – Craig Tullis Jun 02 '15 at 22:55
  • Your database is going to outlive your application code (it just is, they always do). It might even outlive the language your code is written in. NoSQL database are another issue entirely, and I'm fairly interesting in where tools like MongoDB are going. – Craig Tullis Jun 02 '15 at 22:56
  • @Craig: Maybe one of these days you and I will collaborate on a blog post or paper that describes what sort of things are appropriate for SP's. You are quite right that they are underutilized (especially in NoSQL environments), but I don't think all of the BL belongs on the database server, and many BLL's use SQL (even dynamically-generated) to access their data, with quite good results. – Robert Harvey Jun 02 '15 at 23:00
  • 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 Jun 03 '15 at 00:32
  • I kind of glossed over your assertion about tight coupling to the database. That's true to an extent. But all the major database servers (including the open source ones) have powerful horizontal scaling options, written by folks whose focus is highly optimized performance. As opposed to middle tier code written by folks whose job description is implementing business rules. Maybe what I'm arguing against is extremism in a general sense: "Never use store procedures!" Well that doesn't really make sense, but neither does "Never use anything but stored procedures!" – Craig Tullis Jun 03 '15 at 00:34
  • @RobertHarvey ..and of course you were also referring the tight coupling in the sense that writing a bunch of PL/SQL code makes it harder to change your database from Oracle to, say, SQL Server, because you'd have to either rewrite all your PL/SQL in some imperative language in the middle tier, or transmutate all your PL/SQL into TSQL. But that's another example of treating the database server like it's a file server. But the database server is a powerful tool in its own right and should be utilized. It isn't as if the database server gets cheaper if you don't use 90% of its features. :-) – Craig Tullis Jun 03 '15 at 18:04
  • @Craig: SQL Server supports .NET assemblies natively. Perhaps you can get the best of both worlds. – Robert Harvey Jun 03 '15 at 18:05
  • @RobertHarvey I have mixed feelings about those .NET extended stored procedures, though. :-) Last time I looked, it wasn't *really* native. The SQL Server database engine itself is still written in C/C++, not C#, so SQL Server instantiates the .NET runtime. The .NET code still needs to spin up a connection to the database, and so on. And it *might* not be immune to some of the same drawbacks as extended COM stored procedures back in the day. If an exception escaped your C/C++/VB stored procedure, your database server crashed. – Craig Tullis Jun 03 '15 at 18:30