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