Re: Put code inside or outside database?

"GeoPappas" <PappasG_at_gmail.com> writes:

> 3. Use a combination of 1 and 2. In other words, put some code outside
> the database, and some code in the database.
>

We use a mixture of both. We try to avoid having much DML in the application and make extensive use of cnstraints, triggers and stored proceedures.

I've seen some pretty bad apps which have gone the other extreme. You often find aps which claim to support multiple db engines which achieve this by avoiding DB features, using a generic connection mechanism like ODBC and pretty much using the database as nothing more than an over priced storage mechanism. All constraints, data checking and DML is done at the application layer. Systems like this tend to have higher netowrk traffic between the DB engine and the apps server, tend to be less efficient at processing data as its more difficult to take advantage of caching and pre-parsed queries etc - essentially you loose all the DML optimization work Oracle is able to do - sort of like the optimization you normally only get in a specialised system like a DBMS and not in a development language like Java or Perl etc. These sort of apps are often more complex and have larger code base because it has to handle constraint checking etc at the app level.

We use to do almost all our work at the database level. The rationale here was a mistaken belief this would make maintenance easier and staff would require fewer skill sets. However, doing everything at the db level makes development extremely inefficient, especially if you need to interact with the OS or filesystem. Compare PL/SQL with Java or Perl in processing input from a CSV formatted file or generating a report etc.

I think the best approach is to use the right tool for the right job. The database is designed an optimized for DML/DDL, so use it as much as possible for that. Put as much of the logic into the database as you can (constraints, triggers etc).

Put your interface code (human, system, network etc) in the application and use an application language which is suited to your problem domain.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!