History-based (or logged) database.

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