Tracking record history
Tracking record history
Hi, I've had a request to create the ability to log changes so we can see a record of mods - for example a record was changed on 02/04/21 by Fred, again on 09/04/21 by Jill and so on. It would also be nice to be able to somehow show the actual changes - so when a field has been changed, the old vs new is available and the date this took place and who made the modification. Could anyone provide some initial thoughts on how I might go about this? (I don't need to be able to re-create data for a given date - that would be a different requirement.) Thanks. Any thoughts appreciated.
Re: Tracking record history
I found this at https://dev.to/zhiyueyi/design-a-table- ... abase-10fn. The database I need it for doesn't hold many records and gets fairly light use, but it would be good to have a solid solution for further use on a database with a larger number of records.
3. Use an Audit Table
A better solution is to create an audit table to record every single change in every field, which saves the spaces by eliminating redundant information. The table looks like this:
Audit
Column Name Data Type
Id int
Table varchar(50)
Field varchar(50)
RecordId int
OldValue varchar(255)
NewValue varchar(255)
AddBy int
AddDate date
For example, there is a transaction looks like this:
Id Description TransactionDate DeliveryDate Status
100 A short text 2019-09-15 2019-09-28 Shipping
And now, another user with id 20 modifies the description to A not long text and DeliveryDate to 2019-10-01
Id Description TransactionDate DeliveryDate Status
100 A not long text 2019-09-15 2019-10-01 Shipping
Hence, there are 3 fields changed, Description, ModBy and ModDate. Respectively, there should be 3 records added into the audit table as shown below.
Id Table Field RecordId OldValue NewValue AddBy AddDate
1 Transaction Description 100 A short text A not long text 20 2019-09-17
2 Transaction DeliveryDate 100 2019-09-28 2019-10-01 20 2019-09-17
Lastly, update the original record in Transaction table into
Id Description TransactionDate DeliveryDate Status
100 A not long text 2019-09-15 2019-10-01 Shipping
In that case, it's very easy to query for field changes. For example, if I want to find out all the description changes for the transaction with ID 100, I can just query by
Select * from Audit where RecordId = 100 and Field = "description".
[Also part of the text - not my comments] ... The downside of this approach is the possible huge increase of records. Since every change in different fields is one record in the Audit table, it may grow drastically fast such as tens of changes resulting in hundreds of audit records. In this case, table indexing plays a vital role for enhancing the querying performance.
3. Use an Audit Table
A better solution is to create an audit table to record every single change in every field, which saves the spaces by eliminating redundant information. The table looks like this:
Audit
Column Name Data Type
Id int
Table varchar(50)
Field varchar(50)
RecordId int
OldValue varchar(255)
NewValue varchar(255)
AddBy int
AddDate date
For example, there is a transaction looks like this:
Id Description TransactionDate DeliveryDate Status
100 A short text 2019-09-15 2019-09-28 Shipping
And now, another user with id 20 modifies the description to A not long text and DeliveryDate to 2019-10-01
Id Description TransactionDate DeliveryDate Status
100 A not long text 2019-09-15 2019-10-01 Shipping
Hence, there are 3 fields changed, Description, ModBy and ModDate. Respectively, there should be 3 records added into the audit table as shown below.
Id Table Field RecordId OldValue NewValue AddBy AddDate
1 Transaction Description 100 A short text A not long text 20 2019-09-17
2 Transaction DeliveryDate 100 2019-09-28 2019-10-01 20 2019-09-17
Lastly, update the original record in Transaction table into
Id Description TransactionDate DeliveryDate Status
100 A not long text 2019-09-15 2019-10-01 Shipping
In that case, it's very easy to query for field changes. For example, if I want to find out all the description changes for the transaction with ID 100, I can just query by
Select * from Audit where RecordId = 100 and Field = "description".
[Also part of the text - not my comments] ... The downside of this approach is the possible huge increase of records. Since every change in different fields is one record in the Audit table, it may grow drastically fast such as tens of changes resulting in hundreds of audit records. In this case, table indexing plays a vital role for enhancing the querying performance.
Re: Tracking record history
Hi,
if you search the forum, there is a long thread about audit-log. Which is a very nice feature. As you stated already, note that using an audit-log may create a huge datavolume.
if you search the forum, there is a long thread about audit-log. Which is a very nice feature. As you stated already, note that using an audit-log may create a huge datavolume.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Tracking record history
Thank you pböttcher. I had already searched the forum but hadn't seen the thread. So I've taken a quick look and can see 'audit-log' is the search term to use! So I will look closer. Thanks.
Re: Tracking record history
Hi,
for Audit-Log, you can see my footer.
Concerning size of the log:
records/rows: 265,865
data size: 41,484,288
index size: 21,544,960
data free: 4,194,304
No speed problems (except, if you create a child-link and show record changes in a child register).
For audit-log there is a plugin now as well which takes care of all installation! See my footer for autit-log
Olaf
for Audit-Log, you can see my footer.
Concerning size of the log:
records/rows: 265,865
data size: 41,484,288
index size: 21,544,960
data free: 4,194,304
No speed problems (except, if you create a child-link and show record changes in a child register).
For audit-log there is a plugin now as well which takes care of all installation! See my footer for autit-log
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view