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.