sql: Storing expirable credits in database?

mardi 5 mai 2015

It is easy to store user credit with an extra col in the user table, e.g. user_credits, now an extra requirement

for each credit added to the user_credits col, it will auto expire one year after if not being consumed.

Definitely I need an extra table for storing expiring information, e.g.

[table credits_history]
user_id
credits
used
created_at

So, when consuming credits, I need to

check if the user_credits is enough to consume
loop all credits_history table for the user's credit which used = 0 AND now - created_at < 1yr, and set used to 1
update the user_credits

Finally I need to set a daily cron job to update user_credits by looking at the created_at

Are the above approach reasonable? Or any standard way to handle the above requirements?

sql