Creating auditing tables MySQL Oracle etc.

Creating auditing tables MySQL Oracle etc.  

When you create an audit table you have two options.

1. Create a separate table called TABLENAME_log
2. Create audit fields 

Most designers go with step 1 because they never understood how audit fields work.

Let me explain.

Start by building a simple table with these additional required fields example

column name
updated_by = "johndoe"
updated_date = "2013-06-10"
inserted_by="johndoe"
inserted_date="2013-06-10"
effective_end_date="NULL"

When we select from the table we always use the method "select * from tablename where effective_end_date is null"
When we update the table we always use "Update tablename set updated_by='mikedoe', updated_date=now() "
When we insert populate both update and insert columns.
If we delete a row we just update the row with the effective_end_date = now() and insert the new row

Thus allows us to have audit fields on our tables.