Thanks for the question, casey.

Asked: April 13, 2002 - 8:23 pm UTC

Last updated: November 26, 2004 - 7:06 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,
I am working at a health care facility and am the only oracle person here.I haven't worked with oracle for very long but mainly do backups and some performance tuning.Our current security is fairly simple, privileges granted directly to the user such as execute on certain packages/procedures and the ability to select,insert,update,delete data based on dapartment id.
We also make use of the auditing feature in oracle and find it very useful. Our boss is planning on contracting a group of developers to develop a software package for us.

The developers are creating an application to be used over a wan that uses oracle backend for approx 500 users.They are creating a typical gui and middle tier and have a few ideas for building the database.
Even though I don't have alot of experience, I have an idea of how some things should be implemented in the database.I have voiced my opinion on this but they seem determined that they are correct and maybe they are, but I would sure like your opinion on this.
Here is the idea behind their database development setup which I think is very poor but wanted an expert oracle person to look at and see what they thought.
They will have an oracle database that contains only tables,views and indexes.Most of the work will be in the middle tier such as constraint checking. According to them this makes the application faster.Also they say it makes the application more flexible,making it able to use different databases because most of the code is in the application.Last,the security checking is done at the application level(middle tier) and they have their own auditing features which create their own tables in the oracle database.
Tom, are there any benefits from the above? Is it common practice to not put any code in the database? Their thinking is database code(procedures,packages,triggers,security,auditing etc...).This only slows things down is this really true.They also say that this type of development is similar to something IBM is using,how I don't know.Also the security is in the application.Won't this mean that I have full rein on the database outside of the application or any other user for that matter? Wouldn't this give users the ability to get into the database through sqlplus and do whatever they want or use an odbc link through access?I am expected to use this security setup once implemented how can this even be considered security.Would normal software providers offer security developed in this way?Also it doesn't seem necessary to create an auditing system when we already have one in the database.Besides the gui and middle tier, what benefits could we gain from such a system?

and Tom said...

They are doing you a great dis-service.

Ask them -- so, if you do all of the constraint checking in the middle tier, how will you handle the fact that Oracle does multi-versioning making it virtually impossible for you to do things as simple as "primary key"/"foreign key" checking? For example, how can you make it so that one session does not delete a parent record as another session is inserting a child record without using declaritive integrity in the dataase? You see, with multi-versioning, you get concurrent activities that you cannot in most other databases leading to issues like this. They MUST use declaritive constraints.

Next ask them this. How long has this really cool "middle tier" stuff been in vogue. What was the programming technique that was in fashion immediately prior to that. How about immediately prior to that? Oh wait -- you mean the way people develop applications changes over time -- say every 3 to 5 years? So the client server stuff you would have proposed to us 3 to 5 years ago would be legacy old stuff by now? So, in 3 to 5 years while we are still using this system (and we have to since you put the kitchen sink into it) and you guys are off using the coolest, newest "technology dujour" -- what will we do.

I'll tell you -- you'll be screen scraping like people do with mainframe systems so they can "web enable" them. I don't know what the next paradigm will be, all i know is that it will be.

What is the one constant -- databases. They have been there from the beginning. The applications, they come -- they go -- they change.

So, you are going to put the security into the middle tier? What happens when the users realize that "hey, I need to use Brio or Oracle Reports or Cognos or Product X" to get the data out of the database and work with it. Oh yeah, I cannot do that -- why -- because if I go around the application, I lose all security, i bypass everything like that. My only way to the data is via the application. I cannot use my own data -- especially you in health care with hipaa. If all of the security is in the middle tier -- no ad-hoc for you. No in house extensions for you. You cannot touch the data. Ask them how they will satisfy the hipaa requirements in this middle tier and still allow you to use YOUR data (yes, it is YOUR data, not theirs)

They will preclude you from making use of your most valuable asset -- the data.

Tell them to benchmark a simple thing like primary key enforcement. If this "slows down" the database so much -- why is it that they will NEVER build a middle tier application that can enforce (properly enforce) a primary key constraint faster then the database? I'll take on ANY constraint they want. The database will do that constraint enforcement faster, better, and cheaper. And oh yeah -- if the constraint changes over time, we can fix it ONCE (at the data) and have it immediately be in place for EVERY application. You don't have to hunt it down.

