better schema for a database

i have an application in which a user can lock a certain commodity and once he locks it,he has to pick it up in 2 hours. If he doesnt pick it up in 2 hours then that item is unlocked and user loses 1 locking chance. the user has 3 locking chances initially and if he loses all 3 in 2 months time then he is banned for 2 months. now i have prepared a schema for this but i feel its not optimum. the schema looks like this

user table
--locking_chances_left              //initially 3
--first_chance_miss_date            //date on which the user loses its first locking chance
--second_chance_miss_date           //date on which the user loses its second locking chance
--banned                            // boolean field to indicate whether the user is banned

locked_items table
--item_no
--user_id
--locking_time

banned_users table
--user_id
--ban_date                          //date on which the user is banned i.e lost the last chance

now i have a event which is scheduled to run every minute to see if any item in locked_items table has been locked for more than 2 hours and if it finds any then it removes it from this table which unlocks the item and then decreases locking_chances_left by 1 from the开发者_如何学运维 users table. now i have to keep track of whether a user loses all his chances in a period of 2 months to ban him. so i have kept first_chance_miss_date to keep the date when his chances decrease from 3 to 2 and second_chance_miss_date to keep the date when his chances decrease from 2 to 1. i have an after update trigger on users table that checks when the value of locking_chances_left is changed and it updates the first_chance_miss_date and second_chance_miss_date accordingly. is there some better way without using these 2 fields for miss dates and just using one field. thanks for bearing this

---

I'd probably do this with a "user_missed_date" table with user_id and missed_date as fields you can then

select user_id, count(*) as misses from user_missed_date where date>[last two months] group by user_id

Or use that as the basis for a subquery.

You would probably want indexes on both user_id, missed_date and missed_date,user_id

---

I don't think this is a better solution, but I'll throw it out there:

You could have a table of lock_events, instead of locked_items. Every time an item gets locked, it goes in the event table. If an item gets picked up, you could either delete it, or you could add an additional event saying it was picked up. If you select items that are older than 2 hours, you get a list of expired locked items.

This way you have a history of all the events in the system. It's simple to calculate chances_left and also simple to see if the user burnt all his chances in a 2 month period. You end up doing more CPU cycles here, but you also get a nice record of all the transactions on your site!