Storage of item classes and individual items in tmwserv 0.1

[Exceptionfault]

Assumptions:

1.) Each item owned by a player is an individual item, as it could have individual modifiers like color or something else.
2.) Each item is derived from a known item class defined in the items.xml which also describes what individual modifiers are possible for each class.
3.) Items should not be limited to be a part of a players inventory. Items could also be placed in bank accounts, personal chests, send per post or carried by pets.

Actual Implementation:

Every game-server owns an items.xml file which defines the available item classes in the world.
Possible Problems: This tends to inconsistencies if new items are added, as one could forget to update all of the item definition files in parallel.
Suggestion: Only keep one items definition file, read by the account server and propagated to each game-server that registers to the account server. Implement a refresh procedure if items.xml has chagend.

The table tmw_inventories stores an item owned by a character with an individual id, its carriage slot and a reference to the item class id, defined in the items.xml. As the inventoy is handled by the account server (concerning storage in the database), but the items.xml file is only known to the game server, the account server cannot validate the item class ids. It's also not possible to define foreign keys in the database.
Suggestion: Centralize the items.xml at the account server. Keep a database table (tmw_item_classes) with all item classes, which is updated after restart of the account server, or by custom command of a admin (@refresh items database). This table contains all available item classes and common attributes like weight and amount-per-slot.
An additional table (tmw_items) keeps a row per individual item, referencing tmw_item_classes. This table is extremly compact as it only holds common attributes that are common to all item classes, but individual to each item instance, e.g. amount.
The third new table (tmw_item_attributes) holds a list of custom attributes per individual item and therefore references tmw_items. It contains special attributes like color, age, magical bonus etc... everything that makes an item unique.

Now we need n tables to link the existing items to the storages (inventory, chests, banks.. ). E.g. the table tmw_inventories. It has to be modified to no longer contain an item class and an amount. It just says, which character carries wich item in wich slot. No amount, no class. If the item is given to another player, or stored in a chest, only the reference tables have to be modified. The item itself keeps untouched.

[Bjørn]

I support the idea of having separate tables for storing item instances and their attributes.

The thing with a "tmw_item_classes" table I'm not sure about yet. It sounds to me like just an optimization, even though it's probably a significant one for the web frontend.

The idea to send the item database from the account server to the game servers is probably good as well, however it only becomes interesting once the servers are running on different machines.

[Crush]

I think that we don't need the database table tmw_item_classes. I don't think that we will ever have so many different item classes in the game that it will be too much to just keep it in memory (variety is archieved by the unique properties of every item, after all). The database should only be used for storing information which can be changed by events in the game and which has to survive a server restart/crash. But the item base classes are static information and already saved in the items.xml.

But I agree that keeping the attributes of all items in the account server makes maintenance much easier because an update of the item database only needs to be applied to the account server.

Any network traffic between game- and account server should be minimized, though.


Regarding the proposal of using one table for associating items with characters/storage locations and anotherone for associating items with their special properties: I trust your experience with databases in this regard.