Don't forget -- IBM invented the host based systems that people screen scrape and pay consultants tons of money today to screen scrape...

Triggers, security, auditing, constraints -- they all have to be in the database.

I will take on any process they say goes "faster" in the middle tier that has to do with data integrity and data security. It'll be faster, better, and cheaper in the database. 100%

If they do it all in the middle tier, you have just lost the functionality of your database. No more sql for you. there will be no sqlplus access, there will be no odbc connections, there will be no 3rd report tools. There will be ONLY the application they feel like providing at the cost they desire to charge. It'll be lock in city.

Just my 3cents worth. I could go on but I believe you see my perspective on this.

I don't think they even KNOW what the database can do for them.

Rating

(12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

purchasing software using oracle database

casey, April 14, 2002 - 11:02 am UTC

Thanks for the response Tom.This review was very helpful.It confirmed with me that my line of thinking was and is correct.

Learning everyday

Suresh, April 14, 2002 - 7:34 pm UTC

Tom,
You are the best.

Thanks, but

Jean, April 14, 2002 - 9:23 pm UTC

Not up with the lingo.

"screen scrape" ???

April 15, 2002 - 7:35 pm UTC

screen scraping is what you do when you have a "green screen" application, a mainframe app for example, whereby the only interface/api to the data is via a screen that a human being must enter the values into.

To "reuse" this code, you "scrape the screen", present the form to the user in another UI, collect the data and pretend to type it in for them.

Hear hear !

Norbert, April 15, 2002 - 3:13 am UTC

This sounds all too familiar. Some 'consultant' type performs an investigation and concludes that method X (any language will do, as long as it's new and mr consultant is eager to do his first project on it) is the way to go. No more code in the database, which comes in handy, because he never heard of PL/SQL, nor Oracle now you mention.
And then, when you ARE going to develop using Java/C#/..., they can provide you with their developers, who are also eager to try what they learned on their courses.

But the database can also change

Pratap, April 15, 2002 - 6:47 am UTC

Hi Tom

I fully agree with your views.

But there is one argument I frequently come across -

The underlying database can also change. E.g. SQL server to Oracle. If I write all the logic in the application, won't the switch over be faster with less changes?

Thanks

Pratap

April 16, 2002 - 7:21 am UTC

Here is my opinion on that (it's in my book as well)...

You make choices in life, you buy products. Why do you buy products? Because they do things for you.

When you buy a car, do you ask "how hard is it to remove that engine and put a new one in?" -- you might want to take that BMW engine out and put a Mercedes one in there after all (not).

Today -- you want to get things done faster/better/cheaper. They have to be rolled out quickly AND they must be flexible, adaptable, scalable and secure. Put all of your logic into the application -- and you can do neither.

First of all -- you cannot break the work up. You have one guy doing the application and security logic (maybe you have many of them but one person must do both). This makes development slower, less specialization. This makes testing take longer (have to check security in each and every module over and over). This makes adding things harder (gotta do that security all over again). You are not flexible, you are not adaptable, it is not a scalable development solution.

Next, you cannot adapt to change. Client server -- 3 tier with cgi bin -- 3 tier with J2EE -- N tier with web services -- WAP -- etc etc etc. You want to put security into each and everyone of those every time they come along.

My basic point is:

o buy your product
o use the $#@$#@ out of it -- whichever one you buy. we are FUNDEMENTALLY DIFFERENT. If you want database "independence", you need keyed read vsam files and nothing more. If you bought a database -- USE IT

I wonder why Oracle Application dont have constraints in database

A reader, April 15, 2002 - 6:49 pm UTC

Although you mentioned that everything i database is fast I wonder why many applications such as siebel, sap, oracle applications many times (not always) dont have relationship in the database

If I rememeber right (this was long time ago, around 2 years) Oracle Applications 11.0.2 have 0 Foreign keys in database

April 16, 2002 - 7:46 am UTC

Oracle apps makes large use of many database features. triggers, stored procedures, tons of stuff. It is database dependent.

Sieble/SAP -- they have taken a database independent approach, they are not able to use any of the features of a database beyond:

o tables
o indexes

Did those guy know Oracle or RDBMS

Ringo Li, April 15, 2002 - 7:57 pm UTC

To put it simple, they didn't know the Oracle at all.

Do not let them screw up your application. I did and now still suffering. what I am now doing is to re-build all parent child constranit table by table and dig out all concurrency problem. Guess what, our schema does not have any declarative constraints !

Why don't you ask those consultant for some successful implemantation of Oracle DB backed application for reference ?

I doubt if they can provide any ?

What about same checks being performed both at the front-end and database?

Vijay Raigaonkar, April 18, 2002 - 4:34 am UTC

Hi Tom

I too am an Oracle professional for quite some time now and strongly feel that the constraints must be there in the database. But I do not know how to counter a typical argument or statement going in favour of "not having the constraints in the database". The argument goes like this...

We usually require to do the validations in middle/front tier to give immediate response to the user. Many of the validations duplicate the constraints in the database (if those are there). This makes the overall response slower (by whatever amount of time) than the scenerio where we do not have any constraints in the database.

April 18, 2002 - 8:36 am UTC

Fine, thats great - put the constraints in the front end for validation. However, my argument still applies:

o the integrity of the data is job #1. If the data is corrupted, you have not done your job properly.

o if there is a chance of getting to the data without using your application -- without using your validation -- the data WILL BE CORRUPT.

o let me change the "IF THERE IS" to "There is", there always is. Remember, in the span of about a decade we've gone from:

- host based
- "client server" (with a slight divergence to X-Windows for a bit)
- cgi-bin on a webserver
- App server applications (j2ee/.net/.whatever)
- now "web services", WAP, etc etc etc

The speed of checking the constraint in the database to the end user will *not be measurable*.

The corrupt data, when presented to the end user *will be very visible*.

Tell me then -- which do you want?

Also -- are you sure the each and every front end

a) knows all of the validation rules?
b) should have to know all of the validation rules?
c) should be updated everytime there is a change in the validation rules?
d) CAN in fact do all of the validation rules (think PHONE interface, or IVR)

