Table design for handling counter

Question

i have requirement

1. reward will give to user but reward have some condition a. reward can have limit hourly , daily , monthly

here is table maintaining user reward tracker

CREATE TABLE `user_reward_usage` (
   `id` bigint NOT NULL AUTO_INCREMENT,
   `user_id` bigint NOT NULL,
   `reward_id` int NOT NULL,
   `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON 
                                 UPDATE CURRENT_TIMESTAMP,
   `hourly_count` int DEFAULT NULL,
   `daily_count` int DEFAULT NULL,
   `monthly_count` int DEFAULT NULL,
    PRIMARY KEY (`id`),

)

any suggestion in above table or any flaws.

Answer 1

I think this might be denormalised. You probably already have a table to store users. Create a rewards table with reward_id, user_id and store the time user has been rewarded (rewarded_at or whatever you call it) there. All the usage can be then calculated using queries to the rewards table.

In general, if you feel like you need to store some counter, I would suggest to reconsider. Sure, for a small workload it won’t make a difference but if the traffic increases, this will be a point of serialisation for the access to your data. If you already use an in-memory store like Redis, you can consider storing usage data there, on top of the inserts to the rewards table.