Hi! I would like to implement a database which allows me to keep track of
changes from users, but I don't know if there is any model already used
for this. Let me show you what I mean.
Say I have a table t_table1 with 2 columns plus a PK. Normally my table
with some data would look like:
t_table1
------------------
PK | col1 | col2
------------------
1 | 3 | 4
2 | 4 | 7
3 | 6 | 9
... and so on...
If I make a change, I can't get the info about who made the change and
when did he do it, I can't do a "what was the value on a certain
date"-type query.
An UPDATE col1 = 9 WHERE pk = 1; would make t_table1 look like:
t_table1:
------------------
PK | col1 | col2
------------------
1 | 9 | 4
2 | 4 | 7
3 | 6 | 9
... and so on...
To solve my "who and when", and "what on a certain date" problem, I was
thinking on a platform like the following:
t_table1:
-------------------
PK | col1 | col2 | record_date | who_created
-------------------
1 | 3 | 4 | 2003-03-03 11:30:10 a.m. | alvarezp
2 | 4 | 7 | 2003-03-03 11:30:10 a.m. | alvarezp
3 | 6 | 9 | 2003-03-04 11:30:10 a.m. | alvarezp
... and so on...
Now, an UPDATE col1 = 9 WHERE pk = 1; (done on '2003-03-05 12:00:00 a.m.')
by 'ggarcia' would make t_table1 look like:
t_table1:
--------------------
UID | PK | col1 | col2 | record_date | who_created
--------------------
1 | 1 | 3 | 4 | 2003-03-03 11:30:10 a.m. | alvarezp
2 | 2 | 4 | 7 | 2003-03-03 11:30:10 a.m. | alvarezp
3 | 3 | 6 | 9 | 2003-03-04 11:30:10 a.m. | alvarezp
4 | 1 | 9 | 4 | 2003-03-05 12:00:00 a.m. | ggarcia
... and so on...
I would extend SQL to include a "WHEN" clause in SELECT statements. If
omitted, the query should use only the last valid records, using only UID
= {2, 3, 4}, which will make it completely transparent to not-yet-updated
applications.
Of course, may be a "deleted" column would be needed in order to DELETE
from t_table1;" and still have the data available for the hypothetical
"SELECT ... WHEN '2003-03-03 3:00:00 p. m.';"
Has anyone implemented something similar in PGSQL? If so, how have you
done it?
Thanks in advance.
--
Octavio Alvarez Piza.
E-mail: alvarezp@alvarezp.ods.org
