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

> On 14.12.2009 07:53, Tim X wrote:
>> Robert Klemme <shortcutter_at_googlemail.com> writes:
>>
>>> +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, 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...
>

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!

Tim

-- 
tcross (at) rapttech dot com dot au