This discussion is archived
1 Reply Latest reply: Aug 1, 2012 9:01 PM by andrewmy RSS

Control fields

Rinne Newbie
Currently Being Moderated
What are best practices regarding control fields in a transactional system? Fields such as modified_dt, created_dt, user_id, etc.

What is the best mechanism in populating these fields? Through triggers?

When, if at all, makes sense to store the modified_dt and created_dt as dates with timezone?
  • 1. Re: Control fields
    andrewmy Journeyer
    Currently Being Moderated
    There are two ways you can populate those columns - either in the application itself or via triggers. I would choose triggers for consistency since this way avoids the issue of developers forgetting to set values for each insert or update. There are some drawbacks with having the change information in the tables - if the record is updated there is no way to tell which column is updated or what the old values were and if deleted, there is no record of the deletion since the row would have disappeared from the table.

    The other way to record changes by user/date is to capture the information a separate table (e.g. table_name, action=insert/update/delete, column_name, user/date), again using triggers. Having a single table store all this information has some benefits like being able to quickly summarize all changes to critical tables in a weekly/monthly report without scanning hundreds of other tables individually for the user/date. You can also expand the design of this change table to add new_value and old_value columns to capture the old and new values of the column changed during updates.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points