There is no question in my mind that this
kind of auditing must be done at the database level. Otherwise, any time
you access the underlying database directly and update any tables monitored by
the audit log you break your audit trail.
I’ve heard arguments made that
access to the database should be restricted to the application front end only,
thus negating the possibility of someone directly updating the data and
circumventing the audit process, but in 25 years of IT experience I have *NEVER* *ONCE*
seen an environment where that was practical 100% of the time.
Ray Garrison, CIO
420 East Main Street
Bld 2, Suite 2
Branford, CT 06405
203.315.8637 Office
203.315.0429
Fax
www.periship.com
From: Colin Ross
[mailto:colinross@xxxxxxxxx]
Sent: Wednesday, May 23, 2007 4:11
PM
To: pgsql-php@xxxxxxxxxxxxxx
Subject: [PHP] Application Design:
Where to implement historical revisions of objects
Summary:
In a situation where the business model dictates that a certain entity or class
of entities should support the viewing of and differencing of historical
revisions of the data (read properties) contained in the entity should be
supported. Should the logic for the creation and viewing of this historical
information be implemented in the application itself
In Practice:
An application has a model class of "PageContent" which
represents the content that is shown on a certain page. Users of the system are
authenticated, then able to edit the content. From an application design point of view, where
should the logic and underlying system be for the management of historical
revisions.
My specifics include using PHP (5) and Postgre ( 7.4). I am using the Table/Row
gateways provided by the Zend Framework as a base for my model objects.
I don't have a large need to remain rdbms-neutral and am fine with a solution
the "locks me in" to using postgre as this will eventually be a
hosted application in a controlled environment.
Option 1 (Implemented in the
database/persistence layer):
This would follow the path of logic that stems from
seeing the database as not just a dumb container-- but as the manager and
more of a rich container if you will. When a user updates a certain row
(represented as an entity in the application), the database manages the
auditing of the historical data via triggers (ON UPDATE,ON DELETE) using an
audit table with a similar DDL of the base table, with the addition of
audit-specific fields (like revision id/version/etc). The 'current'
version of the data always remains in the base table, and the application, in
its basic functionality, remains unaware of such functionality, except with the
ability to query the audit table.
Option 2 (Implemented in the Application
Layer):
This would see the database as a dumb container, and the
model classes on the application side of 'the boundary' would implement the
logic of saving the revision is a separate table (or potentially in the same
table). All the logic for automagically creating a new revision record on
update/delete/etc. would be handled in the application layer.
Questions:
Which would you choose and why? Outside of (and even including)
portability complaints for the application? From a best practices /
theory point-of-view which is best? Why? Or is the truly a religious
debate with no real right or wrong?
Colin Ross
I have posted this on the zf-general (ZendFramework) and pgsql-php
(Postrge-php) mailing lists to see the differences in views from the two
crowds, abit separately to avoid confusing cross-posting between communities.
