Fwd: Real-life PL/SQL these days ...

From: Stephen Booth <stephenbooth.uk_at_gmail.com>
Date: Tue, 14 Apr 2009 16:09:58 +0100
Message-ID: <687bf9c40904140809k12d7dc28gf88b2d6e5baeb45a_at_mail.gmail.com>

2009/4/14 Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us>:
> Stephan,
> Well Said!  A brilliant comparison to todays world.  We *are* in the "Ponzi" scheme of development right now.  Can anyone say "SOA"?

It often seems to me that commercial development has moved from spending developer time to save user time (i.e. the developer spends hours shaving a few seconds off each transaction because over the lifetime of the application those few seconds will add up to many, many hours) towards saving developer time to spend user time (i.e. developer writes code quickly to get the product to market quicker, as a result the code is suboptimal so the users waste many, many hours over the lifetime of the product waiting for transactions to run).

> I've tried to convince folks of PL/SQL as a development tool.  A legitimate response was "we don't want to have business logic spread across development platforms".  And this does make sense.  No matter what, there is business logic in the front-facing part of the application and it would be difficult to manage/decide what logic goes where.

You do need to be very much on top of your configuration management and have a good idea of what goes where and why.

  • Things like constraints, access control and code that implements business rules it makes a lot of sense to put as close as possible to the data.  That is, in the Datastore layer.  This mitigates the risk of someone accessing the database directly and changing the data. Good access control restricts the data they can change, solid constraints and code to implement business rules ensures that at least any changes maintain the integrity (referential and otherwise) of the data.
  • For transactional code it makes sense to put it in the Transactional (what most people called Application) layer.
  • Presentation code should sit in the presentation layer.

Segregation like this is especially good when you're likely to have more than one presentation layer (e.g. a Java based desktop GUI plus a web browser interface and interfaces to one or more mobile devices) or more than one application accessing the same data (e.g. CRM, order processing, shipping, billing, procurement and credit control apps all accessing the same customer and inventory data).

If you put everything in the application and you need to support a new access method (e.g. a new mobile device) you have to edit working code to support the new access method.  If you segregate presentation code from transactional code you can leave the working code alone and just write a new presentation layer to call the transactional code.

If you put everything in the same application and have multiple applications accessing the same data then you're making an assumption that all the applications implement the same business rules and constraints.  If a business rule changes you also need to change change every application.  By segregating the transactional code from the integrity code you can implement a business rule change once, in the datastore, and it will work in every application without touching the transactional code (so long as you have robust exception handling, of course).

> It's not an easy decision.  The only thing I know is that we get to deal with the consequences (poorly written sql) on the back-end.

An increasingly common problem.  Short of writing an abstraction layer, you're stuck with it.


It's better to ask a silly question than to make a silly assumption.

http://stephensorablog.blogspot.com/ |
http://www.linkedin.com/in/stephenboothuk | Skype: stephenbooth_uk

Apparently I'm a "Eierlegende Woll-Milch-Sau", I think it was meant as
a compliment.
Received on Tue Apr 14 2009 - 10:09:58 CDT

Original text of this message