Database design support for game / user levels / progress

Question

It is a pity that it turned out long and all the prose. I am building my first truly gaming web application and I can use some help thinking about how to structure the data.

Customization

Users need to complete tasks in each of several categories before they can level up. I have a Users , Tasks and Categories table, and a UserTasks table that combines the three. ("User 3 added task 42 to category 8. Now they have completed it.") This all works fine and works great.

Task

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

• You need to reach a certain amount of points in each category in order to advance.
• If you get the number of points you need in Cat 8, but you still need to do other work to complete this level, any new Cat 8 points will be calculated according to your total score, but will not be “rolled over” to the next level.
• The number of categories is small (five at present) and are unlikely to change often, but it is by no means absolutely fixed.
• The number of points needed to level up will depend on the level, possibly on a formula or, possibly, a lookup table.

Thus, the challenge is to track each user's progress to the next level in each category. I thought of several possible approaches:

Possible solutions

• Add a column to the users table for each category and reset them all to zero at each user level.
• You have a separate UserProgress table with a row for each category for each user and the number of points they have. (Basically, the Many-to-Many option # 1.)
• Add the userLevel column to the UserTasks table and use it to get your progress using some sort of SUM statement.

Their current level will be a simple int in the User table.

Advantages and disadvantages

(1) It seems by far the easiest, but it is also the least flexible. Perhaps I could use a naming convention based on category identifiers to help overcome some of them. (With code like " select cats; for each cat, get the value from Users.progress_{cat.id} .") It is also the one where I lose most of the data - I won’t know which points are counted for alignment. I have no need for this, so maybe it doesn’t bother me.

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

(3) is somewhere in the middle - cleaner than # 2, but less intuitive than # 1. To find out where the user is, I would have complex SQL code:

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

Hmm ... this is not so bad. I think I'm talking about myself in No. 3 here, but I will love any materials, tips or other ideas.

Answer 1

I will vote for number 3.

Each time I can aggregate existing data instead of separately storing the "current amount", I can take this opportunity. For larger sites like StackOverflow, this is probably not possible for performance reasons, but for a small to medium web game it should work fine.

Not allowing you to save the "total", you often can not perform the same (or similar) actions in several places. For a very simple example, in a forum, if post_count updated manually and not calculated as COUNT , then deleting a post will also require updating post_count . The more such situations you have, the more opportunities you have for errors, or for "WTF"? moments for players in your game who notice two or more numbers that don't quite add up.

Besides performance, the main disadvantage of using simple aggregation is that you sometimes do not want existing totals to be affected by the change you are making. I have never encountered such a case, but I am sure that they exist.

Answer 2

I would not go with 1. And I would suggest a fourth approach.

If you correctly understood that the user must get a rating of 5 in this category / level to access the next category, but can get a total of 10 points, of which an additional 5 are not taken into account in the next category. The easiest way to do this is to make the table as follows:

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

Do not be afraid to keep the total score, you can check your search table / formula if "select a rating from the table where user_id =? And category_id =?" high enough.

Thus, you have very simple queries, a very simple and extensible database design (no problem adding new categories or changing the number of points).

If at a later date you want to transfer points, simply use the sql sum function to get the total score for the user, and then compare with the required totalized score to a certain category.