Skip to main content

Achievements / Badges system

Image
By User


I have been browsing this site for the answer but I'm still a little unsure how to plan a similar system in its database structure and implementation.



In PHP and MySQL it would be clear that some achievements are earned immediately (when a specialized action is taken, in SO case: Filled out all profile fields), although I know SO updates and assigns badges after a certain amount of time. With so many users & badges wouldn't this create performance problems (in terms of scale: high number of both users & badges).



So the database structure I assume would something as simple as: 





Badges     |    Badges_User      |    User

----------------------------------------------

bd_id      |    bd_id            |  user_id

bd_name    |    user_id          |  etc

bd_desc    |    assigned(bool)   |  

           |    assigned_at      |


But as some people have said it would be better to have an incremental style approach so a user who has 1,000,000 forum posts wont slow any function down.



Would it then be another table for badges that could be incremental or just a 'progress' field in the badges_user table above?



Thanks for reading and please focus on the scalability of the desired system (like SO thousands of users and 20 to 40 badges).



EDIT: to some iron out some confusion I had assigned_at as a Date/Time, the criteria for awarding the badge would be best placed inside prepared queries/functions for each badge wouldn't it? (better flexibility)


Source: Tips4all, CCNA FINAL EXAM
Labels:

Comments

  1. UserMay 23, 2012 at 9:50 AM

    I think the structure you've suggested (without the "assigned" field as per the comments) would work, with the addition of an additional table, say "Submissions_User", containing a reference to user_id & an incrementing field for counting submissions. Then all you'd need is an "event listener" as per this post and methinks you'd be set.

    EDIT: For the achievement badges, run the event listener upon each submission (only for the user making the submission of course), and award any relevant badge on the spot. For the time-based badges, I would run a CRON job each night. Loop through the complete user list once and award badges as applicable.

    ReplyDelete
  2. UserMay 23, 2012 at 9:50 AM

    regarding the sketch you included: get rid of the boolean column on badges_user. it makes no sense there: that relation is defined in terms of the predicate "user user_id earned the badge bd_id at assigned_at".

    as for your overall question: define the schema to be relational without regard for speed first (that'll get you rid of half of potential perf. problems, possibly in exchange for different perf. problems), index it properly (what's proper depends on the query patterns), then if it's slow, derive a (still relational) design from that that's faster. like you may need to have some aggregates precomputed, etc.

    ReplyDelete
  3. UserMay 23, 2012 at 9:50 AM

    I would keep a similar type structure to what you have

    Badges(badge_id, badge_name, badge_desc)
    Users(user_id, etc)
    UserBadges(badge_id, user_id, date_awarded)


    And then add tracking table(s) depending on what you want to track and @ what detail level... then you can update the table accordingly and set triggers on it to "award" the badges

    User_Activity(user_id, posts, upvotes, downvotes, etc...)


    You can also track stats from the other direction too and trigger badge awards

    Posts(post_id, user_id, upvotes, downvotes, etc...)



    Some other good points are made here

    ReplyDelete
  4. UserMay 23, 2012 at 9:50 AM

    I think this is one of those cases where your many-to-many table (Badges_User) is appropriate.
    But with a small alteration so that unassigned badges isn't stored.

    I assume assigned_at is a date and/or time.
    Default is that the user does not have the badges.

    Badges | Badges_User | User
    ----------------------------------------------
    bd_id | bd_id | user_id
    bd_name | user_id | etc
    bd_desc | assigned_at |
    | |


    This way only badges actually awarded is stored.
    A Badges_User row is only created when a user gets a badge.

    Regards
        Sigersted

    ReplyDelete

Post a Comment

Popular posts from this blog

Why is this Javascript much *slower* than its jQuery equivalent?

By User
I have a HTML list of about 500 items and a "filter" box above it. I started by using jQuery to filter the list when I typed a letter (timing code added later): $('#filter').keyup( function() { var jqStart = (new Date).getTime(); var search = $(this).val().toLowerCase(); var $list = $('ul.ablist > li'); $list.each( function() { if ( $(this).text().toLowerCase().indexOf(search) === -1 ) $(this).hide(); else $(this).show(); } ); console.log('Time: ' + ((new Date).getTime() - jqStart)); } ); However, there was a couple of seconds delay after typing each letter (particularly the first letter). So I thought it may be slightly quicker if I used plain Javascript (I read recently that jQuery's each function is particularly slow). Here's my JS equivalent: document.getElementById('filter').addEventListener( 'keyup', function () { var jsStart = (new Date).getTime()...
3 comments
Read more

Is it possible to have IF statement in an Echo statement in PHP

By User
Thanks in advance. I did look at the other questions/answers that were similar and didn't find exactly what I was looking for. I'm trying to do this, am I on the right path? echo " <div id='tabs-".$match."'> <textarea id='".$match."' name='".$match."'>". if ($COLUMN_NAME === $match) { echo $FIELD_WITH_COLUMN_NAME; } else { } ."</textarea> <script type='text/javascript'> CKEDITOR.replace( '".$match."' ); </script> </div>"; I am getting the following error message in the browser: Parse error: syntax error, unexpected T_IF Please let me know if this is the right way to go about nesting an IF statement inside an echo. Thank you.
5 comments
Read more