Question 190751 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Design for game achievement system with multiple levels

I design a game achievement system for a MySQL database. Each achievement has 3 levels and each level contains a different reward. Achievements and their levels and rewards are the same for every user.

Table achievement:

id_achievement title description level_1 level_2 level_3 reward_1 reward_2 reward_3

The description column will be VARCHAR and contain a description of how to achieve the achievement with a placeholder for the level value. Each level will be an INT (value needed to complete that level).

Table user_achievement:

id_user id_achievement progress reward_collected achievement_completed

My idea is to calculate the level for an achievement by comparing progress to achievement’s level.

Please point out weaknesses and recommend improvements (efficiency, storage, etc.). One issue is if user does not collect their reward for completing an achievement’s level before completing the next, previous reward will be overridden/skipped.

Tags:

2

Answers


  1. 0

    Your initial design is a good start. However, you need to focus on some areas to improve your design efficiency and also to address the issue you mentioned. Some suggestions are mentioned below,

    1. Seperate achievements and levels:

    Achievement:
    id_achievement (PK), title, description

    Level:
    id_level (PK), id_achievement (FK), level_number, requirement

    Reward:
    id_reward (PK), id_achievement (FK), level_number, reward_details

    1. Create a table to store the progress of the user at the level level rather than achievement level.

    user_achievement_progress:
    id_user (FK), id_level (FK), progress

    1. Create a table to track the record collection also to prevent overriding or skipping of records.

    user_reward_collection:
    id_user (FK), id_reward (FK)

    And also its mentioned that VARCHAR is being used, so make sure to have an appropriate length for column so that storage can be optimized.

    Login or Signup to reply.
  2. 0

    You should use a normalized table that will need more tables, but keep the tabels clear of repattitions

    Achievement

    id_achievement title description

    level

    id_achievement level numberofitem reward

    user_achievement

    id_user id_achievement progress status level

    status

    id_status statusreached
    1 reward_collected
    2 achievement_completed
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search