Re: Implementing "thick"/"fat" databases

On 24/07/11 17:51, Chris Travers wrote:
>> I was thinking similar thoughts, but you not only beat me to it, you made
>> some good points I had not thought of!
>>
>> The only thing I can think of adding: is that it would be good to lock down
>> the database so that only the middleware can access it, everything else
>> accesses the database via the middleware.
> In general, I am not convinced that middleware is inherently more
> maintainable than in-db procedures.
>
> But the fundamental question is:  Is this a a one-application
> database?  If it is, you can use the middleware to be that application
> lock the db down so only the middleware can use it etc.
>
> But what if it isn't?    What if we want to support a variety of
> applications against the same relational database?  This has to be
> fairly commonplace.....
>
> In this way my experience is that it is often helpful to maintain
> several levels of stable, public API's both on a table level if
> possible (as attachment points for triggers), stored proc API's for
> actually inserting data into relevant areas while enforcing
> appropriate business logic, and so forth.
>
> One of the things we are doing in LedgerSMB is to make the stored
> procedures discoverable, so the argument names (and eventually the
> return types) will have meaning the application can use in building
> calls for the procedure.  This eases one important maintenance point
> because arguments are automatically picked up by the application and
> as long as best practices in coding are followed, will be handled
> sanely.  (The interface will be extended in the future so that return
> types determine the class, and the arguments in determine whether we
> are talking about a presumed object property or a presumed
> application-specified argument.)  Theoretically, we should be able to
> build objects in languages picking up methods and properties from the
> Pg system catalogs but we haven't gotten that far yet with code
> generation.
>
> Best Wishes,
> Chris Travers
So it really boils down to 'It depends...'  :-)

I first started designing systems over 30 years ago.  I remember my
first design principle I came up with, but more importantly that my next
project ignored it for good reasons (same mainframe COBOL environment in
both cases)!

I feel that for a large company, then the middleware approach is
probably better when you have many diverse applications that share a lot
in common, but it depends on many different factors.