X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D56059DC9D5 for ; Wed, 18 Jan 2006 05:59:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 06959-04 for ; Wed, 18 Jan 2006 05:59:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [151.155.5.143]) by postgresql.org (Postfix) with ESMTP id EE68E9DC848 for ; Wed, 18 Jan 2006 05:59:35 -0400 (AST) Received: from [172.16.1.181] grzm [61.197.227.146] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 1.6 $ on Linux; Wed, 18 Jan 2006 02:59:30 -0700 In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v746.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Cc: Ken Winter , "'Richard Huxton'" , "'PostgreSQL pg-sql list'" Content-Transfer-Encoding: 7bit From: Michael Glaesemann Subject: Re: Still struggling with history tables Date: Wed, 18 Jan 2006 18:59:28 +0900 To: Achilleus Mantzios X-Mailer: Apple Mail (2.746.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.358 required=5 tests=[AWL=0.358] X-Spam-Score: 0.358 X-Spam-Level: X-Archive-Number: 200601/115 X-Sequence-Number: 23904 On Jan 18, 2006, at 19:23 , Achilleus Mantzios wrote: > Generally it is very hard to distinguish between two kind of UPDATES: > > a) UPDATEs that mean real data updates and they should be recorded > to the history system. > b) UPDATEs that are just false data entry, and they should mean > just plain correction UPDATES, with no recording. > > This distinguishability is very important, otherwise someone would > define a way to store historic data of changes to the historic data > themselves, and so on. Just a quick note: Sometimes the term "valid-time" is used to talk about the interval of data validity, and tables that include valid-time intervals are sometimes referred to as "state tables". If you're interested in tracking when corrections are made, this is referred to as "transaction-time". Correcting the is sometimes called a "nonsequenced" update, because it's not correcting the sequence of validity: it's just a correction. For more information, you can check out "Developing Time-Oriented Database Applications in SQL" by Richard Snodgrass (available as a free PDF download from his website[1]), or, for more theoretical information, "Temporal Data and the Relational Model" by CJ Date, Hugh Darwen, and Nikos Lorentzos.[2] Michael Glaesemann grzm myrealbox com [1](http://www.cs.arizona.edu/people/rts/tdbbook.pdf) [2](http://www.amazon.com/gp/product/1558608559/)