Inventory system in MySQL

Miscellaneous Topics
#1

This is for a game I’m working on, but it have nothing to do with java, so I need to post it here. So far I have set up the database like this

This is the character table, inventory_id is meant to get the slots where the player can put the equipment

[quote]account_id | name | race | health | mana | exp | created | sex | character_id | world_id | inventory_id
2 | Yemto | Humanoid | 100 | 100 | 0 | 2014-06-10 20:44:28 | 1 | 7 | 0 | 1
2 | Fire | Spirit | 100 | 100 | 0 | 2014-06-11 03:00:43 | 0 | 7 | 0 | 2
2 | Test | undead | 100 | 100 | 0 | 2014-06-18 10:44:43 | 1 | 7 | 0 | 3
[/quote]
This is what the item table looks like, it’s meant to hold most of the date of an item

[quote]item_id | name | info | icon | stack_size | untradable | inventory_id
1 | bag | NULL | NULL | 10 | 0 | 2
2 | cheese | NULL | NULL | 100 | 0 | NULL
5 | tea | NULL | NULL | 100 | 0 | NULL
6 | Big bag | NULL | NULL | 100 | 0 | NULL
[/quote]
The inventory table is so far only used to set how many slots and what type of slots a inventory have.

[quote]inventory_id | normal_slots | weapon_slots | bag_slots
1 | 0 | 2 | 1
2 | 6 | 0 | 0
[/quote]
This is the item_stack table, when someone buys an item an entry in this table is created to create the item.

[quote]item_stack_id | item_id | amount | recursive_id | slot | owner | recursive_table
1 | 3 | 1 | 5 | 0 | 7 | character
2 | 5 | 2 | 1 | 0 | 7 | item_stack
3 | 6 | 5 | 1 | 1 | 7 | item_stack
4 | 5 | 6 | 1 | 2 | 7 | item_stack
5 | 3 | 1 | 7 | 0 | NULL | city
[/quote]
To get something usable from item and item_stack I’m using this

$item = $conn -> prepare("SELECT * FROM `item_stack` LEFT JOIN `item` USING (`item_id`) WHERE `item_stack_id`=:id");
$item -> bindParam(":id",$this->id);
$item -> execute();
$this -> item = $item -> fetch();

But this is where I encountered a problem. recursive_id and recursive_table in the item_stack table is to keep track of who holds the item, which works. But if I want to back track to see if the inventory size is valid, I can’t. Lets say a player find a way to put something in slot 8 in a bag, and as you probably can see, the bag don’t have that many slots. I had the idea of using the recursive_table and recursive_id to see which inventory the item have.

This is the code I began coding, until I realized that it doesn’t work.

$inventory = $conn -> prepare("SELECT `inventory_id` FROM `{$recursive_table}` WHERE `{$recursive_table}_id`=:rid");
$inventory -> bindParam(":rid",$recursive_id);

The code works for characters, but not for item_stack since that will just get another entry to another item_stack which don’t contain inventory_id. I thought of doing something like this.

if($recursive_table == "item_stack"){
$recursive_table = "item";
$recursive_id = $stack["item_id"];
}

$inventory = $conn -> prepare("SELECT `inventory_id` FROM `{$recursive_table}` WHERE `{$recursive_table}_id`=:rid");
$inventory -> bindParam(":rid",$recursive_id);

But I don’t know, it don’t seem to be the best say to me, so I was wondering is anyone here had a better solution.

This is a good example of what kind of inventory system I’m going for
http://mmohuts.com/wp-content/gallery/runes-of-magic/runes-of-magic-inventory.jpg?bb7a3b

#2

Could you explain what the point of this table is? What is its average use case.

#3

It is to create items. The table “item” contains most of the data of the items, like name, tooltip, icon, witch inventory it have. While this table “item_stack” is used to keep track where the item is, who owns it, and the amount/uses of the item.

#4

Looks a bit weird to me. Do you intend to load everything on the start?

Basically I’d start with start with some minor optimizations like putting in Ids for character name and race. Created should be an unsigned integer to store the unix timestamp. Though it is not human readable you save the step to convert it.

