Frequently, database developers get used to creating tables that have the same capabilities. That is how I started to think about what kind of columns I would include in a table template. What columsn might be useful for tables that could help an application track changes to data. There are some basic columns I generally like to include. For example, in tables where user data entry is required, frequently one may want to track when a record was added, and when a row was changed. So, for years I would always place 4 columns in such tables. I used CreatedDate and CreatedBy columns when a record was first created. Then I would use a ModifiedDate and ModifiedBy column to hold who last changed the record. This is a simple and robust design. However, over time I like to break up the history into a separate table. Still, this original design is easy to work with and effective.

Figure 1 standard table with audit columns

Such a standard audit table as above could rely on the calling program to populate the user and timestamp values. An alternative way would be to use triggers to populate those values.

But as I said earlier, I ended up favoring an alternative design. This design involves splitting the table into two tables, one for data and the other history. The benefit of splitting the table in two is because you can actually remove the necessity of storing the createdby and datecreated values. Those createdby and datecreated values can be kept in the modifiedBy and dtModified columns of the history table. So the first time a record is created in tabletemplate , a new record is added to history. When the record is updated, another record is added to the history table. To find out who created the record and the created date, it is simple to use sql to find the row with the lowest HistoryId for the particular idSequence you wish to examine. To get the most recent update to that idSequence, find the maximum HistoryId for that idSequence number.

Figure 2 inserts and updates to the tabletemplate will be reflected in the history table using triggers

To create the above tables follow the following sql:

CREATE TABLE `tabletemplate` (

`idSequence` int(11) NOT NULL AUTO_INCREMENT,

`data` varchar(45) DEFAULT NULL,

PRIMARY KEY (`idSequence`)

) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8$$

CREATE

TRIGGER `mydb`.`tabletemplate_AINS`

AFTER INSERT ON `mydb`.`tabletemplate`

FOR EACH ROW

insert into History (data,idSequenceTT,ModifiedBy) values (new.data,new.idSequence,USER())

$$

CREATE

TRIGGER `mydb`.`tabletemplate_AUPD`

AFTER UPDATE ON `mydb`.`tabletemplate`

FOR EACH ROW

insert into History (data,idSequenceTT,ModifiedBy) values (new.data,new.idSequence,USER())

$$

delimiter $$

CREATE TABLE `history` (

`HistoryId` int(11) NOT NULL AUTO_INCREMENT,

`idSequenceTT` int(11) NOT NULL,

`dtModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`ModifiedBy` varchar(45) NOT NULL,

`data` varchar(45) DEFAULT NULL,

PRIMARY KEY (`HistoryId`),

KEY `fk1_TT_HIST_idx` (`idSequenceTT`),

CONSTRAINT `fk1_TT_HIST` FOREIGN KEY (`idSequenceTT`) REFERENCES `tabletemplate` (`idSequence`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8$$