Friday, September 15, 2006

SOA and the Database - Heresy?

It comes up a lot at conferences and seminars: Where should my business logic reside - the database or the middle-tier? In the SOA world you might think that using the database was the wrong choice, but why not leverage your existing DB-level validation from your Business Services?

I recently had some correspondence with Kent Graziano of ODTUG fame on just this topic. He says, "essentially we generate the TAPI and the associated DB triggers on all tables. The developers then write simple insert, update, and delete SQL against the tables to pass the required data elements. The triggers fire the TAPI procedures and fill in the auto generated stuff (sequence ids, date created, user created, etc). And voila you get referential integrity without coding in the middle tier. One of our apps is even generating the journal records automatically for audit trail. In addition all these applications rely on Oracle views or materialized views (from our read-only ODS) as the source for all the drop down lists. Seems that may be an SOA-type approach as well since we have abstracted the data one layer above the actual database tables (and we reuse the same view definitions for multiple applications to insure one-source of truth across the apps)."

I don't see anything wrong with this approach. Of course, from an SOA point of view you must ensure that your services are just that, and not too fine-grained, but that can be dealt with in or above the DB level in whatever technology or framework you choose to develop business services in.

Moving forward, it might be that Kent's application developers will take advantage of the benefits of middle-tier business logic and rules - for instance using the ESB and Rules Engine of Oracle's SOA Suite - but that's another discussion! So, am I an SOA-heretic to use database logic and services in the same sentence?


Tom said...

Our latest SOA project uses only an Oracle 10g database.

The application layers are split in different packages.

All the low REST services are implemented in PL/SQL using the XMLDB and the MOD_PLSQL.

The development has been test driven with the help of utPLSQL.

All the services can be tested trought an HTMLDB application.

The develop and deploy processes are very agile with this approach.

We have a lot of experience in developing SOA projects in java, but this time it's been easier, faster, and the performance are very impressive.

Susan said...

This sounds like an interesting project. What are you using for the UI, did you use J2EE components, did you use a development tool? I'd love to hear more. Email me privately if you prefer

Anonymous said...

We are also using only Oracle 10g in two applications we are creating now. The interface being developed in Adobe Flex 2.0 communicates with the database using DBMS_EPG where we call packages and procedures which return XML messages (we build the message using the various XML built-in packages and functions provided by Oracle). So far we are very happy in terms of security, flexibility and performance.

Anonymous said...

I am interested also. Was your final UI using HTMLDB (now Apex) or was that just used for prototyping.


tom said...

Thank you for your interest.

We have a workforce automation project developed using J2EE components.

This project has been extended with a voice portal.

The voice gateway uses low REST services so we did a J2EE prototype but the perforance was no so good.

So we decided to go for full PL/SQL services, all hands made, to get performance and quick agile development.

We used APEX to create the configuration and the administration panels for the mobile devices, as well as we used APEX to test quickly all the servcices.

In this way we were able to show the users the voice behaviour and the visual behaviour of the application at the same time.

I think APEX is a great product and I would like to use an Oracle PL/SQL based SOA framework as well.

Veniamin Goldin said...

Susan, we have another dilema - should we use Web Services to query list of data from database (using Oracle ESB DB Adapter for example) to better suite SOA and be more loosely coupled, or it is still fine to get the data using ADF BC or similar?

Which way to consider?

Susan said...

Veniamin, both approaches are ways to consider. This might not be the answer you want to hear though! If you want to the loosely coupled approach then using ESB is a good choice. ADF BC or other technology is also viable if you don't need such loose coupling. There are many articles on OTN that might help you make that decision.