Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2008
    Location
    Québec City, Canada
    Posts
    10

    Simple audit fields

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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