for now, system should have 6 different user levels.
each level gained upon user activity, example:
level 1 - when user register level 2 - when user completes mission level 3 - when user completes more 1 mission level 4 - when user donate > $x amount of money level 5 - when user write more 50 comments in blog level 6 - when user complete quiz and now... i'm wondering, what's best database schema achieve this? should keep track of actions related user's activities, that's why though xp_events table... this:
id # primary key event # type of event, e.g. 'register', 'complete_quiz', etc. user_id # id of user delta # number of "exp" gained after specific action and... in users table keep record of current level , "exp" each user has earned until now.
when user makes activity, call trigger check if user have new level unlocked.
but... i'm aware in long term (e.g. if more levels added), isn't optimal solution.
looking forward suggestions.
i see 2 obvious possibilities here.
one have event table say, user id, event id, dollar amount (for donations), date/time, maybe other data. if reason why keeping of data determine each user's level, simple , effective.
if you're keeping track of data other purposes also, want separate multiple tables. in such case have other data need keep each event. donation need dollar amounts, assume don't apply comments , missions. comments need text of comment , indication of thread comment on or it's subject is. mission -- don't know "mission" in context, want information type of mission , or supposed kill or whatever. of data not applicable events of different types. comment doesn't have dollar amount, mission doesn't have thread, etc. you'd end lot of irrelevant data , bunches of null fields.
