Our schema looks like this:
- table
tblTrip
: stores start and end trip date and time - table
tblTripData
: keeps all the trip data - table
tblTripEvent
: stores events related to the particular trip.
Then we have two more tables, tblTripEmail
and tblTripSms
, which store the email and SMS that have been sent according to the events.
We want to keep just 3 months old trip data, we would like, to from time to time, to delete and archive the rest to be stored in offline database.
What is the best mechanism to store in an offline database? I am not sure how partitioning would be helpful because the tables are linked to each other here.
Below is the table desc for tblTripData
CREATE TABLE IF NOT EXISTS `tblTripData` (
`tripDataID` int(11) NOT NULL AUTO_INCREMENT,
`tripID` int(5) NOT NULL,
`deviceSerialNumber` varchar(20) NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`speed` float NOT NULL,
`course` int(3) NOT NULL,
`gpsdateTimer` 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',
`tripDataInsertDateTime` datetime NOT NULL
PRIMARY KEY (`tripDataID`),
KEY `dateTime` (`gpsdateTimer`),
KEY `tripID` (`tripID`),
KEY `eventAlertID` (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;