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?
1
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.
4
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.