Re: "We don't do triggers"

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:<3fc10fe1$0$7363$cc9e4d1f_at_news.dial.pipex.com>...
> "Peter Connolly" <peter_at_alum.wpi.edu> wrote in message
> news:39fde041.0311211053.7887c44a_at_posting.google.com...
> > In addition to portability, another reason for specifying no stored
> > procedures could be to ensure that there is no business logic in the
> > database procs. In an n-tiered system, all business logic should be
> > on the application server, not in the GUI or the database.
>
> I really, really don't understand this. Perhaps you could elaborate?

In any 3+ tiered system, there is a GUI (i.e. web server + client), a backend to store data (i.e. database, LDAP, etc), and an application server to implement the business logic. See my previous posting for examples.

> >
> > >1. NO DATA LOADS EVER. Key it through the app ALWAYS. even 10 million
> > > rows.
> >
> > I don't think data loads should have to be "keyed" through the app.
> > If all data loads were required to use the application code (i.e.
> > passing data to an EJB) to insert data that could be a very good
> > thing, if it is feasable.
>
> Is it feasible? Ever? You acquire a new company and need to load their sales
> ledger. Is it at all plausible to load this by passing it to an EJB? You
> start a new financial year in your accounting company and decide to open new
> projects for all of your clients (say 50,000)? etc etc.
>
> Even if it is feasible how long does a dataload of 2million 500 byte records
> take when passing it to EJB? what about sqlloader?

I've loaded almost 3 million records from a 50MB EDI file (800MB XML) through a vb.net component in under 4 hours on a xeon 3Ghz. I don't have stats on an EJB, but I would guess that its comparable. sqlloader doesn't load EDI or XML, but I'm sure it would go much faster if it did. Regardless, the vb.net system accepted all different formats (EDI, XML, csv) and all were passed through the same logic to load into the database. Have you ever tried to read an 8 level nested EDI document with a stored proc? Its near impossible.

>
> > All data would be scrubbed and validated
> > the same way. I've seen too many databases that have
> > dirty/inconsistent data due to heterogenous data sources (i.e. five
> > different ways to calculate taxes and split pennies). Performance is
> > always second place to correctness.
>
> So these databases where did they calculate the tax and split the pennies?
> Pound to a penny it was in some app.
>

No, it was in 5 apps populating a few different databases. These 5 apps each had their own methods of calculating how to split pennies. Some of them used stored procs to do it. Are you saying that pl/sql and triggers are not part of an application if they are used? Are they inherently part of the database simply because they are packaged with Oracle?

>
> >
> > >2. NO INTERFACES TO OTHER SYSTEMS, EVER. make other systems talk to
> the
> > > app.
> >
> > This could be a good thing too, for the same reason data loads should
> > be done through the application. Why should a company pay for
> > developers to implement the same logic multiple times?
>
>
> Why should they pay to write interfaces for every new or upgraded system
> they buy?

Your business will either decide they need the data integrated or they will decide they don't. Your company will be required to write some code (pl/sql, java, etc) to get the systems to talk. The point I'm making is that choosing an application for the interface instead of stored procedures has many benefits. For example, would you want to give an Oracle login to every business partner of your company's so that they can re-use your business logic?

>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
Received on Tue Nov 25 2003 - 08:44:21 CST