Re: Implementing a change log

On 9/18/05, Michael Schuerig <michael(at)schuerig(dot)de> wrote:
>
> In my current project I have a customer requirement for implementing a
> change log. This is not just for auditing purposes, rather it is meant
> to be accessible by users so they can get an overview of the change
> history of an object. The entire data set is not big, I'm expecting
> considerably less than 50.000 records. Changes are only made by about
> 30 human users.
>

Will you need to tell who made what changes? If so, you'll probably
want each user to be a fully fledged Postgres users.

> Queries related to change history are (a) for all changes during a
> specific time interval and (b) for all changes to a particular record.
>
> My original intention was to keep two sets of tables. The first
> containing only the working set of current records. The second
> containing all prior versions. I haven't experimented with such a setup
> yet and I'm wondering if it is even necessary. The alternative being to
> keep only a single set of tables.
>

Keeping a single set of tables can get pretty complex. You'd need to
mask each table with a view and a set of rules for working with only
the newest version of each record. I always set up as separate set of
audit tables to record the old versions of each row.

> Can anyone relate their experiences with such a thing? Which approaches
> should I take into consideration?
>

I blogged about my most recent incarnation of "audit tables" here:
http://open-ils.org/blog/?p=28 . We don't use Postgres users (we have
2 million), but it would be trivial to modify what I've done there to
work with real PG users or any other particulars of your environment.

Hope that helps!

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org