Re: Audit Logs WAS: temporal support patch

2012/8/28 Jim Nasby <jim(at)nasby(dot)net>:
> On 8/22/12 3:03 AM, Pavel Stehule wrote:
>>>
>>> SELECT coverage_amt
>>> >FROM policy FOR SYSTEM_TIME AS OF '2010-12-01'
>>> >WHERE id = 1111;
>>> >
>>> >SELECT count(*)
>>> >FROM policy FOR SYSTEM_TIME FROM '2011-11-30' TO '9999-12-30'
>>> >WHERE vin = 'A1111';
>>
>> I like this design - it is simple without other objects
>
>
> The thing I don't like about this is it assumes that time is the best way to
> refer to when things changed in a system. Not only is that a bad assumption,
> it also means that relating things to history becomes messy.

On second hand I don't have a problem with some optional counter,
although I think so database system time is very useful and other
counters for versioning are not necessary - because in one time I can
have only one version - it doesn't do versions from rollbacked
transactions.

>
> The concept that we promote at work is that if you're going to "version"
> something (I don't like the term history because it implies you only want a
> logfile), you should have an explicit way to refer to any given version.
>
> So if you want to track the versioning of a specific field on a table:
>
> CREATE TABLE customer_status_versions (
> customer_status_version_id SERIAL
> , customer_id
> , previous_customer_status_version_id REFERENCES
> customer_status_versions
> , changed_at timestamptz
> , new_customer_status
> );
>
> That kind of structure makes it impossible to be ambiguous about the
> ordering of changes to a single customer's status. It also means that you
> have a specific identifier you can use in places of the system that care
> about that. IE:
>
> CREATE TABLE loans(
> ...
> , customer_id
> , customer_status_when_issued REFERENCES customer_status_versions
> );
>
> Now, when you look at a loan there is *zero* question on not only what the
> customer's status was when the loan was issued. Not only that, you can
> absolutely reliably know all customer status changes that had taken place up
> to that point. And you can do this without any complex temporal logic or
> reliance on a system clock that might not be reliable.
> --
> Jim C. Nasby, Database Architect jim(at)nasby(dot)net
> 512.569.9461 (cell) http://jim.nasby.net