I'm creating a single player web RPG.

I'm debating the following method of storing/retrieving quest items per player id:

  • In userstats table, have a quest_items column of type mediumtext, where I would store the total amount of the item the player has as key, and item_name as value, like so:

enter image description here

If I were to do it this way, how can I load that column data JSON into a JS object, where the key : value pairs are maintained, like so loaded for player "DAN":

quest_items = { 2 : "rice", 1 : "meat" };

  • Or, the alternative is to have some sort of association table, where I store the user_id against the item_id, maintaining single data in each column:

enter image description here

The issue with this is, each time a user gained a quest item, I would have to update this table, which could be costly.

Preferably, I would like to go with option 1, and

  • when I need to update the quest_items in userstats, I simply pass in a js object with new data, JSON_encode it, and save it into the DB column as text.

  • when I need to read it back out, just JSON_decode it

Is it bad to store k:v pairs this way in a DB field?

Update: So, it appears like option 2 is the best. Would this query be right if I wanted to - load all item data (image path, x, y location, etc) from quest_items table against a certain player ID?

$qry = 
    'SELECT u.* 
    FROM users u
    LEFT JOIN join_users_questItems juq ON (u.id_user = juq.user_id)
    LEFT JOIN quest_items qi ON (qi.item_id = juq.item_id)
    WHERE u.id_user = "' . $playerID . '"';
  • "each time a user gained a quest item, I would have to update this table" It would have to insert one row into or update 1 row of the table. Minimal cost for storing a new piece of information. Coding and decoding strings is more costly. You also lose as your queries - if you ever need queries, for example to count items per user - will be vastly more complicated. – ypercubeᵀᴹ May 15 '14 at 22:07
  • The worst of course is that you lose any chance of the database to enforce consistency. You may get a value in there like 2:rice, 1:meat, 2:rice, 1:meat, 3:meat, blue:sugar. Now how many pieces of meat are there? And how much sugar? – ypercubeᵀᴹ May 15 '14 at 22:10
  • @ypercube thank you for this. Please see above – Growler May 15 '14 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.