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.

enter image description here

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:


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?

enter image description here

My current db schemaenter image description here:

Thank you


1 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?

  • \$\begingroup\$ The formatting confuses me. The makeshift tables don't line up in my browser and unfortunately StackExchange has no plans to support tables. Did you mean the *s at the start of lines to start bullet points? Where do the bullet points end? \$\endgroup\$
    – Anko
    Commented May 12, 2014 at 22:58
  • \$\begingroup\$ I will try to make some graphics to clear it up some more, sorry it's not displaying right. \$\endgroup\$ Commented May 12, 2014 at 23:24
  • 2
    \$\begingroup\$ Use preformatted text (code blocks) to do ASCII alignment. \$\endgroup\$
    – user1430
    Commented May 13, 2014 at 1:08
  • \$\begingroup\$ I wouldn't split location and experience into extra tables. After all, each and every player will have one of both in a strict 1:1 relationship. \$\endgroup\$
    – Mario
    Commented May 13, 2014 at 8:19
  • \$\begingroup\$ @Mario Right. Do you think you could provide an answer to this? \$\endgroup\$
    – user3871
    Commented May 13, 2014 at 16:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .