For our project, we need to have a history table for almost every table
in the data model. the history table mirrors the original and has a
couple of extra columns like date, transaction type and username. There
are two ways of populating the history tables:
1) Populate a history table right after any Insert, Update or Delete
with the just entered data.
2) Populate a history table just before any update or delete with the
data that will be overwritten.

Is there any reason other than personal preference to choose one method
over the other? Could someone point me to someplace that treats this
issue and others relating to history tables in a formal manner?

Thanks.

ask80 wrote:
Either way is fine in my opinion. This sort of task is accomplished
using trigger functions. You can read all about them in the manual of
your favorite RDBMS that supports it.

ask80 wrote:
This history table can also be populated before the change with the new
data.
(ie, insert into history, Then change the base table. where 1) does the
reverse
insert into base table, insert into history.)
Depending on your reliability constraints, inserting into the History
table first is slightly more robust. (depends on where the commit
happens)

These auditing tables can sometimes be done by the DBMS. The biggest
issue is scalability, which is affected by the details of the
implementation more than this level of discussion.

Good luck.
Ed

ask80 wrote:
Snodgrass: "Developing time-oriented databases" gives a thorough account of
all aspects. A recommended read to all.

--
Regards,
Kristian Damm Jensen
"This isn't Jeopardy. Answer below the question."