Coding A Storage System For My Game

[AltarofScience]

i have a slight problem. how do i code the storage system? like, resources will be able to be stored in the players inventory, but they also need to be stored in many other places. so players can build storage of various kinds basically anywhere, and then caravans.

i guess i could just have a table labeled storage and have all storage there, they will have player id and coords and then just storage. or maybe i should do it relationally? like every time a player gets an amount of resource i add a new row with the coords player id and rid. i am trying to find the most efficient and easy to code method.

i guess table would be like:
storage id | player id | resource id| x | y

then to show which are usable:

select count(storage id) from storage where player id = player and resource id equals resource and x = current x and y = current y

so if i was bob at -7, 8 trying to use resource 9 it would select a count of all storage ids where those conditions were met.

and then to add res it would be, count(storage id) blah blah blah, storage max, if count>max dont add.

would this kinda thing work?

[Chris]

I will reply in a broad theorethical way. Not limited to your project/question.

Generally, we have player's inventory (items that "follow" the player) and warehouse (items tied to a location/building).

Possible solutions:
1) Separate inventory_items and warehouse_items tables. The pros are the ability to use standard item code like in all RPGs also the performance (less rows per table). The con is complexity of the code (most item relatexd functiuons has to be duplicated).
2) Make one items table and make "storageid" as the owner of the item. Items do not store the player id, but storage id. Each player is assigned storageid (which is different than playerid). This one is probably the most elegant and the most flexible. The requirement for querrying for storageid first is in practice free, since basicly always you need the player table to be read first anyway, to display the player name or HP for example, so you can get storageid too without additional cost. The con is a big items table (search cost), it is especially bad since you rarely need warehouse items (these just exist) and you frequently need inventory items (equipment, display inventory button).
3) Make one table but include excessive identifiers. Each items has playerid and optionally warehouseid (or even x,y of the map). When the item is in the inventory it has playerid=playerid, if in warehouse it has playerid=0. While it seems a crude and wrong solution, the best part is you can use standard RPG code (simply all playerid=0 will be ignored by the standard functions). Also, since playerid definitely will be indexed, it is a nice performace boost (all warehouse items of the player will be excluded by the search as not belonging to that player by all equipment checks). The con is more data in the items row.

My personal thoughts:
I dislike the 1) the most. I hate when encapsulation goes out through the window The 2) seems to be the proper soultion but... items will be the things that you will code identical way in all games you ever make no matter the genre and anything else. Most of these games will need a simple inventory system, adding storageid is a burden when developing the game. If I were to make only one game in my life and it would need such system I would go for 2) but otherwise, I'm not so sure. Right now I like 3) the most, I think. For most projects you just use the standard code, just like everyone on this planet, when you need additional warehouse feature you just add a bit of data and you don't have to change your standard equipment code, which is very nice. The cost of additional data seems not so important to my knowledge and benchmarks.

[AltarofScience]

the thing is that my players will have multiple location specific storages. So if the player owns a mine, the mine will have a storage space and those res will only be accessible from those global coordinates. Players will also have caravans, which is an automatic system of moving resources to a central area. Later on powerful and rich players will have portals, and res can be moved to an area for a cost. Possibly portals will just have hourly upkeep cost, but maybe they will open and close at specified times to move res. Players will also be able to move through portals they have permissions for.

So a player might have 20 caravans going, 10 on site storages, 5 portals, their inventory, and warehouse type buildings in cities. Also players can own shops and such. So if you had a runescape type bank or w/e, which illogically makes all items in the bank available at any bank, maybe your code will work. But my game will not be like that.

Also, I do not want to use my database like a spread sheet, and further, there are not a stable type of objects. There are a stable number of resources of various types but this number is in the thousands, and also there are crafted items with unique stats. With the table design I outlined, I can have my table store all the items regardless of nature and to create a new item type, often a unique item, i do not have to have complex table structures. Further my one table can store all the data with minimal columns. And since most of the table values are foreign ids that means nearly all the columns function as indexes. So it is super easy, even with 1million table rows, to search through the table for what I want.

My table design stores the location of the item, who owns it, makes it easy to count, doesn't require dynamically adding columns, and allows for easy item counting. I learned to make tables this way for my other game, it was a building table, and it needed to have player colony and type ids and such. I learned this
method at phpfreaks.

[AltarofScience]

Okay, I wrote a nice piece of procedural code to generate 100k items to put in storage. I can call COUNT(sid) instantly. I can call all rows where player id = x in 0.00002 seconds. I added a 9 and will now test code speed with 1million results. Any higher and it takes annoyingly long for the code to finish adding new entries, and entries will never be put in in such large quantities anyways. Also it is really easy to delete items from the storage page. And I can use update pretty easily to change locations, by altering x, y coords i think. i will post query speed once all the new entries are in the table.

So it takes like 0.0246 seconds to call all records where player id=1 from a 1mil unit table. there were 846 results i think. taking the count was pretty much instant. i suspect that it might be faster to have a total in the resource storage table, as I would have less rows? But phpfreak people claim no. I will run some tests on my database and see what sorts of systems are faster.

[Chris]

So it takes like 0.0246 seconds to call all records where player id=1 from a 1mil unit table. there were 846 results i think. taking the count was pretty much instant. i suspect that it might be faster to have a total in the resource storage table, as I would have less rows? But phpfreak people claim no. I will run some tests on my database and see what sorts of systems are faster.

You tested it by reading the table by one connection. In the game you will have much more trying to access the table at the some time, tables/rows being locked by other read/write, etc. You can only get a general comparison in such artificial tests. When you put it online it will look completely different (for example in one connection only test MyISAM storage will always be faster than InnoDB, in the real application it could be the opposite).

[AltarofScience]

So it takes like 0.0246 seconds to call all records where player id=1 from a 1mil unit table. there were 846 results i think. taking the count was pretty much instant. i suspect that it might be faster to have a total in the resource storage table, as I would have less rows? But phpfreak people claim no. I will run some tests on my database and see what sorts of systems are faster.

You tested it by reading the table by one connection. In the game you will have much more trying to access the table at the some time, tables/rows being locked by other read/write, etc. You can only get a general comparison in such artificial tests. When you put it online it will look completely different (for example in one connection only test MyISAM storage will always be faster than InnoDB, in the real application it could be the opposite).

i am aware of this yes. also, all my tables are set to innodb. obviously online it will be different, but i want to compare different systems. i actually opened the same page many times with a query as well. but all these problems apply to your methods too, and phpfreak people were adamant to do it this way.

[arai]

I've been experimenting with utilizing "spaces" to represent rooms and containers (including wearable ones.)  Didn't see any reason to differentiate.

[hiigara]

Okay, I wrote a nice piece of procedural code to generate 100k items to put in storage. I can call COUNT(sid) instantly. I can call all rows where player id = x in 0.00002 seconds. I added a 9 and will now test code speed with 1million results. Any higher and it takes annoyingly long for the code to finish adding new entries, and entries will never be put in in such large quantities anyways. Also it is really easy to delete items from the storage page. And I can use update pretty easily to change locations, by altering x, y coords i think. i will post query speed once all the new entries are in the table.

So it takes like 0.0246 seconds to call all records where player id=1 from a 1mil unit table. there were 846 results i think. taking the count was pretty much instant. i suspect that it might be faster to have a total in the resource storage table, as I would have less rows? But phpfreak people claim no. I will run some tests on my database and see what sorts of systems are faster.

An advice: worrying about performance before having a working game is usually a waste of time. I made that mistake on my first project.