Re: Any reason not to have logic in the db?

  • From: Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 12 Jun 2012 14:06:38 +0100

Hi Paul,

> What I want to know is: Is there any reason whatsoever
> to have logic outside the database that could potentially
> be kept there - notably CHECKs, CONSTRAINTs,
> TRIGGERs, containing possibly complex PL/SQL - i.e.,
> it's sort of a question about App-tiers vs. DB.
No, never, not under any circumstances at all. Other opinions are 
available, however, but they are wrong! ;-)

> My own opinion (to put my  cards on the table) is that
> "logic" should be stored as far down the stack as possible
> (DB, first, then app-server...&c).
Agreed. The best place to protect and keep the data secure, is within 
the database.

> Anyone know of any counter-examples?
Yes, vendors of "database agnostic" applications which simply don't 
work. (Except for those developed in Uniface - because Uniface does it 

Check constraints in the application, in fact, any constraint in the 
application is fine, provided that the constraints are (already) in the 

I don't mind the application checking and validating data before it gets 
to the database so that the user gets a better error message than the 
database may chuck back, but the data has to be validated by the 
database before storage and commit.

Any application which does all the constraint and trigger work and 
simply uses the database as a bit bucket for the data is a system asking 
for trouble. And when trouble hits, it's us DBAs who get called out at 
stupid o'clock to fix things - even though we said "don't put that pile 
of cr4p on our servers" in the first place!

Do I sound bitter? I'm not - my call out rates are obscene! :-)

Data are the most important thing to the business, the data must be 
protected. That means arranging everything possible to protect the data 
as close to the data as possible.

After all, these application usually work on the principle that it was 
the application which put the data into the database so when it is read 
back, it *must* be correct. Except that because there are vendor 
supplied patch scripts and possibly the odd occasional DBA run script 
that directly puts data in or out of the database - completely bypassing 
all constraints etc - we get application failures when the data are not 
exactly as expected when reading back from the database.

And because the application was expecting perfection, there are little 
or no exception handlers to inform the user of what happened, and the 
pile of mess gets bigger and bigger until, eventually, it barfs, big 
time. Call out the DBAs again, it has to be a problem in the database. 
Yeah right!

<end rant>

Now, in addition and also, what about code? I like to see all the code 
in the database too. Personal preference because when I get called out, 
I don't have the time or skills to decompile a Java application (which I 
won't go off on another rant about!) running under WebLogic to see what 
the hell it does to get into the mess it's now in. I don't need that 
sort of stuff at stupid o'clock!

Ok, some vendors are so ashamed of their code that they have to hide it 
within the Java, it's not a major problem, I'll just trace the execution 
with 10046 or similar, and see exactly what it's doing anyway - the 
vendors are usually unable to use something like "wrap" to obfuscate 
their code anyway!

Let's face it, running compiled PL/SQL for example, within the database, 
running on a hugely powerful database server can't be any faster than 
running byte-code interpreted Java on a lesser WebLogic server can it? 
(Oh yes it can vendors!) Or, as is sometimes the case, client side!

So, finally and in conclusion, apologies for ranting, and no, there are 
no good reasons for having constraints etc outside the database.


Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767

Other related posts: