Game Inventory Database

[RGStrat]

Hello Everyone,

So I've begun a new project, in an attempt to show off my progress with Javascript. I'm making a little text-based RPG. However, I would like for it to permanently save your character and their progress. Thus, I need a MySQL database.

I won't lie, I've only used MySQL very sparingly in the past and I am very much a beginner. Thus, I'm still struggling whats the best way to even format the database and tables.

Here's my idea, 3 tables:

**Users:**

userid


**Item Description:**
       | Name | Type | Attack |
itemid

**Player Inventory:**
       | itemid | itemid | itemid|
userid

-------------------------------------------------------------

Hopefully this makes sense. I don't plan on tons of inventory items. Perhaps 20 total and they don't need to be unique. Does this sound like an efficient way to do this?

Thanks in advance!

[RGStrat]

Hmm. Not sure what happened but probably obviously the users table looks like:

        |Name|Gender|Endurance|, ect.
  userid

[mattster]

You users table is fine. (Make sure you're using the right datatypes - varchar(X)/int() - and A_I/PRIMARY_KEY for the ID)

Item Desc: ID,NAME,TYPE,ATTACK (again with correct datatypes)

Inventory: ID, USER_ID, ITEM_ID, [.. anything else you might need] (all int() datatypes, with A_I/PRIMARY_KEY for the ID)

Use a new row for each item in a users inventory. Otherwise 20 columns is uneccisarily bulky. This also makes time for a LEVEL column, for instance.

Hopefully that makes sense?

[diafol]

Is MySQL your best option here?

You may find node.js and MongoDB (using mongoose) better for RPG, especially if you're going to be working in 'real time'. Otherwise, it seems you may be stuck with Ajax-type calls (or even traditional link/form submits). Another option would be to use websockets, which would allow you to connect both ways (using MySQL if you like) without polling/ajaxing. Just a thought.

If you are aiming to create a multiplayer RPG, then sockets may be the way to go. Similar to modern chat apps, data is pushed, rather than pulled (traditional Ajax methods).

[RGStrat]

Thanks, mattster! If I'm understanding you correctly, you're saying for the Inventory table that each instance of a item should be its own row, and that the columns will indicate both the ITEM_ID and the user it belongs to? The only advantage I saw to the other way was easily being able to mark the quantity of this type of item in the user's inventory. However, I suppose I could do it the same way with this way, and just use a function to determine if an instance of both "ITEM_ID" and "USER_ID" in the table, and if not create a new one.

Appologies if this sounds pretty basic, I'm still trying to wrap my head around data management. Thanks!

[diafol]

The player_inventory table would ideally just have two fields:

user_id + item_id

However, the items may have certain values attached to them, different to the values in the items table. For example, An item may be partially depleted, so instead of having a 120 points attack value, this specific item now only has 40 points attack value. This depends entirely on how complicated you need your game to be. Also as you note, if you just have primitive (one-use or endless-use) items, then a quantity column for the player_inventory could be useful.

[RGStrat]

Hello diafol,

Honestly I just picked MySQL since I've used it a long time ago and it's the DB solution I've heard the most about, but I'm certainly not married to it. I'll be sure to check out MongoDB and node.js. My plan was to just us AJAX since I'm trying to make this a mostly-javascript project, but if that is just too cumbersome then I would happily switch.

In truth I'm somewhat regretting the decision to make this information storable, as opposed to a client-only solution with javascript. All this focus on the data is a bit of a drag on the rest of the process. Starting to think I've gotten in over my head!

My goal would be to rely on javascript as much as possible. It's just going to be single-player for simplicity.

[diafol]

If it's single player, then Ajax makes more sense than if it were multiplayer. MySQL should be fine.

[RGStrat]

Thanks again for the help!