Re: 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.

Colin Ross wrote:
> 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

If you are only going to be using postgres, do it with a trigger so you
don't have to remember to do it in the application (it's quite easy to
forget something like this).

If you have to support other databases as well, then make sure they
support triggers.

If you have to use a database that doesn't support triggers, then that
makes the decision for you (eg supporting mysql 4.1).

If you have to do it in the app, it's still quite easy:

insert into history_table (...) select x,y,z from orig_table where id='X'

Done :)

--
Postgresql & php tutorials
http://www.designmagick.com/

Hello Colin,

Am 2007-05-23 13:10:42, schrieb Colin Ross:
> 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.

I have something similar, exactly, a Database of military data and data
sets are never modified but added.

So, I give each dataset a serialnumber and a revision 0. Then if the
dataset was modified I change the revision to a higher number and add
the whole dataset into a new row.

This thing is done using trigers.

Greetings
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack Apt. 917 ICQ #328449886
50, rue de Soultz MSN LinuxMichi
0033/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)

But in the one-table holds all system, the Model Object need to know about
it.
i.e. "Select top 1 from data where serialnumber = 3821 order by revision
desc"
And off hand, it would make retrieving a whole data set of say- all the
current data involve a subquery to find the most recent data revision for
each serialnumber.

I don't at all mean to discount the system, but I have yet to find a good
pattern for dealing with multiple variants of the same datarow/set.

Colin

On 5/25/07, Michelle Konzack <linux4michelle(at)freenet(dot)de> wrote:
>
> Hello Colin,
>
> Am 2007-05-23 13:10:42, schrieb Colin Ross:
> > 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.
>
> I have something similar, exactly, a Database of military data and data
> sets are never modified but added.
>
> So, I give each dataset a serialnumber and a revision 0. Then if the
> dataset was modified I change the revision to a higher number and add
> the whole dataset into a new row.
>
> This thing is done using trigers.
>
> Greetings
> Michelle Konzack
> Systemadministrator
> Tamay Dogan Network
> Debian GNU/Linux Consultant
>
>
> --
> Linux-User #280138 with the Linux Counter, http://counter.li.org/
> ##################### Debian GNU/Linux Consultant #####################
> Michelle Konzack Apt. 917 ICQ #328449886
> 50, rue de Soultz MSN LinuxMichi
> 0033/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)
>
>

Am 2007-05-25 11:07:24, schrieb Colin Ross:
> But in the one-table holds all system, the Model Object need to know about
> it.
> i.e. "Select top 1 from data where serialnumber = 3821 order by revision
> desc"

You do not need to know what the last revision was,
since you can automagicaly SELECT the higest one...

Thanks, Greetings and nice Day
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack Apt. 917 ICQ #328449886
50, rue de Soultz MSN LinuxMichi
0033/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)

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

<http://www.periship.com/>

420 East Main Street

Bld 2, Suite 2

Branford, CT 06405

203.315.8637 Office

203.315.0429 Fax

www.periship.com <http://www.periship.com/>

_____

From: Colin Ross [mailto:colinross(at)gmail(dot)com]
Sent: Wednesday, May 23, 2007 4:11 PM
To: pgsql-php(at)postgresql(dot)org
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.