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 j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
