Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: gry@ll.mit.edu, pgsql-sql@postgresql.org
Subject: Re: Design Q.:logic in app or db?
Date: Wed, 26 Feb 2003 10:25:00 -0800
User-Agent: KMail/1.4.3
References: <20030226103220.6875cee9.gry@ll.mit.edu>
In-Reply-To: <20030226103220.6875cee9.gry@ll.mit.edu>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200302261025.00360.josh@agliodbs.com>

George,

> I have general design question about Postgres usage:  How does one decide
> how much, and what parts of logic should go in DB rules, triggers,
> functions, constraints etc, versus what should go in the application?

Ideally, this should be done strictly on the basis of carefully planned des=
ign=20
architecture, where each business rule is implemented at the application=20
level where it is most effective, such as referential data integrity in the=
=20
database and security in the middleware.

Realistically, business logic tends to be implemented in the layer where yo=
u=20
have the most programming expertise.   The people on this list are often=20
expert DBAs, so they implement as much business logic as possible in the=20
database in the form of views, triggers, and rules.   However, I"ve seen=20
plenty of shops with multiple crackerjack Java programmers and a weak DBA=
=20
implementing all of their business logic in the middleware.=20=20

> The main thing not done yet is to facilitate ad-hoc queries
> (via odbc excel etc.) from db-naive users:  maybe restructuring the
> db to make it simpler, maybe views and functions... The data is
> somewhat complex in structure.

This desired feature would argue strongly in favor of putting as much busin=
ess=20
logic as possibly in your database in the form of views and rules.   If use=
rs=20
can bypass the interface and middleware, you cannot rely on it to enforce=
=20
data integrity and access control.

--=20
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco