Should I store logging information in main database table?

Question

For example, say I have a table of products. Should I store logging information such as who it was created by, last edited by, last updated date, ... Or should I separate the logging information in say an auditing table if the logging information is not relevant to the actual application?

Thank you.

Answer 1

If you are serious about keeping auditing information it should go in a separate table. The last-updated stuff will just get overwritten, it's no substitute. If you add a timestamp to the key then you can keep the history in the same table, but at the expense of making queries more expensive and application logic more convoluted, so I'd advise against that.

Answer 2

I'll usually keep LastChangeUser and LastChangeDate columns info in each table, and sometimes include CreateUser and CreateDate as well. Which is usually good for most tables.

However, if you need to store more than that, for really important tables (usually money related), go to another table. In that table (OriginalTableName_History) I usually have a HistoryID that is an auto increment, a HistoryDate, and a HistoryType (I=insert, U=update, D=delete), and then all the columns from the original table. I'll usually have a single trigger on the main table that puts every change (insert/update/delete) into the history table.