Re: preserving data after updates

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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Scott Frankel
> Sent: Friday, March 04, 2005 1:51 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] 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
>
>