This question came up at work today, would like to get some fellow developer's opinions:
Scenario 1: 2 tables to track checks in terms of HD space a separate table would have been smaller since the checks table grows at 1000+ per week, but there would only be a handful of returned checks. In terms of cpu overhead, 90% of ops would have gotten a checkid from the checks table and then a search on the actions table indexed on checkid. Since there would be few actions per checks it shouldn't have been too bad. CREATE TABLE returned_checks ( returned_checksid int(11) NOT NULL auto_increment, webm_entityid int(11) NOT NULL default '0', checkno int(11) NOT NULL default '0', processed datetime NOT NULL default '0000-00-00 00:00:00', mirr_isdeleted tinyint(3) unsigned NOT NULL default '0', timestamp timestamp(14) NOT NULL, KEY wid (webm_entityid), KEY rid (returned_checksid) ) CREATE TABLE newpayday_checks ( type varchar(16) NOT NULL default '', amount float(9,2) unsigned NOT NULL default '0.00', textamount varchar(128) NOT NULL default '', name varchar(64) NOT NULL default '', webm_entityid int(11) NOT NULL default '0', addr1 varchar(60) NOT NULL default '', addr2 varchar(60) NOT NULL default '', citystatezip varchar(70) NOT NULL default '', country varchar(40) NOT NULL default '', socialsec varchar(15) NOT NULL default '', checkno int(11) NOT NULL default '0', date date NOT NULL default '0000-00-00', sent datetime NOT NULL default '0000-00-00 00:00:00', mirr_isdeleted tinyint(1) unsigned NOT NULL default '0', timestamp timestamp(14) NOT NULL, paydayfile datetime NOT NULL default '0000-00-00 00:00:00', stopped datetime NOT NULL default '0000-00-00 00:00:00', originalcheckid int(11) NOT NULL default '0', checkid int(11) NOT NULL auto_increment, checkdate date NOT NULL default '0000-00-00', KEY name (name,date), KEY checkid (checkid), KEY checkno (checkno), KEY mirr_isdeleted (mirr_isdeleted), KEY socialsec (socialsec), KEY webm_entityid (webm_entityid) ) TYPE=MyISAM; Scenario 2: one table to track checks (with a returned date in it) Reasons behind this are: * less duplication of data and indexes therefore less disk space which in turn means less disk IO * less chance of becoming out-of-sync, more data integrity * less joins (since we'll likely be joining this table) means less cpu overhead * less management, one table to maintain * one less entry in the mysql table cache (only holds so many) CREATE TABLE newpayday_checks ( type varchar(16) NOT NULL default '', amount float(9,2) unsigned NOT NULL default '0.00', textamount varchar(128) NOT NULL default '', name varchar(64) NOT NULL default '', webm_entityid int(11) NOT NULL default '0', addr1 varchar(60) NOT NULL default '', addr2 varchar(60) NOT NULL default '', citystatezip varchar(70) NOT NULL default '', country varchar(40) NOT NULL default '', socialsec varchar(15) NOT NULL default '', checkno int(11) NOT NULL default '0', date date NOT NULL default '0000-00-00', sent datetime NOT NULL default '0000-00-00 00:00:00', mirr_isdeleted tinyint(1) unsigned NOT NULL default '0', timestamp timestamp(14) NOT NULL, paydayfile datetime NOT NULL default '0000-00-00 00:00:00', stopped datetime NOT NULL default '0000-00-00 00:00:00', originalcheckid int(11) NOT NULL default '0', checkid int(11) NOT NULL auto_increment, checkdate date NOT NULL default '0000-00-00', returned datetime NOT NULL default '0000-00-00 00:00:00', KEY name (name,date), KEY checkid (checkid), KEY checkno (checkno), KEY mirr_isdeleted (mirr_isdeleted), KEY socialsec (socialsec), KEY webm_entityid (webm_entityid) ) TYPE=MyISAM; Which Scenario in your opinion, is better and why? -Ryan.
