First, we shall have a table:
[guid, date, hour, entity_id, score]
The column 'guid' is the primary key of the table, which is 'date' concatenated with 'hour'.
When the score is incremented or decremented, we also update this table. Here is the crucial technique for updating:
INSERT INTO `hour_counts` (`guid`,`date`,`hour`,`entity_id`,`score`) VALUES ('#{guid}','#{time}','#{hour}','#{entity_id}','#{score}') ON DUPLICATE KEY UPDATE `score`=`score` + 1;
With this SQL statement, the record is automatically created if it is not there. If it is there, then we just increment the score.
Since MySQL handles concurrency for us, we can be sure that there is no duplicate record.
That is pretty cool.
