history - Tracking MySQL Database Changes -

i'm running mysql 5.1.36 , have database used web-based desk system. database has 3 tables i'd track changes for:

create table if not exists `tickets` (   `ticketnum` int(11) unsigned not null,   `submittedfromip` tinyblob,   `submittedfromdevice` varchar(255) default null,   `entrydate` datetime default null,   `closeddate` datetime default null,   `lastname` varchar(50) default null,   `firstname` varchar(50) default null,   `email` varchar(50) default null,   `location` varchar(4) default null,   `inventorynumber` varchar(50) default null,   `devicename` varchar(50) default null,   `description` text,   `notes` text,   `agent_id` smallint(5) unsigned not null default '1',   `totalhoursspent` float not null default '0',   `status` smallint(5) unsigned not null default '1',   `priority` tinyint(4) not null default '0',   `lastupdatedbyagent_id` smallint(5) unsigned default null,   primary key (`ticketnum`),   key `closeddate` (`closeddate`) ) engine=innodb default charset=utf8;  create table if not exists `ticketsolutions` (   `entry_id` int(10) unsigned not null,   `ticketnum` mediumint(8) unsigned default null,   `entrydatetime` datetime default null,   `hoursspent` float default null,   `agent_id` smallint(5) unsigned default null,   `entrytext` text,   `lastupdatedbyagent_id` smallint(5) unsigned default null,   primary key (`entry_id`),   key `ticketnum` (`ticketnum`),   key `entrydatetime` (`entrydatetime`),   key `hoursspent` (`hoursspent`),   key `rating` (`rating`) ) engine=innodb default charset=utf8;  create table if not exists `tickettagsmap` (   `ticketnum` int(11) unsigned not null,   `tag_id` int(10) unsigned not null,   `addedbyagent_id` smallint(5) unsigned not null,   `datetimeadded` datetime not null,   primary key (`ticketnum`,`tag_id`),   key `tag_id` (`tag_id`),   key `fk_agentid` (`addedbyagent_id`) ) engine=innodb default charset=utf8; 

based on i've read, best way handle create duplicate tables, 2 fields per table:

modifieddatetime action 

is best way? every time slightest change made record, entire record inserted corresponding history table. seems huge waste of space. there better way this?

it may or may not waste of space depends on typical operations tables. insert , delete way track store column values. update can save space. can create 2 tables, instance,

update_history_main(id int not null auto_increment primary key, modify_date datetime not null, table_involved varchar(50) not null);  update_history_details (id int not null auto_increment primary key, update_history_main_id int not null, field_name varchar(100), old_value varchar(100), new_value varchar(100), foreign key (update_history_main_id) references update_history_main(id) on update cascade on delete cascade); 

and add records these tables after each update. problem here old_value , new_value columns should large enough keep value of column original tables. need create update_history_details_text_blobs tracks changes in text/blob columns.

update. thus, body of after update trigger tickets table may like

delimiter $$$  create trigger afterticketupdate after update on tickets  each row  begin      declare main_id int;      insert update_history_main(modify_date, table_involved)        values(now(),'tickets';      select last_insert_id() main_id;       if (new.submittedfromdevice != old.submittedfromdevice)       insert update_history_details(update_history_main_id, field_name,        old_value,new_value)       values (main_id, 'submittedfromdevice',old.submittedfromdevice,         new.submittedfromdevice);      end if; // ... check other fields.   end $$$