Re: "Business Logic / Rules should never be in the database or stored procedures"
Tim X
> 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
> bsc708...@myoracleportal.com
> (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
Thomas Kellerer
> 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"
Robert Klemme
+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
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
Joachim Pense
>
> 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.
>
>
> 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?
>
You all seem to view stored procedures as "part of the database" because
they happen to be stored there. If you have a stored procedure that is
called from somewhere (as opposed to a trigger), in my view this is just
application code like, say, a C program, only it's less integrated in the
overall development and tools landscape.
Joachim
Gerard H. Pille
...
> 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
> bsc708...@myoracleportal.com
> (remove XX to contact)
Quite right. Not that you are of your rocker, that is.
Tim X
>
> +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?
>
Yep. In fact, I just finished a project where I pretty much insisted
that was the design. All the business logic is in the database. The only
access is via pl/sql packages. this is enforced by granting execute
privs to a separate schema, which is the schema the web UI is able to
connect to. the web UI essentially retrieves ref cursors to display data
via calls to plsql procedures/functions and sends back data via plsql
procedures. All the web UI does is handle the display of data and
provides forms for entering/updating data.
Originally, I was going to use Oracle for the front-end UI as well, but
that was taken out of my hands and given to our web developers, who
implemented the interface using Java with a combination of JSP and
Spring.
The application has been in production for a little over three months
now. Nearly all our problems have been due to the web interface. We ran
into a bug in Spring where it doesn't close cursors properly and a few
other issues due to the fact they adopted Spring part way through the
development. There were also some performance issues, all of which were
due to the web interface and not Oracle. The problem was the web team
used a form of reflection supported by Spring. Apparently (I'm no spring
or Java expert), you can use spring in such a way that it builds
procedure/function calls dynamically through a plsql procedure that
queries the dictionary tables to find out what the arguments for a
procdure are. Once I found this out, I instructed the web team to remove
this additional layer of waste and performance improved significantly.
Again, the problem was essentially due to the web developers not wanting
to know anything about the database or even the provided procedures -
they just wanted it to all work like magick!
Of course, we have had some bugs in the back-end. Nearly all have been
very simple and most essentially 'cosmetic' rather than serious.
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.
The best part is i can now go back tofocusing on the second stage. This
is the most exciting part as this is where we plan to add some really
cool new functionality. I'm also pleased I seem to now have a good web
developer to work on the UI. I actually find UI design and
implementation quite boring, so I'm pleased to be owrking back on the
guts of the system, which for me has the more interesting and chalenging
problems.
One thing I'm hoping to be able to do soon is spend some time looking at
11g. Originally, I had thought of using Oracle's rule manager to
implement much of the business rules. However, in initial trials with
10g, I found rules manager just wasn't quite up to the task. While it
appeared to provide much of what I was looking for, I found it somewhat
unreliable. There were times when changes just didn't seem to take
effect and you frequently had to get the DBA to clean up internal tables
etc to get things back into a consistent state. My gut feeling was it
was just another example of a new Oracle feature that just wasn't quite
ready and you have to wait until the next release. I'm hoping the 11g is
a lot better. In the end, I essentially rolled my own solution, ripping
many of the ideas off from rule manager. So far, its worked really well.
Even since going live we have had to update/change some of the business
rules and this has proven to be both straight-forward and reliable.
However, I'd still like to try to use rule manager as I'd rather let
oracle take care of the maintenance of that code instead of me!
Tim X
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.
Shakespeare
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
Mark D Powell
> 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.
>
> 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.
>
> performance short sightedness may come from, you think ?
>
> rules to exist in the database... and should.
> BChase
> (remove XX to contact)
>
> ****************************************************************
> ****************************************************************
The more business rules that can be encoded into the database via
constrains (PK, UK, FK, and column), via stored code (packages,
procedures, and fuctions), and via table level triggers generally the
easier it is to maintain the integrity of the data. If business
decisions are going to be made based on the application output then
you want the data upon which those decisions are made to be valid.
Putting busines logic into the database greatly helps in this area.
Common code provided by stored code reduces not just the chance of
incorrectly performing a business task, but also speeds application
coding since the logic necessary to perform common tasks has already
been written and can be reused after the very first use.
Having the business logic close to the data usually helps the
performance of data intensive tasks.
None of the above means you do not still code business logic into the
application. Business logic needs to guide the design of the screen
processing and report generation. Some logic will need to exist in
the screen code and/or object layer but you had best put as much of
the business logic into the database as practical.
I say this based on over 20 years of experience.
IMHO -- Mark D Powell --
Gerard H. Pille
...
> 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
Gerard H. Pille
>
> 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.
Robert Klemme
Tim, thanks for the extensive use case narrative! I was pondering
similar ideas for one application in our company but so far I'm the only
one proposing this. Part of the reason might be that Oracle knowledge
is really sparse and / or people don't believe that good app logic can
be implemented in a non "application" programming language. Funny
though that what we have now is a procedural API implemented in an
object oriented language...
Tim X
Given the domain and what the interface is required to provide, yes.
Tim X
IMO, you have to consider the available skill sets in addition to the
technology. If the developers available are not willing or not able to
come to grips with Oracle, any decision to put more of the business
logic within the database is likely to fail. This is why I also think
any architectural decision must involve those who will be building the
system and should never be imposed by anyone outside the development
process who isn't actually involved in the development at a hands on
level. In addition to building the bed, the architect should also have
to sleep in it!
joel garry
Based on nearly 30 years of experience, I think the division of
business rules among layers is more difficult than anyone wants to
admit.
It's true that the closer to the db the better the integrity... but is
it true that the business rules describe the necessary integrity
properly? What winds up happening is either there is a limit to the
complexity of the rules that can be put in the db, which leads to all
sorts of strange things as the rules are bent to simplify, or you get
what the everything-in-the-app people ask for, which they don't
realize is bad integrity of the rules across the app.
What we need is a practical way to put as much business logic as close
to the db as possible, but that way doesn't exist. All sorts of
frameworks are in use, hence this kind of discussion.
As a db-centric person, I agree with you and Tim and all the others
here, but we're in the minority. There are, after all, orders of
magnitude more developers than dba's. On the other hand, there're
more dba's than architects. It's good to see one listening to us!
I'd like to say there's room in the industry for all kinds of ways for
architects to deal with this, but unfortunately, the mode seems to be
slavish following of the paradigm of the week. Or weak. So there's
plenty of work for us for the foreseeable future fixing the problems
which come from bad architecting.
jg
--
@home.com is bogus.
http://threatpost.com/en_us/blogs/qa-eugene-spafford-121409
Gerard H. Pille
> So there's
> plenty of work for us for the foreseeable future fixing the problems
> which come from bad architecting.
>
Which, in the current economic situation, is not to be snivelled at.