Something that has always been a little curosity of mine is how do games like MMOs create and store information in databases?
I'm currently in school for Cybersecurity and Information Assurance, and I started database design course recently and getting through it alright, but my past investigations about how to design something at a much bigger scale brought me back to thinking about MMO's and specifically Account, Character, Inventory management in a RDMS.
Things like:
How do you index something like a Character's inventory?
How fragmented can this information get? (As in, a character may have something on line 22 in an inventory table, but also has the next item in their bag at row 4,732,119) Do/Is there ways to keep this information together?
What kind of timing do you expect to get when you have such a large table, or how do you keep it all together? Is there a join you do at character login that keeps in memory everything and only offloads it later at intervals/log off?
I'm sure I have quite a few other questions, but I'm curious how Pantheon has tackled this.
A quick way to see how most games handle it is with games that have console commands available to the player.
The first one off the top of my head most people have seen would be Skyrim and how you can add an item to your inventory as long as you know the hexadecimal code for it.
This changes a bit for an MMO where the players info and relevant item chart are all stored server side and can there for be updated/modified at any time.
There was an instance in EQ where low level rats started dropping a belt from the newest end game raids for a short time as a item code was link to the rats at some point way back when, after the update the belt now held the unused item code and started dropping as it was listed for the rats as well as the ended raid mobs.
From there its a matter of charting the players equiped or stored slots and any bags then assiging that same hex code to it.
Yeah I dont think a "additem" command is quite like the storage of items in a character inventory relate. Adding an item via drop or command just calls the system to generate it, not exactly where it lives afterwards.
The story about the rats is interesting though, sounds like generic rats were accidentally given a loot drop in their table, or the rats themselves were moved into the dungeons spawn table with the belt being part of that. Someone didn't do their job with data integrity it seems lol.
When talking specifically about how inventory information is stored, I would guess that there's an Inventory join table that tracks player_id, item_id and some metadata like position in an inventory. Because it's all stored on the server it's fetched & returned on login, like you suggested. I would think that inventory information (as well as all player information for that matter) is posted to the server whenever items are added/moved/removed; doing it on an interval risks data loss of a player's inventory updates but their game crashes before the next interval.
I wasnt so much referencing the add item command as much as how the table views gold as the item code 0000000f and locks picks as 0000000a
Hexadecimal codes are used in MAC addressing so thats how I know them but it is a way for computers to count past 9 since it counts numbers 0-9 then changes to characters a-f (so 0-f total)
The hyper link in my last post for the Item Codes shows all the different codes and how many different items you can archive with a hex setup.