A couple of years ago I designed a reward system for 11-16yo students in PHP, JavaScript and MySQL.
The premise is straightforward:
- Members of staff issue points to students under various categories ("Positive attitude and behaviour", "Model citizen", etc)
- Students accrue these points then spend them in our online store (iTunes vouchers, etc)
Existing system
The database structure is also straightforward (probably too much so):
Transactions
239,189 rows
CREATE TABLE `transactions` (
`Transaction_ID` int(9) NOT NULL auto_increment,
`Datetime` date NOT NULL,
`Giver_ID` int(9) NOT NULL,
`Recipient_ID` int(9) NOT NULL,
`Points` int(4) NOT NULL,
`Category_ID` int(3) NOT NULL,
`Reason` text NOT NULL,
PRIMARY KEY (`Transaction_ID`),
KEY `Giver_ID` (`Giver_ID`),
KEY `Datetime` (`Datetime`),
KEY `DatetimeAndGiverID` (`Datetime`,`Giver_ID`),
KEY `Recipient_ID` (`Recipient_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=249069 DEFAULT CHARSET=latin1
Categories
34 rows
CREATE TABLE `categories` (
`Category_ID` int(9) NOT NULL,
`Title` varchar(255) NOT NULL,
`Description` text NOT NULL,
`Default_Points` int(3) NOT NULL,
`Groups` varchar(125) NOT NULL,
`Display_Start` datetime default NULL,
`Display_End` datetime default NULL,
PRIMARY KEY (`Category_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Rewards
82 rows
CREATE TABLE `rewards` (
`Reward_ID` int(9) NOT NULL auto_increment,
`Title` varchar(255) NOT NULL,
`Description` text NOT NULL,
`Image_URL` varchar(255) NOT NULL,
`Date_Inactive` datetime NOT NULL,
`Stock_Count` int(3) NOT NULL,
`Cost_to_User` float NOT NULL,
`Cost_to_System` float NOT NULL,
PRIMARY KEY (`Reward_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=latin1
Purchases
5,889 rows
CREATE TABLE `purchases` (
`Purchase_ID` int(9) NOT NULL auto_increment,
`Datetime` datetime NOT NULL,
`Reward_ID` int(9) NOT NULL,
`Quantity` int(4) NOT NULL,
`Student_ID` int(9) NOT NULL,
`Student_Name` varchar(255) NOT NULL,
`Date_DealtWith` datetime default NULL,
`Date_Collected` datetime default NULL,
PRIMARY KEY (`Purchase_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6133 DEFAULT CHARSET=latin1
Problems
The system ran perfectly well for a period of time. It's now starting to slow-down massively on certain queries.
Essentially, every time I need to access a students' reward points total, the required query takes ages. Here is a few example queries and their run-times:
Top 15 students, excluding attendance categories, across whole school
SELECT CONCAT( s.Firstname, " ", s.Surname ) AS `Student` , s.Year_Group AS `Year Group`, SUM( t.Points ) AS `Points`
FROM frog_rewards.transactions t
LEFT JOIN frog_shared.student s ON t.Recipient_ID = s.id
WHERE t.Datetime > '2013-09-01' AND t.Category_ID NOT IN ( 12, 13, 14, 26 )
GROUP BY t.Recipient_ID
ORDER BY `Points` DESC
LIMIT 0 , 15
- Run-time: 44.8425 sec
SELECT Recipient_ID, SUM(points) AS Total_Points FROM
transactionsGROUP BY Recipient_ID
- Run-time: 9.8698 sec
Now I appreciate that, especially with the second query, I shouldn't ever be running a call which would return such a vast quantity of rows but the limitations of the framework within which the system runs meant that I had no other choice if I wanted to display students' total reward points for teachers/tutors/year managers/leadership to view and analyse.
Time for a solution
Fortunately the framework we've been forced to use is changing. We'll now be using oAuth rather than a horrible, outdated JavaScript widget format.
Unfortunately - or, I guess, fortunately - it means we'll have to rewrite quite a lot of the system.
One of the main areas I intend to look at when rewriting the system is the database structure. As time goes on it will only get bigger, so I need to do a bit of future-proofing.
As such, my main question is this: what is the most efficient and effective way of storing students' point totals?
The only idea I can come up with is to have a separate table called totals
with Student_ID
and Points
fields. Every time a member of staff gives out some points, it adds a row into the transactions
table but also updates the totals
table.
Is that efficient? Would it be efficient to also have a Points_Since_Monday
type field? How would I update/keep on top of that?
On top of the main question, if anyone has suggestions for general improvement with regard to optimisation of the database table, please let me know.
Thanks in advance, Duncan