Re: preserving data after updates - Mailing list pgsql-general

Door number 3.

Thanks for the responses and terrific suggestions!
Scott



On Mar 4, 2005, at 8:28 AM, Greg Patnude wrote:

> I use a modified form of option 3 with an ON UPDATE RULE.... the
> update rule
> copies the row to an inherited table...
>
> CREATE TABLE dm_user (
>
>        id SERIAL NOT NULL PRIMARY KEY,
>
>        lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id),
>        dm_user_address INTEGER NOT NULL DEFAULT 0,
>        dm_user_email INTEGER NOT NULL DEFAULT 0,
>
>        f_name VARCHAR(50) NOT NULL,
>        m_name VARCHAR(50) NOT NULL,
>        l_name VARCHAR(50) NOT NULL,
>
>        uname VARCHAR(20) NOT NULL,
>        upwd VARCHAR(20) NOT NULL,
>        pwd_change_reqd BOOLEAN DEFAULT FALSE,
>        login_allowed BOOLEAN DEFAULT TRUE,
>        lost_passwd BOOLEAN DEFAULT FALSE,
>
>        create_dt TIMESTAMP NOT NULL DEFAULT NOW(),
>        change_dt TIMESTAMP NOT NULL DEFAULT NOW(),
>        change_id INTEGER NOT NULL DEFAULT 0,
>        active_flag BOOLEAN NOT NULL DEFAULT TRUE
>
> ) WITH OIDS;
>
>
> CREATE TABLE dm_user_history (
>
>        history_id SERIAL NOT NULL PRIMARY KEY,
>        hist_create_dt TIMESTAMP NOT NULL DEFAULT NOW()
>
> ) INHERITS (dm_user);
>
> CREATE RULE dm_user_upd_history AS ON UPDATE TO dm_user DO INSERT INTO
> dm_user_history SELECT * FROM dm_user WHERE id = old.id;
>
> CREATE RULE dm_user_nodelete AS ON DELETE TO dm_user DO INSTEAD UPDATE
> dm_user SET active_flag = FALSE WHERE id = old.id;
>
>
>
> "Scott Frankel" <leknarf@pacbell.net> wrote in message
> news:bd02bff5561d8b271301ba10bafca105@pacbell.net...
>>
>> 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 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>