RPG - storing player data

Question

Given the following tree structure, where each player logged in can have data for current and completed levels, quests per level, NPCs per quest, and multiple tasks per NPC... I'm trying to figure out the best way to store the current and completed data per player.

I asked the question before, albeit with too much detail. It was requested that I generalize the question... So if you'd like more context, see here:

https://stackoverflow.com/questions/23587430/rpg-storing-player-data-for-semi-complex-tree-structure

Some information about my RPG structure:

• Levels, Quests per Level, and Tasks per NPC are completed in chronological order.

• NPC dialog and task dialog data is stored in a js object in a npc_dialog_1.js file in the following structure:

  var dialog = {
  quests : {
      questName : {   
          NPCName: {
              TaskName:  {
                   "english" : 
                    [
                        "Hello, here is some dialog",
                    ],//more items per task
              }, //more tasks per NPC
          }, //more NPCs per quest
      }, //more quests options per "quests"
  }, //more options in dialog besides "quests" if I want
  };
  

  I am storing each dialog object in a separate npc_dialog_n.js file per map, that I'm retrieving with requireJS. This will reduce clutter in a single npc_dialog file.

• NPCs per Quest, however, can be started in any order... this is trying to mimic a GTA-style quest queue, as the game follows a general linear progression, yet for each quest you can talk to multiple NPCs and start, pause, and resume the NPC's tasks at any point.

Since the player can start, pause, and resume tasks per any NPC at a given time, I'm trying to figure out the best way to store the current and completed data per player.

mfirdaus recommended the following DB table with M:M relationship b/t user & npc... yet this would add up quickly as each player can have multiple NPCs per quest, and multiple tasks started per NPC... Any way around this set up?

My current db schema:

Thank you

Answer 1

• Having username in userstats is redundant.
• You probably would want to key userstats.id_user and users.id_users.
• userstats.id_stats as auto incremented would be ok to reference from an npc interaction.

• Remove Coords from userstats.

  table: playerLocation

  id_user | playerX | playerY | levelName
     1    |   211   |   105   |  myMap2

• Remove experience from user stats.

  table: playerExperience

  id_user | exp
     1    | 200

• Modify quest tracking integers.

  table: userstats (see below)

• Remove the questname from userstats.

• Add a column for quest complete.

• Change the quest Ids to a more useable format.

  101, 102, 103 (combination of level and quest number)

• Create a quest table:

  table: questInfo (This can have some optional fields depending on how you do quest rewards)

  questnum |   questname   | npc_id | reward_xp | reward_items | reward_money
    201    | "ride or die" |   5    |    200    |  1001, 1005  |     500

  table: npc

  npc_id | Quest 1 | Quest 2 | Quest 3 | ...
    5    |   101   |   102   |   103   | ...

• Your userstats would end up looking like:

  id_stats | id_user | questnum | questcomplete 
      1    |    3    |   102    |      0
      2    |    2    |   101    |      1
      3    |    4    |   103    |      0

I hope this gives you some guidance... the main thing is to normalize your DB. It will make it 1000x easier as you expand and build your game.

EDIT:@growler about NPC's.

Are you implying quest 1 with 4 npcs,
NPC 1 complete task
Allowed access to NPC 2
NPC 2 complete task
Allowed access to NPC 3 etc?

If so, I would treat them all as individual quest even though they aren't in the large scheme.

Quest 1 could be ID'ed as 101, quest 1 part 2, 102.

I usually add quest handling in the engine itself...but perhaps for a larger MMO style you might add in a questpreq table listing required completions.

questnum | prereq
     202 | 201

and just chain off that.

The reason the tables are split up so much is because you should always normalize your data. Depending on how you track player location...IE writing to the DB every move, or holding location on the server in memory and just writing to the DB "saving player stats" every 2~10 minutes would influence your structure. It's going to be less costly to make hits on a 3-4 column table every .6ms then a 5> table. When the user gains exp, it's a quick update. It's not something that happens very often.

If you are writing mobile/single player/ something that isn't being controlled by a reliable server/source...I would 100% keep the location table small as is and write back to it. If you're on mobile and they lose connectivity or power outages etc and you haven't "saved" data they will experience a roll back.
This is a pretty good article to take into consideration as well on updates to the DB

How often to update a Game Client about the World?