and the list goes on. People can do whatever they want, if they want to be maintaining that system for the rest of their natural lives -- thats great (for them) but a dis-service to the customer (IMHO)...

I put the logic in both places, its the only way to do it.

Siebel 7.7

A reader, November 25, 2004 - 5:16 pm UTC

Siebel 7.7 is certified and fully supported only on Oracle 9iR2 & CBO

But when I looked at the installation guide, they require the following init.ora parameters

hash_join_enabled=false
optimizer_index_cost_adj=0
optimizer_index_caching=100
and a handful of other RBO-ish parameters

Also, all sessions connecting to Oracle do 'alter session set optimizer_mode=first_rows_10'

Wont these kind of settings cripple the CBO's ability to determine the optimal execution plan? Dont these settings almost turn the CBO into the RBO? (Siebel 7.5 required optimizer_mode=rule in the init.ora!)

Thanks

November 25, 2004 - 8:15 pm UTC

none of those are rbo'ish.

the RBO would not be affected by any of them.

looks like they are "first rows" happy -- which for a transactional application may well be true. I would think that many reports will change those settings, allowing for hash joins, full scans and so on.

A reader, November 25, 2004 - 5:19 pm UTC

"I put the logic in both places, its the only way to do it"

Why? Why not just put it in 1 place, the database? This way all apps can immediately benefit from it? Add one more business rule, just 1 place to add it?

November 25, 2004 - 8:17 pm UTC

If it is not in the client app -- at least the OBVIOUS edits, the end user would hate it.

fill in form, submit, find the first error
fix it
submit, find the second error
fix it
submit, find the third error
fix it,
submit,.........

so, you have a field that should be a number between 0 and 5 -- database MUST enforce it, client CAN enforce it -- to make end users life "better"

Validation in client app

A reader, November 26, 2004 - 7:06 am UTC

I agree. Although, most (web based) client apps that I have seen also behave in a one-at-a-time mode i.e. fix one error, submit, fix the next, submit! Older client-server apps have more "real time" checking of each field (after tabbing out of it for example).

Oh well, I guess Oracle v15 will have a init.ora parameter to "return_all_errors=true" so that if a SQL statement violates 10 constraints, the error stack will return all 10!

validation

A reader, December 23, 2011 - 12:11 am UTC

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer