Best way to track challenges/goals completed by a member?

Question

This is to be an application-agnostic question, but one I'd like to get a better answer on.

What is the best set-up to track users completing a challenge or goal? Think similar to Stack Overflow, where if you hit a certain target you're awarded a badge.

Currently, I'm the developer of a website and attached Facebook application. I want to know the best way to track challenges completed. Currently, I have a single line of code sprinkled in with the actual controller code, which probably isn't the best way. Something like the following:

if ($upload_photo == true) {
    $challenge->perform(1);
}

As you see, the above basically fires off a call to the perform() method (of the Challenge class), which has a database look-up to see if the challenge threshold has been met thus far and if not, inserts a record and credits the member with the number of points associated with that challenge. The database set-up for this is simple, as follows:

member_id INT
challenge_id INT

And to get the number of times a member has performed a specific challenge, I just run a COUNT (*) AS count FROM table_name WHERE member_id = ? AND challenge_id = ? query.

But is this the best set-up? I think not, and open to answers on how to improve this set-up.

As aforementioned, I'm also in charge of developing a Facebook application associated with this website. There will be a progress bar that will allow users to perform certain actions and be rewarded for them. For example, bookmarking the application; liking the application; and suggesting the application to their Facebook friends.

Using Facebook's PHP SDK I can query if these actions have been done. No problem there. My problem comes for users who will leave the application, then return at a later time when there session's expired. When an action has been performed, should I store that as a record in a database table, and just query the database when a user logs into the app? Or should I just do the checks on login and store the results in a session, overwriting the relavant session keys when they trigger the relavant action?

Any suggestions for improvement on this set-up would be welcome. Thanks in advance.

Answer 1

Are you looking for advice on how to store the goals in the database? If so, the tried and tested many-to-many relationship sounds appropriate in your situation.

A table that contains users, a table that contains goals and an intermediary table containing the identifiers of each table where the users have achieved that goal.

I'm sure you're aware that with your idea of storing the data in the session, this data will be lost when the session is destroyed. Whether or not you care about that is up to you. If it's in the database then at least it is available to you indefinitely.