0

Dear All,
I got few tables as below. So then what I do on any time I will calculate the total points collected based on each table via some of this queries like below. What I am using now is the sum function. I am confuse should I like run a monthly service which will total and store in separate table the total for each month rather than doing it live calculation? Which one is more effective?

Query 1
Select sum(tblDriverF2FPrograms.f2FProgramsPoints) As sumF2FPrograms FROM tblDriverF2FPrograms Where tblDriverF2FPrograms.driverID=".$driverID. " And tblDriverF2FPrograms.clientID=".$clientID." And Month(tblDriverF2FPrograms.dateTimeInsert)=".$monthValue. " And  Year(tblDriverF2FPrograms.dateTimeInsert)=".$yearValue

Query 2
Select sum(tblDriverMerits.meritsPoints) As sumMerits FROM tblDriverMerits Where tblDriverMerits.driverID=".$driverID. " And tblDriverMerits.clientID=".$clientID." And Month(tblDriverMerits.dateTimeInsert)=".$monthValue. " And  Year(tblDriverMerits.dateTimeInsert)=".$yearValue

Table 1
CREATE TABLE IF NOT EXISTS `tblDriverF2FPrograms` (
  `driverF2FProgramsID` int(5) NOT NULL auto_increment,
  `clientF2FProgramsID` int(5) NOT NULL,
  `driverID` int(5) NOT NULL,
  `clientID` int(5) NOT NULL,
  `employeeIDInsert` int(5) NOT NULL,
  `dateTimeInsert` datetime NOT NULL,
  `employeeIDVerify` int(5) NOT NULL,
  `dateTimeVerify` datetime NOT NULL,
  `f2FProgramsPoints` int(2) NOT NULL,
  `driverF2FProgramsUpdateStatus` enum('Draft','Approved') NOT NULL,
  PRIMARY KEY  (`driverF2FProgramsID`),
  KEY `driverID` (`driverID`),
  KEY `clientID` (`clientID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 


Table 2
CREATE TABLE IF NOT EXISTS `tblDriverMerits` (
  `driverMeritsID` int(5) NOT NULL auto_increment,
  `clientMeritsID` int(5) NOT NULL,
  `driverID` int(5) NOT NULL,
  `clientID` int(5) NOT NULL,
  `employeeIDInsert` int(5) NOT NULL,
  `dateTimeInsert` datetime NOT NULL,
  `employeeIDVerify` int(5) NOT NULL,
  `dateTimeVerify` datetime NOT NULL,
  `meritsPoints` int(2) NOT NULL,
  `driverMeritsUpdateStatus` enum('Draft','Approved') NOT NULL,
  PRIMARY KEY  (`driverMeritsID`),
  KEY `driverID` (`driverID`),
  KEY `clientID` (`clientID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1
3
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by newbie14
0

Dear Bob,
Sorry I thought I could get view from the programming and db guys separately. Thank you.

0

Depends on usage. If you have loads of visitors, each running intensive sql queries on every page, you'll soon start melting your account, so you may in that situation want to store temporary info in a dedicated table. However, a sum function once in a while for a quiet site shouldn't do any harm AFAIK.

Does the info have to be up to the minute or are you taking 'history' snapshots? If the latter, perhaps it would make sense to have a 'summary table' and just use that - as long as the data is static.

0

Dear Ardav,
The data is quite static is based on user keying not live from anywhere else. So what best you suggest is the summary rite on what frequency do you think is best to run the summmary. Can I like update the summary each time I enter data into the system?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.