Re: Business logic in DB? (was Re: ref_cursor was (Sybase vs Oracle)?)

Here's how we do it (at PACCAR, Inc. in Renton, WA):

All access to the databases (selects, inserts, updates and deletes) is handled via fundamental stored procedures and triggers which are maintained by the DBA group. Complex stored procedures, which may contain application logic and which may reference the fundamental stored procedures, can be maintained by the applications programmers. Each application database has its own dbo alias login/userid, which gives selected application programmers dbo authority in that database only.

In two-tiered applications, all business logic must reside in complex stored procedures on the database. In multi-tier applications, business logic may reside either on the middle tier or in complex stored procedures, as determined by the application designers. However, all access to the database is restricted to the fundamental stored procedures.

                                         Larry Burns
                                         DBA Group
                                         PACCAR MIS Truck Systems
                                         Renton WA, USA

In article <366f2a80.0_at_paperboy.telerama.com>,   "Daniel Clamage" <dclamageNOSPAM_at_telerama.com> wrote:
> You could put business rules into the database (in the form of stored
> subprograms and triggers that validate/clean data), then have a middle tier
> server program scan the business rules and serve them up to clients, so that
> the client application can validate/clean data before it's submitted to the
> database.
>
> This is analogous to a train control system where the hardware interlocking
> logic, which uses vital relays to create a safety system, is simulated in
> software so that switch movement requests can be made with a high certainty
> that they will be granted (this is how several PeopleMover transit systems
> at international airports were built).
>
> --
> - Dan Clamage
> http://www.telerama.com/~dclamage
> If you haven't crashed the Server,
> you haven't been trying hard enough.
> Joanna Warrens wrote in message <366DE96D.21880A7C_at_ix.netcom.com>...
> >Good points -- I'm about 85% with you.
> >
> >What I should have made more clear is that I believe a "middle tier" is
> >where the business logic should reside, rather than the database.
> >Perl client A and C++ client B shouldn't be reinventing the same wheel.
> >And you're correct -- middle tier infrastucture (CORBA, Java RMI, DCOM)
> >is still an unproven quantity, at least to our group.
> >
> >That said, I'd rather have business logic in a small set of well-known
> >Java or C++ class files, rather than in the DB.
> >This is not only for the mechanical and political implementation ease,
> >which may be unique to our organization, but also for my personal
> >perception that stored procedures/ triggers/ PL/SQL are
> >a relatively obscure repository for business logic.
> >
> >Perhaps it comes down to my belief that I consider a database
> >to be the place where we park the world when we go home at night,
> >rather than where we actually work/compute/do business logic.
> >I might change my mind when Java becomes widely available within
> >Oracle/Sybase/etc.
> >-- wade
> >
> >Joe & Anne Buhl wrote:
> >>
> >> Sure it's easy and quick to put the logic in the client. But then try
 and
> >> track it down when there is a problem or it needs modification. There is
 a
> >> benefit to approving the logic at your discussion groups.
> >> everyone should be aware of what is taking place and problems can be
 fleshed
> >> out then and their.
> >>
> >> If you have developers sprinkling business logic all through the
 application
> >> it can become a nightmare to track down. If your going to put the logic
 on
> >> the client you should still have your approval discussion
> >> and have a centralized place for your logic. But that still does not
 stop it
> >> from being code dependent.
> >> Write some business rules in a c application or forms and then you have
 to
> >> repeat the logic if you write something else in Perl. If it's in the
> >> database it can be used by more applications. CORBA stuff still seems
> >> immature. Bottom line is wherever it ends up it needs strict control and
> >> proper management
> >> ( your weekly discussion).
> >>
> >> joe
> >>
> >> Joanna Warrens wrote:
> >>
> >> > Adrian Hands wrote:
> >> > <snip>
> >> > > The benefit's of PL/SQLs tight coupling to the database are very
> >> > > valuable indeed.
> >> > >
> >> > > ...however...
> >> > >
> >> > > my current feeling is that (with Oracle 7, at least) we'd do better
 to
> >> > > use a little less PL/SQL.
> >> > > At least take those procedures that run for more than a few minutes
 and
> >> > > move the outer loop to the client/host language so that the client is
> >> > > making shorter duration calls to Oracle. This is because PL/SQL can
 be
> >> > > really difficult to debug.
> >> > <snip>
> >> >
> >> > Our company has at least a dozen production Sybase servers,
> >> > each with it's own replicated warm-standby server.
> >> > Many applications are beginning to make use of
> >> > subscription replication to distribute data from one
> >> > production database to another.
> >> >
> >> > Any new or altered table, trigger, or stored procedure must
> >> > be formally announced at each Monday's tech meeting, and
> >> > then must be blessed and installed by our dba group.
> >> >
> >> > However, applications that use the databases (perl, C++, Java, etc)
> >> > generally do not need to be so blessed.
> >> >
> >> > The net result is that it is difficult to embed procedural logic
> >> > into the database (via stored procs). However, it is quick and
> >> > easy to embed such logic in a perl/C++/Java program.
> >> > Also, it's quick and easy to test such logic against production data.
> >> >
> >> > For this reason, I generally recommend against placing business logic
> >> > in the database. On the other hand, I do like referential integrity
> >> > constraints, so I'm trying to have my cake and eat it too, I guess.
> >> >
> >> > How do other folks see this? DBAs? Developers?
> >> > --wade
> >> > ps: xposted to c.d.o due to pl/sql mention.
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Dec 10 1998 - 00:00:00 CET