RPG - storing player data for semi-complex tree structure

Question

I'm making a web RPG in js, using melon JS and SQL DB with PHP. This question is about how to store completed and current tasks per non-player character (NPC).

NPC dialog and task data: all dialog is stored in a js object in the following structure:

var dialog = {
    quests : {
        quest1 : {  
            NPCName ("Joe"): {
                TaskName ("1 - Introductions") :  {
                     "english" : 
                      [
                          "Hello, here is some dialog",
                          "More dialog..." (stored in array so I can cycle through it)
                      ],//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'm not storing all map dialogs in the same file because the file would get too cluttered... so instead: when the map changes, I use js_require to load in a new js file with a new set of dialog:

loadNpcDialog : function (dialogNumber) {
    require(["./dialog/npc_dialog_level_" + dialogNumber + ".js"], function(dialog) {
    });     
},

task number: when a new NPC is created (class game.NPCEntity), I create a local variable per that instance of NPC called taskNum, set to 0. When they complete a task, I just access and increment that NPC's task number:

    game.player = me.game.getEntityByName(game.data.currNPC)[0];
    game.player.taskNum++;

For this RPG, I want to achieve the following:

• GTA-style free world quest queue: Level progression, Quest progression, and Task per NPC progression is linear (complete level 1 to go to level 2, etc), but for each Quest, a set of NPCs are generated... (you can think of them as subquests), each containing 1 through n tasks. I want to build in quest queue flexibility that allows the player to talk to any of the generated NPCs in any order... and complete the tasks in linear order (task 1 then 2, then 3...). This is like GTA style because the overall game follows a linear progression, but gives you the flexibility to start any quest you want by talking to random people in the world.

• Game data: The game should store the current and completed level, quest names per level, npc names per quest, and tasks per npc name for each Logged In Player ID. It should load the following tree (red = complete):

My question

• When the game loads, it should remember those things I mentioned in the tree above. I've set up the DB to load level and quest information (I just return the current level and quest num from DB, then loop through the NPC dialog structure shown above storing the values in a currentAndCompleteLevels and currentAndCompleteQuests array up until the loop reaches the current level and quest num from DB)... as well as the player's coordinates and experience...

However, since I'm allowing the player to start, pause, then resume any NPC task list at any time, I can't really add a npc completed num and tasks completed num column to the database. This is because I can't loop through the NPC dialog structure and load in the same way I do for level and quest information because the order in which you complete NPC quests is not linear. Somehow I have to track the completed task num per NPC. How can I do this?

I was thinking of creating a new NPCData table to store all NPCs in the game, then store current task num for that NPC... but then I would have to create a new entry for any new player who logged into my game.

Or maybe create two DB columns in userstats table, currNPC and currTask, then loop through an associative array storing all tasks per NPC? But then I would need to have a column for each completed NPC and the completed tasks per NPC. Oy, my head is spinning.

Current DB Schema:

Answer 1

I suppose this is a classic many-to-many database relationship between user and task, in which case I would suggest creating a relationship table usertasks. Here I would store the foreign keys: id_user, levelnum, questnum.

I'll also add:

• npcnum which is the NPC id for that quest and
• tasknum which is the number of tasks complete.

So a "subquest", would actually be identified by levelnum,questnum and npcnum and we can relate that to a user with id_user.

So you can load tasks done for a quest joining the levelnum and questnum from userstats to get the tasks done for each task for the current point in time for a user.

Here's an SQLfiddle of the proposed relationship table.

http://sqlfiddle.com/#!2/edea9/1

Assuming you only need to have the current subquests in memory, we can store the progress with an array of numbers with the assumption that levelnum and questnum will always be the "current" one.

var tasks=[];
function progress_task(npcNum){
  tasks[npcNum] = tasks[npcNum] ? tasks[npcNum]+1 : 1;
}
function get_task(npcNum){
  return tasks[npcNum] || 0;
}

var currNPC=1;
progress_task(currNpc);   

Caveats for having a usertasks is that you will have entries for the older quests and levels and you do end up with your worry of having an NPCData type table with subquests*users number of rows. If this feels messy, than another option perhaps is just to convert the javscript variable tasks to JSON and store that, loading it back again when you load userstats which perhaps is the simpler route.