database design help for game / user levels / progress

Sorry this got long and all prose-y. I'm creating my first truly gamified web app and could use some help thinking about how to structure the data.

The Set-up

Users need to accomplish tasks in each of several categories before they can move up a level. I've got my Users, Tasks, and Categories tables, and a UserTasks table which joins the three. ("User 3 has added Task 42 in Category 8. Now they've completed it.") That's all fine and working wonderfully.

The Challenge

I'm not sure of the best way to track the progress in the individual categories toward each level. The "business" rules are:

1. You have to achieve a certain number of points in each category to move up.
2. If you get the number of points needed in Cat 8, but still have other work to do to complete the level, any new Cat 8 points count toward your overall score, but don't "roll over" into the next level.
3. The number of Categories is small (five currently) and unlikely to change often, but by no means absolutely fixed.
4. The number of points needed to level-up will vary per level, probably by a formula, or perhaps a lookup table.

So the challenge is to track each user's progress toward the next level in each category. I've thought of a few potential approaches:

Possible Solutions

1. Add a column to the users table for each category and reset them all to zero each time a user levels-up.
2. Have a separate UserProgress table with a row for each category for each user and the number of points they have. (Basically a Many-to-Many version of #1.)
3. Add a userLevel column to the UserTasks table and use that to derive their progress with some kind of SUM statement.

Their current level wil开发者_JAVA百科l be a simple int in the User table.

Pros & Cons

(1) seems like by far the most straightforward, but it's also the least flexible. Perhaps I could use a naming convention based on the category ids to help overcome some of that. (With code like "select cats; for each cat, get the value from Users.progress_{cat.id}.") It's also the one where I lose the most data -- I won't know which points counted toward leveling up. I don't have a need in mind for that, so maybe I don't care about that.

(2) seems complicated: every time I add or subtract a user or a category, I have to maintain the other table. I foresee synchronization challenges.

(3) Is somewhere in between -- cleaner than #2, but less intuitive than #1. In order to find out where a user is, I'd have mildly complex SQL like:

SELECT categoryId, SUM(points) from UserTasks WHERE userId={user.id} & countsTowardLevel={user.level} groupBy categoryId

Hmm... that doesn't seem so bad. I think I'm talking myself into #3 here, but would love any input, advice or other ideas.

I'll vote for number 3.

Any time I can aggregate existing data instead of separately storing a "running total", I jump at the opportunity. For larger sites like StackOverflow, this probably isn't feasible due to performance reasons, but for a small-to-medium web game, it should work perfectly.

By avoiding keeping a "running total", you can often avoid having to do the same (or similar) actions in more than one place. For a very simple example, in a forum, if post_count is manually updated rather than calculated as a COUNT, then deleting a post will also require updating the post_count. The more situations like this you have, the more potential you have for bugs, or for "WTF?" moments for the players of your game who notice two or more numbers that don't quite add up.

Aside form performance, a major downside of using simple aggregation is that you sometimes don't want existing totals to be affected by a change you are making. I've never run into a case like this, but I'm sure they exist.

I would absolutely not go with 1. And I would like to offer a 4th approach.

If I understand you correctly a user needs a score of say 5 in a given category/level to access the next category, but can get a total of 10 points there, of which the extra 5 don't count toward the next category. The easiest way todo this is make a table like this:

| user_id | category_id | score |   (primary key = user_id, category_id)

Don't be afraid to store the total score, you can check in your lookup table / formula if "select score from table where user_id = ? and category_id = ?" is high enough.

This way you have very simple queries, a very simple and extensible database design (no problem adding new categories or changing scoring).

If on a later date you want to carry over scores, just use sql's sum function to get the total score for a user then substract this with the required summed up score to a given category.