How to check a database table frequently?

Question

I am working on a leveling system where user will be leveled up whenever they reached certain amount of points, says when he/she gained 100 points will be leveled up to Level 2.

For this I was able to do the leveling "real time", as I will check if any new points added to the table, system will calculate whether he/she meet the criteria.

But now, there is a level down scenario, where, if user failed to upgrade to next level before current level expired (indicated with an expiry date and time), the user will be leveled down. I was able to do this if I set all the level expiry time to midnight(12 a.m.) and run a job to scan through all the users to check if any of them have to be leveled down.

My question is, if I allow the level expiry time to be not only midnight but follow the time he/she leveled up, says if user leveled up to Level 2 at 2016/3/24 9:30 a.m., the level expiry will be 2016/4/24 9:30 a.m..

In this case, how can I check if this user should be leveled down, if this user does not login or gained any new points by that time? I cannot set any job for this leveling down since all users can have different expiry time.

Thanks in advance.

Answer 1

Do it using a combination of techniques:

1) You do need a regularly scheduled job (running however often, say every 10 minutes or even hourly) that scans for new expiry times that have passed since the last time the job ran (you can store the job execution times in a separate table for this). Btw, this scheduled job can also handle any other event that needs to be handled unattended (i.e. without users having to be logged in)

2) When users do log in, explicitly check for their levels, expiry, and anything else you need. This will ensure you get synchronous (near real-time) accurate checks on their status even if the job hasn't run recently enough.

The combination of these techniques should allow you to handle any scenarios like this.

Answer 2

I had a similar situation in a commercial Provisioning system where a user should be flagged after 30/60/90 days of inactivity. For each of the flags I created a Computed Column(s) in the database which when the record was scanned (a select against the table) it would return a Boolean (bit) computed at that time for the current date whether the user had logged in within those different time frames.

You can provide similar logic by containing the last level up time as a column and then having a separate column apply the business logic to see if they have leveled up...etc.