Re: Audit Logs WAS: temporal support patch

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.

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