itemTable:
Avoid NULL fields. Depending on your actual encoding you might waste some space. From my experience it is also to consider to store any character data somewhere else. Let it be some database table or a plain file that is deployed to the client. That way you can save up a lot of space on the network.
And I don’t get the reason why you need the inventory_id inside of the item table.
The field untradable is pretty misleading. Why not naming it “is_tradeable” or shorten to “tradable”? Would make more sense.

inventory:
To store the info about which bag and so on in the inventory seem to be very weird. From my understanding someone would expect the actual inventory data there.
You might also think about how to organize everything. Most MMOs servers I’ve seen uses a fixed table size and append to the end when there is an expansion.

item_stack_table: I don’t see any usage to it

Also try to put your fields that relates to other database at the beginning of the table. That way MySql or the database of your choice don’t have read in the whole table row.

@Runes of Magic System: You may look up their private server data files …

Here is an example how I would start the first approach according to your desired outcome of RoM.


-- account
create table if not exists `account` (
	id int(10) unsigned auto increment primary key,
	email varchar(128) not null, 
	username varchar(16) not null,
	password varchar(40) not null,
	salt varchar(40) not null
) Engine InnoDb;

-- character
create table if not exists `character` (
	id int(10) unsigned auto increment primary key,
	account_id int(10) unsigned not null,
	name varchar(16) not null,	
	health smallint(5) unsigned not null,
	mana smallint(5) unsigned not null,
	-- other character stat fields	
	index(account_id)
) Engine InnoDb;

-- item
create table if not exists `item` (
	id int(10) unsigned auto increment primary key,
	icon_index smallint(5) not null,
	max_stack_size smallint(5) not null,
	tradeable tinyint(1) not null
	-- you might consider packing such flags into one field together with other flags	
) Engine InnoDb;

-- character equipment
create table if not exists char_equipment (
	character_id int(10) unsigned not null unique,	
	slot_left_hand int(10) unsigned not null,
	slot_right_hand int(10) unsigned not null,
	slot_ring_1 int(10) unsigned not null,
	slot_ring_2 int(10) unsigned not null,
	slot_chest int(10) unsigned not null
	-- you can add all other fields here too, consider shorten the field names
) Engine InnoDb;

-- inventory
create table if not exists inventory (
	character_id int(10) unsigned not null unique,
	slot_size smallint(3) unsigned not null,
	slot_1 int(10) unsigned null,
	slot_2 int(10) unsigned null,
	slot_3 int(10) unsigned null,
	slot_4 int(10) unsigned null,
	slot_5 int(10) unsigned null
	-- more fields depending on the actual size
) Engine InnoDb;
#5

No, since this isn’t a java game, It’s a turn based RPG web game, coded mainly with php and javascript. I use PDO to connect to the database, and of course html, html5 and css.

I don’t see how else I would make an item without item_stack without copying the whole item, which would be a waste of space, and would make the code much messier. How would I make it possible for a character to split stacks, or even have stacks in the first place?

I’m not sure about your idea with the slots_ in the inventory table, since I’m going to have furniture with a lot more inventory space, so I would need slot_1 to slot_50, which is way too much in my opinion. So I think it’s better to have a size number instead.

What you said about the inventory table made me think, if I made it something like; inventory_id, item_id, amount_id, slot and then I stored the last created id inside whatever have the inventory. That way I could move around items much easier. Sure the item have no idea where they are, but the right bag/furniture/character/location would have a much easier time to locate them, and maybe I could have a inventory_layout table to have specific layouts and sizes.

#6

Ah ok, that makes sense and the whole structure should apply more to a general approach. Still there can be a lot of things similar

Take a look at your own answer. The item does not need to know about the stack. Each slot/field/whatever of inventory can store exactly one item, but it will have assigned a specific amount of them. One row per stack :wink:

Yea, for a browsergame it does not make that much sense. The classical way is pretty much easier to achieve.

Yea, to continue on this depending on your inventory layouts if any you could store the row and column informations with each item you store.