Skip to Main Content
  • Questions
  • Your actual view on IT Architecture for where to put the logics: inside or outside the database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jan-Peter.

Asked: June 09, 2017 - 8:56 am UTC

Last updated: June 13, 2017 - 4:06 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Tom and team

This is the first question ever I am posting here, until now I was using Asktom as a silent reader only - and I want to say thank You a thousand times to keep this site up for so long, helping many people to find explanations and solution patterns for similar problems.

Now my question: what is Tom's and the team's actual view in terms of best practices, where to put the "logics". Because the "mainstream" of IT Architecture today says: take all logics OUT of the db and be ignorant about where to put the data - a view which I do NOT share, which some people may find provocating - so be it =)

I know from many Tom's posts, that You are in favor of putting it INto PL/SQL Stored Procedures, which I worked with many years successfully as well. I would recommend PL/SQL for DWH as first option in any case, for OLTP there might be some other variants acceptable ... for simple reasons of efficiency.

Now there is all these talks about REST, DbaaS, Micro Services etc You name it, which at any cost strives to seperate the "layers" of data, logics and representation. To the degree that they want to be deliberately ignorant on where the data are stored at all, could be even non-db, anything. They don't care. And they think they are smart doing so ... and they oppose Stored Procedures in any form, making the database to a data dump.

Is my impression right that this kind of hip ignorance towards databases comes with some considerable hidden costs? Actually I can see it for real in a number of projects ... to whom that pays off, is clearly a question on which side of the table You are sitting.

I am open to any comments!

and Chris said...

First rule of discussing where to put "business logic":

No one can agree what "business logic" actually is!

My view is:

All code you write to create software for a company is "business logic". This includes DDL. Meaning your table definitions are business logic.

So unless your schema is a giant "thing" table that stores JSON (or similar) you've got "business logic" in your database whether you have stored procedures or not. So a statement like "no logic in the database" is nonsense.

Remember: you can do a lot with basic primary, unique and foreign keys. The more you do with these, the less code you have to write. The less code you write, the less chance of bugs. So you should use these where possible.

For an intro to how to do this I thoroughly recommend developers read The Database Programmer blog. This shows how you can create a school timetabling application with basic database constraints. This enforces "complex" rules like "a student or teacher can only have one class at a time":

https://database-programmer.blogspot.co.uk/2008/09/comprehensive-table-of-contents.html

A lot of your code then becomes simple SQL with appropriate error handling.

This still leaves the debate over where this SQL belongs. Toon Koppelaars has compelling argument for placing this in PL/SQL: performance. There are many optimizations for SQL in PL/SQL which make it much faster than placing it in Java (or whatever).

You can see him discuss his findings at:

https://www.youtube.com/watch?v=8jiJDflpw4Y

As he says, this is because with PL/SQL you are in the database's "living room". Whereas other technologies must come in through the front door. This means you can actually use less DB server resources using PL/SQL!

I'd also like to point out that REST and PL/SQL aren't mutually exclusive options. You can use ORDS to expose PL/SQL as REST endpoints. Tim Hall has good instructions on how to do this at:

https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-create-basic-rest-web-services-using-plsql

In short: using constraints, stored procedures and other features available in the database will help you build better, faster applications with less code.

Sounds like a winner to me ;)

Further reading:

Bryn Llewellyn's "Why PL/SQL?" paper: https://blogs.oracle.com/plsql-and-ebr/why-use-plsql
Toon Koppelaars "Helsinki Declaration": https://thehelsinkideclaration.blogspot.co.uk/

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Toon Koopelaars : Smart Database Architecture

Rajeshwaran, Jeyabal, June 13, 2017 - 1:55 pm UTC

Very recently was part of the webinar session "Smart Database Architecture" from Toon Koopelaars.

where he shows how SQL's get executed with less resource from plsql than from application side (java layer).

The recording of that webinar session is available at this below link.

http://www.prohuddle.com/webinars/ToonKoppelaars/ThickDB.php
Chris Saxon
June 13, 2017 - 4:06 pm UTC

Thanks for sharing.

Nimish Garg, June 13, 2017 - 4:55 pm UTC

I have enjoyed reading this discussion and adding the link(answer) from asktom itself on the same question, which is my personal favorite
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526787800346856323

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library