Prev: PL SQL Error
Next: "Business Logic / Rules should never be in the database or stored procedures"
From: Thomas Kellerer on 13 Dec 2009 03:24
> * 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"

From: Gerard H. Pille on 13 Dec 2009 08:44
BChase wrote:
....
> What is my problem ? Well assuming I am not misinterpeting things, my Enterprise Architect area and some non-Oracle knowledge
> architects feel that the business logic / rules should only be in the application tier.

Business logic in the database has faster access to the data, so if a lot of data is needed ...
In the application layer it is closer to the user, so when you want to be user friendly ...

> Only Create, Read, Update, and Delete operations belong in stored procedures.

A number of expressions spring to mind that I'd better not put into writing. These architects
will probably have functions to retrieve a column from a record, maybe a function where you
provide an id and the name of the column, which will be translated using dynamic sql. I've seen
programs where three functions were called to retrieve 3 columns from a record. Underneath the
complete record was fetched 3 times.

> Reason being that the database cannot process the business rules efficiently nor can they effectively be managed.

Is that so?

My contention is that they care coming at it from a typical application perspective, not an
ERP perspective.
> I can understand workflow logic being externalized (aka Oracle Workflow), but the restricting stored procedures to CRUD operations
> only... would seem to belittle the power of the Oracle database and what it has to offer.

Since you are using Workslow, I suppose performance is no issue? The Oracle database is an
awesome instrument, when looked after properly.

>
> Mind you, these other individuals have primary backgrounds and experience with SQL Server. This may be where some of there
> performance short sightedness may come from, you think ?
>

Never underestimate an opponent, and it would be "their" shortsightedness.

> Anyways, am I off my rocker about sayings its a blend, but that there definitely exist many opportunities for the business logic /
> rules to exist in the database... and should.
> BChase
> bsc7080mqcXX(a)myoracleportal.com
> (remove XX to contact)

Quite right. Not that you are of your rocker, that is.
From: Shakespeare on 14 Dec 2009 03:13
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
From: Gerard H. Pille on 14 Dec 2009 16:10
Tim X wrote:
....

> As a result of the issues, I've now been given a free hand to fix things
> and more importantly, choose the web developer to work on fixing the
> interface. I found one who was more interested in actually
> understanding how Oracle works, things to do and things to avoid when
> working with Oracle and agrees with the basic principal of just letting
> the front-end handle the interface etc. In the last 3 weeks, we have
> made some really significant improvements and users are finally
> beginning to get real value from the system.
>
....
> Tim
>
>

Do you think you will ever achieve a user friendly front-end using a web interface?

Gerard
From: Gerard H. Pille on 14 Dec 2009 16:14
Tim X wrote:
>
> Some good points and reminds me of something else I forgot to mention.
> something I've often seen in applications that have the business rules
> in the applicaiton layer and none in the database (i.e. basic CRUD) is
> much much larger data retrievals/transfers. These systems tend to do
> little filtering of the data at the db level. instead, they retrieve
> large chunks of data and then filter it at the app level and then curse
> Oracle for being inefficient! It stuns me that people think something
> like Java sorting and filtering out data will be more efficient than
> doing it at the SQL level.
>
> Tim
>

Precious few people care about this.
 |  Next  |  Last
Pages: 1 2
Prev: PL SQL Error
Next: "Business Logic / Rules should never be in the database or stored procedures"