Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012

    Question Unanswered: Table Design with Audit fields


    I am designing a schema where in all the tables will have created_by, created_on, modified_by and modified_on columns for audit purpose and status (0 / 1) for soft delete. My tables will have application data columns on a average of 20-25 max.

    Will these additional columns impact the DML operations on the tables?

    These columns are only for audit purpose and rarely used in queries. So I am thinking to separate it. So that, all my tables will have only active data columns.

    Can I have these columns in a separate table on the same schema with one-to-one relationship?


    On a separate table on the different schema, lets say audit, with one-to-one relationship.

    To update these columns, using of triggers is advised?

    Please advice.


  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I've never needed to audit a table in MySQL, but my general practice is pretty simple.

    My production tables have no auditing information in them at all. This is espeically important when working with third party products, since altering their schema is fraught with peril.

    my auditing tables include columns for what type of change (inset, update, or delete), who made the change, and when the change was made. Depending on your auditing needs, you may choose to audit different users or processes differently from others. An example of different auditing levels might be to ignore ETL altogether, keep application changes for ten days, and keep dba/user changes forever.

    I do use three triggers on the production table, one each for INSERT, UPDATE, and DELETE. They record the audit specifice informaiton and the value of every important column within the modified rows after the operation is complete.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2012
    Thanks PatP.

    I am trying to record what & when changes made & who made changes to the records of application DB tables. Planning to use insert, update & delete triggers to do this.

    Having these additional columns in the a table will impact the DML operations performance?
    Or, separating these columns to different table will improve DML operations.


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts