Re: theory and practice: ying and yang

"Kenneth Downs" <knode.wants.this_at_see.sigblock> wrote in message news:i0fum2-icd.ln1_at_pluto.downsfam.net...
> Alfredo Novoa wrote:
>
>>>
>>>My thesis is that if you take this migration to its logical
>>>conclusion there may be achievable an optimal stage in
>>>which *all* application software in internal to the DBMS
>>>in the form of stored procedures.
>>
>> Declarative code should be favoured over procedural code.

Stored procedures can be used declaratively. They can also be chained declaratively in an unlimited series.

They can also be presented to users within workgroups within an organisation by means of a menu arrangement, itself a declarative stored procedure.

>> Stored procedures should be used as a last resort to implement
>> business rules.

The simplest and easiest way to implement business rules (IMO) is to exhaustively allocate a series of status codes (not null) to the granular detail items which are flowing through the system.

These statii reflect and identify the life-phase of the detail item. Grouping/selecting by this status thereby provides detail items in the organisation which are in the same life-cycle phase.

Rules are then implemented and defined for these status conditions, and may be as simple as a table with specific parameters and values, often related to the system date.

This is mainly table data, yes ----- because you need the ability to be able to change the rules easily via centrally referenced parameters ---- but (declarative) stored procedures may be used around the edges to pull it all together.

>> End of story.

The end of the story is that I prefer to use stored procedures instead of code that necessarily must dwell outside the RDBMS environment (for example, embedded in VB or C on a client).

This of course brings up another issue, and that is the intellectual property associated with the code, and a reason that many do not like to follow the path of using stored procedures.

The simple reason being with client bound code, you have source and executable, and the source can be "hidden". Using stored procedures (unless you encrypt them, which I dont) every man and his dog can see how the application works.

But I dont see this as a problem.
Some however do.

> But then you have the classic loophole problem. As soon as you allow that
> Sprocs are used for "last resort", you have to spell out the conditions
> under which they can be used and suddenly everyone is claiming they've got
> a special case and *must* use a sproc.
>
> This is why you have to support them either entirely and systematically,
> or
> not at all, you can't have "last resort" case.

My tendency is to support them entirely and systematically from the ground up, commencing with a register by which the procs are known to the evolving system.

Also, rather than using a small number of extremely generalised all-purposeful
procedural sprocs, I invariably use a larger number of small specific and declarative sprocs, all of which are registered in a table.

Because many of these procedures are used for information reporting, you find that many of the procedures are able to be used in multiple places.

For example, consider a proc which accepts an invoice number, and then returns the invoice detail items. When using this drill-down approach, whenever you have any report listing invoices (ie: for client, for job, for employee, for financial exception listings, etc, etc) then you can chain the same procedure (show invoice detail). ---- Reusable objects.

-- 
Pete Brown
Falls Creek
OZ
www.mountainman.com.au