Re: Implementing "thick"/"fat" databases - Mailing list pgsql-general

From Darren Duncan
Subject Re: Implementing "thick"/"fat" databases
Date
Msg-id 4E2A24C5.90208@darrenduncan.net
Whole thread Raw
In response to Re: Implementing "thick"/"fat" databases  (John R Pierce <pierce@hogranch.com>)
List pgsql-general
John R Pierce wrote:
> On 07/22/11 4:11 PM, Darren Duncan wrote:
>> Karl Nack wrote:
>>> I've been following a few blogs
>>> (http://database-programmer.blogspot.com/,
>>> http://thehelsinkideclaration.blogspot.com/) that make a very compelling
>>> argument, in my opinion, to move as much business/transactional logic as
>>> possible into the database, so that client applications become little
>>> more than moving data into and out of the database using a well-defined
>>> API, most commonly (but not necessarily) through the use of stored
>>> procedures.
>>
>> I strongly agree with that design philosophy.  One principle is that
>> the buck stops with the database and that regardless of what the
>> application does, any business logic should be enforced by the
>> database itself.  Another principle is to treat the database like a
>> code library, where the tables are its internal variables and its
>> public API is stored procedures.  Using stored procedures means you
>> can interact with the database from your application in the same way
>> your application interacts with itself, meaning with parameterized
>> routine calls.
>
> the alternative 'modern' architecture is to implement the business logic
> in a webservices engine that sits in front of the database, and only use
> stored procedures for things that get significant performance boost
> where that is needed to meet your performance goals..  Only this
> business logic is allowed to directly query the operational database.
> The business logic in this middle tier still relies on the database
> server for data integrity and such.    The presentation layer is
> implemented either in a conventional client application or in a
> webserver (not to be confused with the webservices)....  so you have
> user -> browser -> webserver/presentation layer -> webservices/business
> logic -> database
>
> The main rationale for this sort of design pattern is that large complex
> business logic implemented in SQL stored procedures can be rather
> difficult to develop and maintain

I should clarify that the primary thing I support, with respect to putting it in
the database, is the business rules/constraints, because the buck stops there.
It should not be possible for any database user lacking in data-definition
privileges to circumvent any of the business rules.  So one can not circumvent
by using a generic SQL shell, for example.

As for the rest, yes I agree with you that this doesn't have to actually be in
the database, though from a standpoint of good design principles, all of the
business logic should still be in one place, next to if not in the database, and
that all database access should go through the business logic layer.

All logic that is not specific to an application should go in a logic layer, so
it is shared by multiple applications whether web or command-line or whatever,
and so then the application is largely just a user interface.

In other words, thinking in the Model-View-Controller paradigm, the Model should
be fat and the Controller should be thin.

-- Darren Duncan

pgsql-general by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: Locking several tables within one transaction
Next
From: Alban Hertroys
Date:
Subject: Re: Update columns in same table from update trigger?