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@xxxxxxxxxxx> wrote in message
news:bd02bff5561d8b271301ba10bafca105@xxxxxxxxxxxxxx
>
> 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@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
