# Storing quests for an online RPG in a MySQL database

I'm creating an online RPG game and I've been looking around for a way to save players' progressions in quests in MySQL. I've been looking at Pim Jager's answer answer for a while and I've come to the conclusion that this could work, but then my question would be:

Where/how do I store if a player currently has a quest accepted? How do I keep track of at which step of the quest the player currently is? This has to be saved somehow but I can't seem to figure out how I would do that. Let me know if you can help me out.

Thanks!

• Btw: I disagree with the premise of the linked question. The data about the quest content (not player progress in that content) is something which only changes when you release a new version of your game. That's something I would rather put into files than in a database. Files will be far easier to maintain in this situation, because you can put them under version control. That means unless you create something of WoW scale where your game is so huge that it is worth the effort to create an advanced toolchain for your quest authoring and management which benefits from having a database. – Philipp Jun 20 '17 at 9:25
• I'll keep it in mind! If I do choose to put that data into files, what do you recommend to put it in? XML? – Niels van Dam Jun 20 '17 at 13:57
• XML, JSON, YAML, an interpreted scripting language... whatever integrates well with your technology stack and fulfills your specific needs. – Philipp Jun 20 '17 at 14:17

Create a table CharacterQuestSteps with three columns:

CharacterId  UNSIGNED INT NOT NULL
QuestId      UNSIGNED INT NOT NULL
Step         INT
PRIMARY KEY (CharacterId, QuestId)


When a player-character hasn't accepted a quest yet, then there should be no entry in that table.

When the player is on a quest, the field Step says on which step of the quest they are. Which value means what will usually be quest-specific, because different kinds of quests will have different steps. The quest being finished can be a magic value (like 9999) or a value of NULL. If using magic numbers or meaningful NULLs is too dirty in your opinion, you could also add a fourth boolean field Completed.

When you have a lot of the stereotypical "Kill X mobs" or "Collect X Items" quests, you could also add another column Counter which holds the count-value for the current quest step. This architecture limits you to one counter per quest-step, though. If you want to allow quests with multiple counters in one step ("Kill 10 goblin warriors, 10 goblin archers and 1 goblin leader"), you will need a second table CharacterQuestCounters which looks like this:

CharacterId  UNSIGNED INT NOT NULL
QuestId      UNSIGNED INT NOT NULL
CounterName  VARCHAR      NOT NULL
CounterValue INT          NOT NULL
PRIMARY KEY(CharacterId, QuestId, CounterName)


When you change the step of a quest, you can delete the corresponding entries from this table, because they will likely be not applicable to the new step.

• Wow, thank you so much for the quick reply. This helps a bunch! I'll give it a go! This definitely sounds like what I needed. – Niels van Dam Jun 20 '17 at 13:42

Pim's answer includes a Quest and QuestStep table. Depending on what you want, you may choose:

• A table that maps players to quests (eg. a table with a PlayerId and QuestId). You insert a row whenever a player accepts a quest; or you can have a "status" column (accepted, completed, etc.)
• A table that maps players to quest steps. This way, you can track per-step which player accepted (or is at what state) for each quest.