What are the common audit columns for each database table?

Question

I was going to include "status", "date_created", "date_updated" in every table in the database. "status" - for soft removal of lines.

Then I saw that some people also add user_created, user_updated columns for each table.

If I also add these columns, I will have at least 5 columns for each table. Will it be too much overhead?

Do you think it’s a good idea to have these five columns?

Also, "user" in "user_created" means database user? or application user?

Answer 1

In accordance with the above comments, it is recommended to add audits only to those tables that really require this.

Usually you want to audit the user of the application - in many cases, applications (for example, Web or SOA) can connect all users with the same credentials, so saving a database entry is pointless.

IMHO, the date created / last date updated / lastupdateby never give a complete picture, since you can only see who made the last change and did not see what was changed. If you are performing an audit, I would suggest that instead you audit the complete change using patterns such as trigger auditing. You can also avoid using triggers if your inserts / updates / deletes in your tables are encapsulated, for example. via Stored Procedures . True, audit tables will grow very large, but they, as a rule, will not be frequently requested (usually in a witch hunt), and can be archived, easily broken down by date (and can be made read-only). With a separate audit table, you will not need the DateCreated or LastDateUpdated , as this can be obtained. However, you still need the last user of the changes, because SQL will not be able to get the application user.

If you decide on logical deletions, I would avoid using a “status” as a field indicating a logical deletion, since you probably have tables that model the state of the process (for example, payment status, etc.). Using bit or char , such as ActiveYN or IsActive , are common for logical deletions.

Logical deletions can be cumbersome, since all your queries will need to filter Active=N records, and storing deleted records in your transaction tables can make these tables more than necessary, especially in Many : Many / junction tables. Efficiency can also be affected, since a two-state field is unlikely to be selective enough to be useful in indexes. In this case, physical deletions with a full audit may make sense.

Answer 2

I used all five before, of course. When I want to track who creates and (last) edit entries through a web application, and when this happens, I turn on the timestamps and the registered user (but not the database user, this is not how my system is configured; we use one account for all interaction with the database).

Similarly, a status can also be useful if users change the status of a record, well, status. If it comes from "Online" to "Offline" to "Archive", this entry may reflect this.

However, I do not use them for each table, nor should I. Sometimes I have tables designed only to store parts of the record ( normalized ), or it just doesn't matter how much the status or time created by whom is needed.

What you should consider for each table is the primary key field. Unless you are more sophisticated in your approach than you, you will almost always want this. Some things do not necessarily need one (a list of states, for example, may be a unique abbreviation). But this is more important for most of your tables than a series of timestamps and status fields.

Answer 3

The simple answer is just put it in your database what you need in your database.