On 9/18/05, Michael Schuerig <michael@xxxxxxxxxxx> 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@xxxxxxxxx GPLS -- PINES Development Database Developer http://open-ils.org ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
