Guidance required on copying records v normalisation (1 Viewer)

[t00ley]

Hi All,

Can anyone please advise on the best approach for copying records for archiving purposes.

Scenario:
I need to update records that meet criteria, but the user may later need to see the original record, what changes took place and the updated record.

My thoughts were to have:
tbl_MyData - Main 'live' data table
tbl_MyRules - Table of rules (with criteria & updates needed)
tbl_MyChangedData - Table to copy the impacted records to before making changes
tbl_MyChangesLog - table to capture basic details of the changes (tbl_MyData.ID, tbl_MyRules.ID, tbl_MyChangedData.ID)

The above will provide the current data in tbl_MyData, with a backup of the related record in it's 'previous' data state in tbl_MyChangedData (which could create a 1 to many relationship, if multiple rules apply to 1 record). What rules were applied can be viewed in tbl_MyChangesLog.

Now, whilst this doesn't create a true 'duplicate' record, many fields may not have changed. So with normalisation/record duplication in mind, what is the best practice approach?

I've thought of not having tbl_MyChangedData & changing the log to record by field changed (eg field name, change from, changed to), though this will require extra coding to recreate the 'original' data view by undoing the changes.

Also, it's worth mentioning that the users operate on a monthly cycle and when a month is completed the database is archived and the above tables (except tbl_MyRules) effectively have all records deleted.

Thanks in advance

Tooley

 

[Uncle Gizmo]

How about adding a simple boolean field "yes/no" - which indicates that the record is archived?

 

[The_Doc_Man]

Technically, if there is confusion over whether a record is current or not, the table design is deficient.

The applicable normalization rule says that the record has to be uniquely selected by its primary key (whether that key is simple or compound and natural or synthetic). If you can't know this from the contents of the record, then its key specification must be incomplete. For instance, it might be missing a date field to show when it was created or when it was obsoleted. Other issues could also lead to this same condition.

This is also a case for one of my "Old Programmer's Rules" - which is "Access won't tell you anything you didn't tell it first (or at least tell it how to tell you)." If you want to see a change history for something, you have to STORE a change history for something. How you do this is subject to another entire topic on its own (called "auditing").

If, for example, you had a set of records that resemble each other but have differences due to being edited on certain dates, then if you had a historical view, you would have a query that did a GROUP BY for all of the other fields in the key, but an ORDER BY for the key revision dates.