Hello everybody,

I am designing a database schema, and I was wondering about adding a couple of primitive audit fields to each table.

These ones are:
- ADDED_BY
- DATE_ADDED
- MODIFIED_BY
- DATE_MODIFIED

I feel that these one can be useful in some cases for simple checkups, but I think that will overload (a bit) my model. Another fact is that with these column, we can't know who modified and when between the added and last modification.

Any advice, experience or recommendation about this approach?

Thanks
Bruno

It's a common approach - we do exactly that on my project for all tables. On a small number of tables, where auditing is more critical, we have triggers that write to a log table so that the full history of inserts, updates and deletes is kept. But for most tables, knowing who added the row and who last updated it is good enough 9 times out of 10.

The overhead of having these columns is minimal. They do distract from the "real" data on logical data models though, so my preference would be to leave them off ERDs.

I avoid the "Added" columns, as I have found that sort of data to be unreliable and of little value. I have scripts that will do more complete auditing, if that is really necessary.

In the meantime, here is a script for adding "Modified" and "Modifier" columns and triggers.