Its called a "temporal database". Usually its intended for medical or police databases where you need a hind sight. i.e. if today is 31/12/2005, what did we know at 20/12/2005. for example, for a doctor appearing at court and required to testify what he knew at 20/12/2005. Very cool. It would be nice if postgreSQL could have a switch that could turn it into a temporal database. Regards, tzahi. > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx > [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Scott Frankel > Sent: Friday, March 04, 2005 1:51 AM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: preserving data after updates > > > > Is there a canonical form that db schema designers use > to save changes to the data in their databases? > > For example, given a table with rows of data, if I UPDATE > a field in a row, the previous value is lost. If I wanted to > track the changes to my data over time, it occurs to me that I could, > > 1) copy the whole row of data using the new value, thus > leaving the old row intact in the db for fishing expeditions, > posterity, &c. > -- awfully wasteful, especially with binary data > > 2) enter a new row that contains only new data fields, requiring > building a full set of data through heavy lifting and multiple > queries > through 'n' number of old rows > -- overly complex query design probably leading to errors > > 3) create a new table that tracks changes > -- the table is either wide enough to mirror all columns in > the working table, or uses generic columns and API tricks to > parse token pair strings, ... > > 4) other? > > Thanks > Scott > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
