Re: "Business Logic / Rules should never be in the database or stored procedures"
- From: Tim X <timx@xxxxxxxxxxxxxxx>
- Date: Sun, 13 Dec 2009 19:10:29 +1100
BChase <bsc7080xxmqc@xxxxxxxxxxxxxxxxxx> writes:
I have run into a differing of opinions in my shop and wish to hear comments from some of our seasoned individuals here.
I am architect within a fortune 500 company. My primary role has served around the Oracle Applications environment and Oracle
database. We are currently on 11g and 11.5.10 Application tier. We do have Oracle Workflow, but most of our coding is PLSQL based
stored procedures. From my understanding, there exist a blend of business logic between the database and the application layer for
the Oracle Applications framework. I say this because of how Oracle expands the OA environment by adding modules. The rules have to
go somewhere when so many of the framework pieces appear to be the same.
This all came about when an EA standard was posted about not parsing XML within stored procedures for performance reasons. My
thought maybe it should have been an opportunity to educate and explain how to mitigate performance issues if one needed to pass XML
objects to stored procedures and parse the information as opposed to a bold sweeping statement.
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. Only Create, Read, Update, and Delete
operations belong in stored procedures. Reason being that the database cannot process the business rules efficiently nor can they
effectively be managed. 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.
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 ?
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@xxxxxxxxxxxxxxxxxx
(remove XX to contact)
****************************************************************
No, I don't think your off your rocker at all.
* Any "Never Ever" statement needs to be considered carefully. It is a
very black and white statement, yet we live in a grey world.
* IMO where the business rules live depends on many factors, including
the type of application, the skill sets of the developers/maintainers
and the types/complexities of the business rules. More often than not,
its not an either/or situation. Some of the business rules are best
suited to the database and stored procedures, others are more easily
expressed and maintained at the application level.
* 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.
* There are pros and cons on both sides. Oracle provides a number of
facilities at the database layer that assist in both expressing
business rules and enforcing such rules. Having the rules in the
database means that *all* your application interfaces will be
constrained by the same business rules. Changing the rules in one
place ensures all application interfaces are working with the same
rules etc. On the other hand, having all your business rules in the
database locks you into that database pretty tightly. There is little
in the way of 'standars' in this area. Many companies are nervous
about hitching their application to a single vendor. Some shops feel
they will have a bigger potential market if their application will
work with Oracle, MS SQL Server, DB2, etc. Personally, I've only ever
seen applications being reduced to the lowest common denominator using
this approach, but that doesn't mean its not valid for some domains.
* In some cases, it can be more difficult to define,enforce and maintain
business rules in the application layer, especially if that is a layer
consisting of multiple languages or distinct/distributed interfaces.
I've seen a number of applications that have come unstuck because
rules were updated in one module and not another etc.
* I don't think Oracle is expressing any paticular preference. While
they have added products and facilities that have a definite
'external' feel, they continue to expand features inside the database.
for example, dbms_rlmgr, in my opinion, was added to make it easier to
define business rules within the PL/SQL and Java layers within the
database. Many other enhancements to PL/SQL also look to be additions
to increase expressivness etc.
* I would be very skeptical of any statement that banned the use of XML
in stored procedures because it was inefficient. As we all know,
performance is rarely a simple issue solved by banning the use of some
construct. This is probably even more true of Oracle. I have only used
some of the quite extensive XML capapbilities within Oracle 10g. What
I have used has performed quite adequately for our needs and has not
put any great strain on resources. I would want to see some very good
tests and hard numbers before I'd be happy with such a blanket ban.
For all we know, XML in the database may be a little slower, but more
maintainable and reliable than putting it in the application layer or
maybe nobody has profiled the application layer and just haven't
noticed its even worse or.....
Tim
--
tcross (at) rapttech dot com dot au
.
- Follow-Ups:
- Re: "Business Logic / Rules should never be in the database or stored procedures"
- From: Thomas Kellerer
- Re: "Business Logic / Rules should never be in the database or stored procedures"
- Prev by Date: Re: count specific data changes in adjacent ordered rows
- Next by Date: Re: "Business Logic / Rules should never be in the database or stored procedures"
- Previous by thread: count specific data changes in adjacent ordered rows
- Next by thread: Re: "Business Logic / Rules should never be in the database or stored procedures"
- Index(es):
Relevant Pages
|
