Audit trail seperate table???

  • Thread starter Thread starter 1
  • Start date Start date
1

1

I got Richard Rensel's modification for MS KB audit trail. Great! But:

Trailing is stored in each table's update field. Updating memo fields and
copy-pasting changes in the update field (which is a memo field) quickly the
update field runs out of space. So I thought of a second approach that would
make everyone's life easier.

1) Wouldn't it be better to have a table solely for storing data changes in
all tables?? Something like:

Table name:
AuditTrails

Fields:
TrailID PK
DateTime Now() (date/time of operation)
VerNum Integer (Version Number)*1
VerDate date (Version Date)*1
Action New,Update,Delete.. (Action performed)
Table (table to which change
is made, calling form's recordsource)
Field (field to which change
is made, calling control's recordsource)
Record (RecordID (PK) to which
change is made)
Value (Historic value, aka
value before update)*2
User (User that made the
changes)

*1 Each table contains two fields: VerNum and VerDate hold an incremental
number in the first, and the date the last change was made in the second,
thus when a change is made, the VerNum & Verdate from the record are copied
to the audittrail table's corresponding fields.

*2 Guessing that "changed to" is not needed because the updated record
reflects the updated entry.

2) Also, having a button "show Changes History" would query the audittrails
table with a filter set to the current form's recordsource.

3) Wanting to use the code for versioning control, the ability to have a
"rollback to previous version" function would be GREAT!! For that, the
action would be recorded as RollBack.
 
While there's certainly merit in your suggestion, it may not work in all
cases. You've only got a single field in which to store the PK for the
record that was changed. That'll work fine if you're using an Autonumber as
the PK for each table, but not all people do that. For them, the PK may be
multiple fields (up to 10), of multiple data types.
 
Doug,
You are more than right, I've missed that one out!
I am in awful pain here!
I am trying to design a versioning system for my db data, but I come to an
abrupt end. why?

I want to have "two depth" versioning. That is:
1) For a "simple" table, such as "documents", Richard Rensel's mod is
fine, for now at least.
But 2) For two tables with a one-many or many-many relationship, such as
employees-responsibilities and
products-recipe-ingredients, I would like when a record is changed from the
many side, the one side's version number to be incremented.

So far I have inserted an intermediate table between the relationship.
Employees-Version-responsibilities, products-version-recipeIngr-ingredients.
The relationship schema is (for the second case) as follows:
- a product has many versions of recipeIngredients,
- each version has many recipeIngredients,
- Each ingredient is in many recipeIngredient.

The problem with that, other that adding one table for every one-many,
many-many relationship I want to have versioning for, is that when adding a
new version of a product , the record holding the current version in the
versioning table is duplicated (with also the many side records), and
version information is updated to the first to maintain ref integrity to the
many side.
That though creates duplicate similar records if for example in the
recipeIngr table, the ingrients stay the same but changes the quantity of
one ingredient. So the "many side" is duplicated and the only change is a
different quantity in one of the ingredients.
That results in db "spamming" making a normalized db schema hold
de-normalized information. Is that right?

Any suggestions???

Thanx in advance.
 
Back
Top