What are common Auditing columns for each database table?

Question

I was going to include 'status', 'date_created', 'date_updated' to every table in database. 'status' is for soft deletion of rows.

Then, I've seen few people also add 'user_created', 'user_updated' columns to each table.

If I add those columns too, then I will have at least 5 columns for every table. Will this be too much overhead?

Do you think it's a good idea to have those five columns?

Also, does the 'user' in 'user_created' mean database user? or application user?

Answer 1

As per comments above, would advise adding auditing only to those tables actually requiring it.

You generally want to audit the application user - in many instances, applications (such as Web or SOA) may be connecting all users with the same credential, so storing the DB login is pointless.

IMHO, the date created / last date updated / lastupdateby patterns never give the full picture, as you will only be able to see who made the last change and not see what was changed. If you are doing auditing, I would suggest that instead you do a full change audit using patterns such as an audit trigger. You can also avoid using triggers if your inserts / updates / deletes to your tables are encapsulated e.g. via Stored Procedures. True, the audit tables will grow very large, but they will generally not be queried much (generally just in witch-hunts), and can be archived, easily partitioned by date (and can be made readonly). With a separated audit table, you won't need a DateCreated or LastDateUpdated column, as this can be derived. You will generally still need the last change user however, as SQL will not be able to derive the application user.

If you do decide on logical deletes, I would avoid using 'status' as an field indicating logical deletes, as it is likely you have tables which do model a process state (e.g. Payment Status etc.) Using a bit or char field such as ActiveYN or IsActive are common for logical deletes.

Logical deletes can be cumbersome, as all your queries will need to filter out Active=N records, and by keeping deleted records in your transaction tables can make these tables larger than necessary, especially on Many : Many / junction tables. Performance can also be impacted, as a 2-state field is unlikely to be selective enough to be useful in indexes. In this case, physical deletes with the full audit might make better sense.

Answer 2

I've used all five before, sure. When I want to track who, through a web app, is creating and (last) editing records, and when that happens, I include timestamps and the logged-in user (but not the DB user, that's not how my system is setup; we use one account for all DB interaction).

Likewise, status can also be useful if users are changing a record's, well, status. If it goes from being "Online" to "Offline" to "Archive", that record can reflect that.

However, I don't use these for every table, nor should you. Sometimes I have tables that are meant only to store parts of a record (normalized), or just don't have a value as far as needing a status or time created by who.

What you should be considering for every table is a Primary Key field. Unless you are more sophisticated in your approach than you sound, you will almost always want one. Some things don't necessarily need one (a states list, for instance, could Unique the abbreviation). But this is more important to most of your tables than a series of timestamp and status fields.