how to keep a track of last modified time

Question

I have an PARTSORDER table. I want to keep the track of last modification time for easy record for easy access later (query orders which was modified two days ago). Should I have a column with datetime type to put time in it or a better way you usually use? thanks,

Answer 1

I almost always have the four following audit fields:

CREATED_BY
CREATED_DATE
MODIFIED_BY
MODIFIED_DATE

These allow for recording the original user/date as well as the last modified user/date.

Also,

If you need a history of changes (more than just the most recent) then you can always use a journal table of sorts.

Answer 2

In MySQL, you can create a TIMESTAMP column on a table that will automatically set to the current date and time whenever modified (on INSERT and UPDATE).

Here is an example:

CREATE TABLE `PARTSORDER` (
 `modified` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM;

Answer 3

If you are using MS SQL Server, you'll want to use a DATETIME column. TIMESTAMP may look good at first, but you can't query it or use it in an index. It's more for tracking the order in which things happened in the databse or comparing your original record against the database for changes when updating (which you should be doing if you are not already in a multi-user environment). Details: Timestamps vs Datetime data types

I have even worked on systems that have a log table to track changes because there are often more than one modification to the record. In addition, some have another table that tracks the indiviual changes made in a key/value type listing driven by an update trigger. This data ties back to the log and ultimately to the record. You can then easily build up a history of all changes made to the record.