Re: "Business Logic / Rules should never be in the database or stored procedures"

From: Shakespeare <>
Date: Mon, 14 Dec 2009 09:13:55 +0100
Message-ID: <4b25f3c7$0$22903$>

Robert Klemme schreef:
> On 13.12.2009 09:24, Thomas Kellerer wrote:
>>> * In my experience, those who argue for not having any business
>>> rules/logic in the database tend to be developers
>>> who are essentially lazy and refuse to learn anything about the
>>> database or its facilities. They just want to use it as a bit bucket
>>> and do absolutely everything in the application layer. Too often, this
>>> even includes basic data manipulation that could have been done more
>>> efficiently and resulted in clearer and more easily maintained code
>>> using SQL. This situation appears to have gotten worse with the growth
>>> in popularity of Java - essentially, code monkeys who just want to
>>> plug in API calls and who have no interest in learning all the tools
>>> available to them. they have mastered basic select, update and insert
>>> and thats as far as they want to go. When you are coming from this
>>> perspective, stored procedures are really just a way to make SQL
>>> 'easier and you get the CRUD way of thinking.
>> Completely agree
>> And things like Hibernate tend to make this situation even worse as
>> people ge the impression they don't need to think about the database
>> anymore "because Hibernate takes care of that"

> +1
> There is an underlying dilemma which I haven't seen any satisfying
> solution to yet: you want enforcement of business rules in the database
> in order to prevent any intentional or unintentional screw up of
> application data. OTOH you want those rules in application code as
> well, because there is where all the business logic resides.
> From my experience what often happens is this: some basic business
> constraints are enforced in the database (uniqueness, NOT NULL,
> referential integrity, even some CHECK constraints) while the more
> complex rules live in application code only.
> From a redundancy point of view in an ideal world we had a single
> source for business logic and extract application code as well as schema
> based integrity checks from that. That probably will never work out of
> the box because it omits aspects of physical deployment of data as well
> as performance of checks. Also, checks might be done redundantly. And
> we even haven't discussed schema migration yet...
> Another option would be to place all the business logic in the database
> and treat application logic as glue between UI and database only. With
> Oracle we have a full features programming language that is tightly
> integrated with SQL and would make coding application logic at least
> feasible. This does not seem to be done frequently. Does anybody have
> any experience with that?
> Kind regards
> robert

Oracle CDM Ruleframe + Headstart was/is built on this last principle, and has been quite popular in the early/mid 2000's for Oracle Designer projects. Database logic (triggers) called the same (pl/sql) code that application logic (pl/sql) did. All table updates from the application was done through api's which called table-api's, but when an update was performed on the table directly, the triggers would call the table-api.

Application performance was ok most of the time, despite of all the (coded) overhead caused; biggest problem was debugging, specially when Change Event Rules were modeled using this framework.

Shakespeare Received on Mon Dec 14 2009 - 02:13:55 CST

Original text of this message