Re: table versioning approach (not auditing)

On 29/09/14 15:00, Abelard Hoffman wrote:
> Hi. I need to maintain a record of all changes to certain tables so
> assist in viewing history and reverting changes when necessary
> (customer service makes an incorrect edit, etc.).
>
> I have studied these two audit trigger examples:
> https://wiki.postgresql.org/wiki/Audit_trigger
> https://wiki.postgresql.org/wiki/Audit_trigger_91plus
>
> I've also read about two other approaches to versioning:
> 1. maintain all versions in one table, with a flag to indicate which
> is the current version
> 2. have a separate versions table for each real table, and insert into
> the associated version table whenever an update or insert is done.
>
> My current implementation is based on the wiki trigger examples, using
> a single table, and a json column to record the row changes (rather
> than hstore). What I like about that, in particular, is I can have a
> "global," chronological view of all versioned changes very easily.
>
> But there are two types of queries I need to run.
> 1. Find all changes made by a specific user
> 2. Find all changes related to a specific record
>
> #1 is simple to do. The versioning table has a user_id column of who
> made the change, so I can query on that.
>
> #2 is more difficult. I may want to fetch all changes to a group of
> tables that are all related by foreign keys (e.g., find all changes to
> "user" record 849, along with any changes to their "articles,"
> "photos," etc.). All of the data is in the json column, of course, but
> it seems like a pain to try and build a query on the json column that
> can fetch all those relationships (and if I mess it up, I probably
> won't generate any errors, since the json is so free-form).
>
> So my question is, do you think using the json approach is wrong for
> this case? Does it seem better to have separate versioning tables
> associated with each real table? Or another approach?
>
> Thanks
>
>
I implemented a 2 table approach over 15 years ago for an insurance
application. I used both an /effective_date/ & and an /as_at_date/, no
triggers were involved. I think a 2 table approach gives you more
flexibility.

The /effective_date/ allowed changes to be made to the table in advance
of when they were to become effective.

The /as_at_date/ allowed quotes to be made, valid for a period starting
at the as_at_date.

End users did not query the database directly, all queries were precoded
in a 4GL called Progress backed by an Oracle database. The same could
be done with a WildFly Java Enterprise AppSever (or some other
middleware) and a PostgreSQL backend.

Different use case, but the concept is probably adaptable to your situation.

You may want a change table, that has a change_number that is in each
type of table affected by a change. This would help for query type #2.

I would be quite happy to contract to work out the appropriate schema
and develop some SQL scripts to query & update the database, if you were
interested. My approach would be to create a minimal database with
sample data to validate the schema design and SQL scripts.

Using a flag to indicate current record, seems inflexible. As some
changes may not take affect until some time in the future, and you can't
query the database to see what was the situation at a particular point
in the past. For example: somebody complains about something that
happened last Saturday near noon, how would you query the database to
what it was like then?

Cheers,
Gavin