Dear All,
I have a table tblEventAlert as below. So what happens when the data gets insert into this table the problem is that the eventAlertID is autoincrement. Then below it I have another table. My problem is that I want the eventAlertID to be stored into the tblSCvData. I know I can have a master table and read the last value and update that table whenever I need to insert data into tblEventAlert but I am scared because my rate of insert will be high and that might cause a delay or many deadlocks too. Any other solution? Another solution is to compare eventAlertDateTime from tblEventAlert and dateTimer from tblScvData is that ok to compare based on datetime?
CREATE TABLE IF NOT EXISTS `tblEventAlert` (
`eventAlertID` int(11) NOT NULL auto_increment,
`enterpriseID` int(5) NOT NULL,
`associateID` int(5) NOT NULL,
`vehicleID` int(5) NOT NULL,
`trailerID` int(5) NOT NULL,
`masterID` int(5) NOT NULL,
`slaveID` int(5) NOT NULL default '0',
`eventAlertType` varchar(5) NOT NULL,
`eventAlertCategory` enum('D','E','S') NOT NULL,
`eventAlertDateTime` datetime NOT NULL,
`eventAlertInsertDateTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`eventAlertLatitude` double NOT NULL,
`eventAlertLongitude` double NOT NULL,
`geoFenceID` int(5) NOT NULL,
`eventAlertTypeSend` enum('Email','Sms') NOT NULL,
`eventAlertSentEmail` varchar(50) default NULL,
`eventAlertSentPhone` varchar(11) default NULL,
`eventAlertMessage` varchar(255) NOT NULL,
PRIMARY KEY (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=85 ;
CREATE TABLE IF NOT EXISTS `tblScvData` (
`scvDataID` int(11) NOT NULL auto_increment,
`header` varchar(3) NOT NULL,
`deviceType` varchar(3) NOT NULL,
`eventID` varchar(2) NOT NULL,
`masterSerialNumber` varchar(20) NOT NULL,
`simStatus` smallint(6) NOT NULL,
`csq` smallint(6) NOT NULL,
`mcc` smallint(6) NOT NULL,
`mccc` smallint(6) 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,
`gpsStat` tinyint(4) NOT NULL,
`unitStat` varchar(12) NOT NULL,
`serialKey` varchar(8) NOT NULL,
`digitalInOut` varchar(4) NOT NULL,
`fuelLevel` varchar(3) NOT NULL,
`battVolt` float NOT NULL,
`deviceVolt` float NOT NULL,
`odoMeter` float NOT NULL default '0',
`chkSum` varchar(8) NOT NULL,
`driverID` int(5) NOT NULL,
`groupID` varchar(8) NOT NULL,
`associateID` int(5) NOT NULL default '0',
`geoFenceID` int(5) NOT NULL default '0',
`eventAlertID` int(11) NOT NULL,
`scvDataInsertDateTime` datetime NOT NULL,
`gpsString` varchar(450) NOT NULL,
PRIMARY KEY (`scvDataID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=26224 ;