database design help for game / user levels / progress

Question

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 will 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.

P.S. Sorry for the cross-post. I wrote this up on SO and then remembered that there was a game dev-focused one. Curious to see if I get different answers one place than the other....

Answer 1

So the challenge is to track each user's progress toward the next level in each category.

I'm not sure why is this a challenge? You can quickly and trivially calculate each of these 5 values from the contents of UserTasks, as I understand it:

• You know their level, as it's already cached as an integer as part of the User.
• This means you know how many points they need in each category, and therefore how many are needed overall.
• So, you total up all the points they've earned from relevant tasks so far. (Which tasks are relevant? I don't know as your schema is unclear. See my second comment on the question.)
• Then you can compare these against the total, whenever you need them.

Since you have a small number of categories I would suggest just performing 5 seperate queries, one per category, clamping each value against the maximum number of points that count at this level. Add them up, compare against the total.

If a task can count towards any given level (eg. you can do Task 10 at any point and collect the associated points towards your next level) then you might need a flag of some sort to show that the points for this task have been redeemed in order to go up a level. That flag allows you to exclude them from future queries.

You could presumably do something sneaky like set the points column in usertasks to zero to mark it as redeemed, but that sort of thing leads to unmaintainable code so I wouldn't recommend it.

So, something like: select sum(points) from usertasks where redeemed=0 and category=1 - that's how many category 1 points you've built up.

And: select min(sum(points), 20) from usertasks where redeemed=0 and category=1 - that's how many category 1 points that contribute towards your total if you only require 20 points in category 1.

If you had a separate table for 'category_level' which had a field for a current level and a field for the number of points you needed in that category at that level, I think you could do all this with one query: join usertasks to categorylevel (via category if necessary), group by category, sum up the points, and take the point cap from the categorylevel table. But that's a lot of complexity when 5 simple queries will suffice.