4

For as long as I have been writing database-backed websites, I have included created_by, created_at, updated_by, and updated_at in every single table. As you can guess, whenever a record gets created or updated the appropriate fields are set. Currently I'm working on an auditing module for my website so that every insert/update gets added to an Audit table along with the field values from the record being changed. Habit has me still adding those create/update columns to each record but I'm wondering if they are even needed anymore.

Are created_by/created_at/updated_by/updated_at fields still necessary or helpful if those same actions are being tracked in a dedicated table?

  • MS SQL has a feature that will record who, when, and what fields were changed. – Mych Mar 4 '15 at 15:20
4

No, they're not. The purpose of auditing is to determine who did what in the past. The current data is irrelevant for this purpose, but you will need to record which data changed - there's no point in saying "user Dave change the person table" without saying what he changed. This information doesn't have to be recorded so formally though.

You might like to store the table that was modified, and a text field containing the data changes to assist in filtering the audit records during an investigation, or just the data changes if you can filter on other factors or substring searches of the text.

  • Exactly what I was looking for, thanks! The current plan is to store the user, the action taken, the datetime, the table, the table id, and all of the records' data before the change. The main goal is to facilitate "Dave updated this record but we need to change it back" or to look at a history of the given record. In the end, you told me exactly what I needed (and hoped) to hear. Moving the auditing data out of the data tables and into a dedicated auditing table will be a relief! – justinvoelker Mar 4 '15 at 15:00
  • Personally I would just store the changed data - it can be difficult to see 1 field change in a sea of fields. Also, a separate audit table can be truncated periodically to store only a year's data (or whatever) thus keeping your DB trim and shapely! – gbjbaanb Mar 4 '15 at 15:04
  • I was thinking about a page that allows one to view a given audit record and highlight differences between it and the next version (be in another audit record or the current real record). I'll still take a look at just storing the changed fields as it will keep the record size down. Also need to decide if storing old value is enough or if I want old and new values. – justinvoelker Mar 4 '15 at 15:33
  • here we record audits in the form "user A changed X from Y to Z, [repeat]" . But that's just us as our users like human-readable audits. YMMV. – gbjbaanb Mar 4 '15 at 16:14
0

It still depends on whether you need to do joins with the tables. For example you might still need to show the username of the person who created the entry. Or maybe you need to check if the person is allowed to edit the entry by checking the created_by. I would not worry about duplicate data in the audit log since an audit log should NOT live with your main application for many reasons including security, modularity and scalability.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.