An example from my (previous) work I've quoted several times, but still valid:

At my then employer we wrote an application for customers to enter their car registration number and it returned which rims and tires in our assortment would fit their car and showed those rims graphically on picture of customers car.

The logic for finding the rims/tires was written in SQL and wrapped in a PL/SQL packaged procedure returning a cursor variable. Front end graphic web code was written in ASP and Java applet.

3 years later java applets were out of style and Shockwave Flash was the hype (+ faster and better at the graphics.) Front end re-written. PL/SQL and SQL untouched.

3 years later customers were using iPads all over, which meant no Flash worked. Front end re-written in HTML-5 to be responsive to multiple devices. PL/SQL and SQL untouched.

Front end code need to move with the times. If we hadn't re-written front end over and over, we would have lost customers. But we saved a lot by not having to touch the back end logic.

As Connor states, this way of doing app development is valid not just for Oracle PL/SQL and SQL. It'll be equally valid in Microsoft T-SQL or other database environments.

The point is that the business logic part of your app that works with the data - code it as close to the data as possible. This part will change much less and less often than UI code.

The part of your app that deals with user interface - code it in whatever front-end framework that works to provide users with good experience in "present-day environment". This part will change often when user demands change due to new devices, cloud, VR, whatever... "Game changes" most often happen at this level.

Separating app code like that has been good advice for many years and will continue to be good advice going forward.

You might read http://thehelsinkideclaration.blogspot.com (start from the earliest blog posts in March 2009) - it explains very nicely separating code into layers like this.

Hi Russell,

You started out asking about "where application logic should be built". Kim Berg Hansen changed the expression to "the business logic part of your app that works with the data".

The change in terms is extremely important.

"Business logic" is what needs to be done, not how it's done. Business logic was implemented for centuries before computers existed.

When we say "application logic", we are already talking about "how" to implement business logic in software.

The term "application logic" is often used because of the Java / J2EE dogma of separating code into presentation, application and data layers - all of which are to be coded in Java of course.

In reality, "business logic" is sometimes data-oriented and sometimes not. The logical data model and its implementation in DDL all have to do with business rules concerning data. On the other hand, sending an email when my order has been shipped is not fundamentally a data issue.

It is vital to identify "the business logic part of your app that works with the data".
- Implement it as much a possible in the physical data model (DDL, constraints).
- Provide APIs to the application layer using SQL wrapped in stored procedures and views. The APIs should deal with individual objects (for OLTP), or multiple objects (for bulk processing), or should provide set-based access for batch and BI processing.
- The APIs should never commit or roll back! The APIs should never turn unforeseen exceptions into return codes! Otherwise you will break "statement level atomicity".

The "application layer" manages the transaction. It commits or rolls back. It does any non-persistent calculations, and it manages distributed transactions.

Connor and Kim hardly mention the "application layer". I think that is because many applications are mainly data-oriented and don't have much need of one.

I worked for years with Java dogmatists. All three layers were to be written in Java. The data layer was managed by Hibernate to allow for database portability. As a result, the "application layer" was huge and the other layers were frameworks.

For years now, we have admitted that the presentation layer has to be done in Javascript - and that we have to take different browsers into account.

We should do the same with the data layer: take different databases into account and use data-oriented languages with specific added value.

Once we do that, what's left in the "application layer"? Often not much.

Implement the data-related business rules (and security!) as close to the data as possible. Then you only need to change them when the data model changes.

Best regards, Stew

It's all here (above) and there (Bryn's paper, Helsinki blog). Kim mad a couple of true statements. Stew coined a couple of good definitions. And Connor eloquently demonstrated the Yafet syndrome. I'm not even going to try and give a summary of my view on this topic, you can read the Helsinki blog for that. The arguments over there prove to be timeless.

If there's just one thing I'd like you to take away from 'us database guys' telling you to do stuff in the database tier, it would be this:

Make the DBMS a service provider in your architecture.

If you have an app page that given a car registration number needs to get info on rims and tires, then *logically* build a service module in the database that has as its input a registration number, and as it outputs rim+tire info. Now whether you do that as ref-cursor, or as a stored-procedure with formal IN and OUT params, is not really relevant.

If you have an app page that given a logged in user, needs to display information, be it aggregated or not, that originates from multiple tables in the database, again then logically build a service module in the database that has the username as its input and returns all required display information as its output. Dependent upon the complexity of the display information to be returned you may consider giving back an XML-document or a JSON string, instead of a set of formal OUT params.

What is important is that you *not* embed SQL statements or PL/SQL blocks (other than the ones calling the db resident service modules) nor any conditional logic around them in your UI-technology du-jour (Yafet).
Because if you do, your agility to move to the next 'whitechipmunk' paradigm in UI-land, diminishes drastically.

Oh, and by the way, if you do this right, it will all perform 1, 2, sometimes 3 orders of magnitude better, while running on less hardware. Yes, I know, because I do this for a living nowadays.
That's orders-of-magnitude, ok? Not 2 or 3 times faster. No, 100 and sometimes 1000 times faster.

I enjoyed reading this piece. Of course I second what was said. Thanks, Connor, for citing my paper.

This is a very interesting discussion.

I like the term YAFET - I'll have to remember that one. Also, I love the names of the various front end technologies that you've come up with. :) My favourite is Ferris Wheel Framework.