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

From: joel garry <>
Date: Mon, 14 Dec 2009 14:10:22 -0800 (PST)
Message-ID: <>

On Dec 14, 12:34 pm, Mark D Powell <> wrote:
> On Dec 12, 4:58 am, BChase <> wrote:
> > 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
> >
> > (remove XX to contact)
> > ****************************************************************
> > Resource Library is now Online _at_
> > ****************************************************************
> 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.

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.


-- is bogus.
Received on Mon Dec 14 2009 - 16:10:22 CST

Original text of this message