Our main table, tblLink, will have its primary key as foreign in a few other tables too and one of the tables will be tblMainData which over time will be millions of rows.
We would like to maintain only 3 months of data based on the insert date in tblLink.
The rest we would like to purge and keep to generate history reports. What is the best mechanism to go about in this scenario? Below is how our table structures looks like.
CREATE TABLE IF NOT EXISTS tblLink ( linkID int(5) NOT NULL, compID int(5) NOT NULL, vehicleID int(5) NOT NULL, deviceID int(5) NOT NULL, locationFromID int(5) NOT NULL, locationToID int(5) NOT NULL, employeeIDInsert int(5) NOT NULL, dateTimeInsert datetime NOT NULL, dateTimeStartJourney datetime NOT NULL, dateTimeEnd datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’, dateTimeEndJourney datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’, linkStatus enum(‘a’,‘d’,‘e’,‘m’) NOT NULL, PRIMARY KEY (linkID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;CREATE TABLE IF NOT EXISTS tblEmailLog ( emailLogID int(11) NOT NULL AUTO_INCREMENT, compID smallint(6) NOT NULL, linkID int(11) NOT NULL DEFAULT ‘0’, userID smallint(6) NOT NULL, alertCodeID tinyint(4) NOT NULL, eventAlertID int(11) NOT NULL, userEmail varchar(100) NOT NULL, alertDateTime datetime NOT NULL, alertInsertDateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (emailLogID)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;CREATE TABLE IF NOT EXISTS tblEventAlert ( eventAlertID int(11) NOT NULL AUTO_INCREMENT, compID int(5) NOT NULL, mainDataID int(5) NOT NULL, vehicleID int(5) NOT NULL, eventAlertSentEmail varchar(50) DEFAULT NULL, eventAlertMessage varchar(255) NOT NULL, PRIMARY KEY (eventAlertID)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;CREATE TABLE IF NOT EXISTS tblMainData ( mainDataID int(11) NOT NULL AUTO_INCREMENT, linkID int(5) NOT NULL, header varchar(3) NOT NULL, deviceSerialNumber varchar(20) NOT NULL, latitude double NOT NULL, longitude double NOT NULL, speed float NOT NULL, course int(3) NOT NULL, dateTimer datetime NOT NULL, gpsDateTime datetime NOT NULL, insertDateTime datetime NOT NULL, odoMeter float NOT NULL DEFAULT ‘0’, driverID int(5) NOT NULL, eventAlertID int(11) NOT NULL DEFAULT ‘0’, mainDataInsertDateTime datetime NOT NULL, gpsString varchar(450) NOT NULL, PRIMARY KEY (mainDataID), KEY dateTime (dateTimer), KEY linkID (linkID), KEY eventAlertID (eventAlertID)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;CREATE TABLE IF NOT EXISTS tblSubData ( subDataID int(11) NOT NULL AUTO_INCREMENT, mainDataID int(11) NOT NULL, linkID int(11) NOT NULL, eventAlertID int(11) NOT NULL, deviceSerialNumber varchar(20) NOT NULL, subdeviceSerialNumber varchar(20) NOT NULL, dateTimer datetime NOT NULL, eventType varchar(2) NOT NULL PRIMARY KEY (subDataID), KEY mainDataID (mainDataID), KEY linkID (linkID), KEY eventAlertID (eventAlertID)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;