Is this the best approach to creating an audit trail?

Question

I am trying to create some functionality that keeps an audit trail of how the data in this user form has changed over time, with a dated audit at the foot of this page. For instance:

04/02/09 21:49 Title changed from "Tom" to "Chris".

I do this by storing data in this format in the session, and then storing a check for any differences in the stored data. If there is, I store the data, as it was until the last editing in a table called history, and storing the new values ​​in the current user table.

Is this the best perception approach?

Answer 1

One sentence; this would be relatively easy to do in a database trigger. In this case, you never have to worry about whether the code that starts the update remembers the recording history.

Answer 2

I’m not sure that there is one “best approach”, so many variables should be considered, including how far you go along the development path.

After going through code-based and db-trigger verification solutions, I made some comments below; Hope you can see where you are right now (in terms of development), these issues can be affected:

• If you need to map the user who changed the data (which you usually do), then db triggers will need to somehow get this information. Impossible, but more work and several ways to approach this (db user executing the query, common user column in each table, etc.).
• If you use db triggers and rely on the number of corrupted rows returned from requests, then your audit triggers must be disabled or your existing code modified to take them into account.
• IMHO db triggers offer greater security and offer an easier way to automate auditing, but they are not reliable, since anyone with the appropriate access can turn off triggers, change data, and then turn them back on. In other words, make sure your db protection authority is limited.
• Having one table for history is a good way, although you will have more work (and data to store) if you are checking history for several tables, especially when it comes to restoring the audit trail. You should also consider locking issues if there are many tables trying to write to the same audit table.
• Having an audit table for each table is another option. You just need each column of the audit table to be zero, and also save the date and time of the action (insert / update / delete) and the user associated with the action.
• If you go with the “single table” option, if you don’t have much time to spend on it, do not make too fancy an attempt to audit only when updating or deleting, although it may be tempting to avoid insertion (since most applications do this more often than updates or delete), restoring the audit history takes a lot of work.
• If your servers or data span multiple time zones, consider how to use the appropriate type of date and time in order to be able to store and restore the timeline, i.e. Store the date of the audit event in UTC, and also take into account the time zone offset.
• These audit tables can become huge, so have a strategy if they begin to affect performance. Parameters include partitioning into different disks, archiving, etc. Mostly think about it now, and not when it becomes a problem :)

Answer 3

I have always been a fan of using a single table instead of breaking it down into an “active” table and a “history” table. I put 4 columns on these tables, all the timestamps: created, deleted, started, completed. "created" and "deleted" are pretty straightforward. The timestamps "start" and "end" mean that the record was actually an "active" record. The current active record will have a "start" time until now() and NULL "end". By separating the “created” and the “starting” times, you can plan for changes that will take place in the future.

This design, unlike the two-story design, allows you to easily write queries that will automatically work with the necessary data. Suppose your table stores the tax rate over time ... you don’t want all your queries using tax rates in their calculations to have additional difficulty in deciding how to look in the history table when processing old invoices , for example ... you can simply find the tax rate that is valid at the time of the invoice in one request, regardless of whether the current tax rate or not.

This idea was originally not mine (although I myself reinvented the original idea before reading about it) ... you can find a detailed discussion of this in this online book .

Answer 4

Participating in sessions makes me a little wary (are you sure that you work correctly with it when two users work on the same data at the same time?), But in general, yes, maintaining a history table is the right thing.

Answer 5

I would also think of a database trigger to insert or update to record change information (who, when, what, value before, value after) in a separate audit table. Thus, you know that even if the data is changed outside your application using the database directly, it will still be raised.

You can also do something to determine if the data outside your application has changed, for example, calculate the hash or crc of the record and save it in a field somewhere, and then check it when reading the data.

Answer 6

I think your suggestion involves writing a lot of code / metadata to ensure matching objects / records so that you get an audit at the business level.

As an alternative, a database trigger may not give you a high enough level of idea of ​​what happened. This may be acceptable if you use audit so rarely that the effort to recreate business value is in order.

It also looks like a good application for AOP (Aspects), where you can use object model reflection to dump something meaningful without requiring a lot of metadata.