database design help for game / user levels / progress
- by sprugman
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:
You have to achieve a certain number of points in each category to move up.
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.
The number of Categories is small (five currently) and unlikely to change often, but by no means absolutely fixed.
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
Add a column to the users table for each category and reset them all to zero each time a user levels-up.
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.)
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.
