2012/8/28 Jim Nasby <j...@nasby.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 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
