> On Wed, Dec 10, 2008 at 05:58:08PM -0600, Jason Long wrote:
> > I need to add some complex constraints at the DB.
> >
> > For example.
> >
> > Do not allow a line item of inventory to be changed if it does not
> > result in the same number of joints originally shipped.
> >
> > These will involve several tables.
> >
> > What is the best approach for this?
>
> You might want to talk to people who have done bookkeeping
> applications for PostgreSQL, or possibly even buy one of the
> proprietary PostgreSQL-based systems for it, as this stuff can be
> fiendishly tricky to get right.
>
As a developer of just such a bookkeeping application, here's (IMHO)
the best way to handle this: Wrap the dependent operations into one
stored procedure, grant rights to that procedure and not to the
underlying tables. If an operation (such as shipping product)
requires multiple database queries and updates, wrapped in a
transaction, don't place your trust in every user and/or application
to do that properly.
In your example, don't give the user or application UPDATE permission
to the raw inventory or product tables, that's just asking for trouble.
Instead, create a ship_product() procedure that takes all the steps
required. (You'll also need others, adding inventory for example)
There are many, many benefits to reap once you've made the commitment
to doing this. More re-usable code, a stable API, atomic operations,
faster transactions, less traffic over the wire, etc etc.
I would still add critical constraints and triggers as a failsafe so
an admin with rights can't accidentally introduce bad data to the
system, but there's just no substitute for proper encapsulation.
Plus, sometimes it's expensive or impossible to verify after the fact
(in a constraint trigger) whether the transaction was valid, but
just wrapping the stuff in a stored procedure is much simpler.