Re: choices regarding where to place code - in the database or middle tier

"Joe" <joelax_at_dbdirections.com> wrote in message news:ULcQb.466$KU5.37_at_nwrddc02.gnilink.net...
> Jim -
>
> Thanks for your response. I agree with your points. I'll try to clarify my
> question regarding the areas that still bother me.
>
> Before Oracle decided to allow you to write stored procedures in Java,
they
> had this really nice language (as you pointed out) called PL/SQL. It seems
> to allow you to do just about anything you would want to do with your data
> and they've continued to enhance it with each version of Oracle. (though I
> haven't looked at 10G yet myself)
>
> Then along comes Java, a language that attempts to solve a whole different
> set of problems, and not necessarily the same ones that PL/SQL was
designed
> for. Even without Java existing, let alone being hosted in the database,
you
> can still have all the same arguments about where to put your business
> logic. However, at least some of the choices would be clearly delineated
by
> what language was being used. If you found a particular problem that had a
> better solution in PL/SQL, it was going to live in the database. If either
> because of the nature of the problem, or a particular programmer's
facility
> with something besides PL/SQL , a particular business rule or process was
> expressed in another language, it would typically live outside the
database.
>
> But along comes Oracle and takes Java and hosts it in the database. (Why,
> I'm not sure. Was it just to grab the Java crowd?) Now, you have a
situation
> where you have the very same piece of code that can live in the database
or
> in your middle tier. What drives the choice then?
>
> In the Microsoft world, the situation is a bit different. SQL Server
never
> had as well developed a language as PL/SQL. I've still managed to support
> large systems with stored procedures using T-SQL, but it lacks features
that
> Oracle programmers take for granted such as packages, decent error
handling
> (although that will be improved in the next version) , the list goes on
and
> on. In the Microsoft world, the database was never treated as the center
of
> the development universe in the way that Oracle seemed to consider their
> product. But Microsoft with the next version of SQL Server, is taking a
set
> of languages that they developed for the middle tier, and putting it in
the
> database. (As opposed to the Oracle situation, this isn't even a way to
> reach out to a different developer community like Java programmers, .NET
> developers are already in the Microsoft camp.). When I look at the
> literature on the choice Microsoft has made, what I get is a series of
> examples demonstrating problems that are more easily solved in .NET than
in
> T-SQL. What I don't see then is any reason that I shouldn't simply have
this
> code in the middle tier. In fact, the more I read about it, the more I am
> curious as to what drove Microsoft to put this feature in.
>
> I've received a lot of valuable feedback from numerous people in this
> thread. Some of the conversations have been at a strategic level such as
"Is
> it a good or bad development that database vendors seem to be merging the
> database and middle tier?". What I've gleaned from the converation so far
> is
>
> 1. Most people agree that you shouldn't forgo declarative referential
> integrity in favor of rules in the middle tier. In that way lies data
> corruption. (for those of you that disagree - please note I said "most")
>
> 2. When you get to situations that require very high performance, it's
worth
> taking a look at a TPC Monitor or other middleware solution. It's what
> database vendor's themselves do to get the utmost best performance.
>
> 3. You can get into awkward situations by forcing everything into one
layer
> or another - witness some of the stories regarding various vendor
packages.
> One of the ways to judge where to put a particular peice of code and how
to
> write it, should be clarity and maintainability.
>
> I'm still trying to clarify for myself situations that fall in the middle.
> So, take a situation where the code in question will perform adequatly in
> either tier, and assume we are not talking about enforcing declarative
> referential rules. Where should this code go? What other factors besides
> performance come into play?
>
> To me, it's not just a theoretical question to be happily argued over a
beer
> or two. I currently support clients using SQL Server. In around a year,
all
> the .NET developers out there will be faced with this choice. Many of them
I
> suspect won't think much about it, but merrily use either the left mouse
> click or the right to make their selection (Microsoft makes somethings way
> too easy to accomplish in opinion). I'm a pragmatist enough to know that I
> can't just say "Don't use this feature, put all your code in the middle
tier
> " or the opposite answer either. Instead, I'm looking to learn from what
has
> already taken place on the Oracle side of things about the consequences of
> such choices.
>
> How is code maintenance affected by these choices? Is it easier, harder,
or
> really has no overall effect on projects if Java code lives in two places?
> Does tuning performance of code become harder?
> Are people seeing cases of poorly performing instances because the Java
> code being hosted is not written will?
> Is this causing more situations where the programmer points at the DBA and
> the DBA points at the programmer?
> Who actually decides where the code should reside?
> Has it caused more security issues?
>
> I'm sure there are also other issues that I haven't thought of, which
again
> is the reason for posting these questions in an Oracle forum, where you've
> had to deal with these issues on a practical level for several years now.
>
> Thanks
>
> Joe Lax
> joelax_at_dbdirections.com
>
>
>
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message
> news:QX3Pb.103910$8H.177187_at_attbi_s03...
> >
> > "Joe" <joelax_at_dbdirections.com> wrote in message
> > news:Y7IOb.20178$ko5.19870_at_nwrddc01.gnilink.net...
> > > Hi -
> > >
> > > Over the last several versions of Oracle, developers have been
provided
> > with
> > > a pretty revolutionary idea for a database product - namely the
ability
> to
> > > write code that used to belong in the middle tier and store it in the
> > > database. I'm referring here to the ability to write stored procedures
> in
> > > Java.
> > >
> > > Now of course, Microsoft with their SQL Server product is doing the
same
> > > thing. The next version of SQL Server will allow programmers to write
> > > stored procedures in any of the .NET languages.
> > >
> > > I'm interested in looking at the increased choices developers now have
> > > because of these new features in more depth ,developing some best
> > practices
> > > on the subject, and possibly publishing an article on the topic.
> > >
> > > I personally am more experienced with SQL Server than with Oracle. I
am
> > > therefore looking for someone who has been involved with making these
> > > choices in the Oracle environment who would like to collaborate with
me
> on
> > > the subject.
> > >
> > > If you are interested, please contact me at joelax_at_dbdirections.com
> > >
> > > Thank you
> > >
> > > Joe Lax
> > >
> > >
> > >
> > >
> > >
> > Joe,
> > I want to make a subtle distinction. Just about any database can store
> code
> > in a the database. (binary object) That said I think you mean more that
> > complex business logic can be stored and run in the database or server
end
> > (eg in Oracle pl/sql or Java). Having the business logic (not the GUI
> > logic) in the database allows one to switch GUIs or have multiple
systems
> > interact with the backend and consistant business rules are followed.
> > Having it in the middle tier means that every other system has to go
> through
> > that middle tier. Which means that other groups will go right to the
> > database and not through the middle tier.(time constraints, must do it
> now,
> > can't wait to use middle tier, middle tier written in a language we
don't
> > like or don't know....)
> >
> > Siebel, Peoplesoft et al hire programmers and not really dbas.
> Programmers
> > drive the projects and dbas are relagated to a lower importance. Thus
> these
> > products don't use Referential integrity, stored procedures etc. For an
> > example, in Siebel you "have to define all database objects through
their
> > tool even indexes". Unfortunately, that means you can't create a
Function
> > based index or an index where one of the elements of the key is
descending
> > instead of the default ascending. Dumb, just dumb.
> >
> > Jim
> >
> >
>
>
>

Joe,
Put the RI and business rules in the database. Anything that has to do with maintaining data integrity and related matters goes in the database. For Oracle, I would use pl/sql where that fits the best and use Java where that fits best. This gives you great flexibility to allow a whole plethora of applications written in whatever the business thinks is best to write them in. (Java, Cobol, APL, Fortran, C, whatever) This also means you can have client server, web based applications, multi-tiered apps, etc. all accrssing the same database and one set of business rules applying to all of them. This does not mean that those applications can't have logic in them, (eg edit rules, format rules, just the database will enforce those rules.) By putting the business rules in the database allows one point of QA for all those applications from a data point of view. (data consistency)

Having worked for a variety of companies for a couple of decades one a variety of projects (all involving databases) I can say from a pragmatic point of view that it is rare that one application and only one application is going to access the data.(I am not talking about a small address book application)

Jim Received on Fri Jan 23 2004 - 22:34:59 CST