The game editor and tools uses a database back-end to store all sorts of various data for the production purposes. Even though all data are exported to XML files which are used while playing the game in the end, you need the database for modifying most parts of the game.
Most of the time you don't have to work with the database directly, because the editor handles the data manipulations for you. But there are a few cases where you have to put the data into the database manually (either directly with your SQL client or preferably using our MS Excel plugin DB Addin). The most common such scenario is when you want to create a completely new item or modify some existing one.
Database inheritance structure
The database tables use sort of an
inheritance to gradually extend properties of records of the same type. There is a base table, which contains only the common properties (columns), and it can be inherited by other tables, which add more specific properties. Note that a table can inherit from a table that is already inherited from another one. For example base table item contains only the most basic properties shared by all items in the game, this table is inherited by pickable_item adding properties common for each item that can be picked up by anyone, and this table is further inherited by player_item with properties that are only needed for items that can be picked up by a player. Specifically the tables regarding items have a bit complicated inheritance structure, you can see its schema below.
When something is defined with an inherited table, this single record is in fact spanning all the inherited tables, because each inherited table in fact contains only columns that were added for that purpose, therefore its more basic data are stored in the inherited tables. Following the previous example, the base item table contains the item_name column (since that is property useful for all items), pickable_item table has weight column (because all items that can be picked up need it), but it doesn't have the item_name column itself. All these inherited tables have some sort of an id column (it's typically the primary key of the table) which value ties the specific record across all these tables together.
The DB Addin makes working with this structure easier. When you open some inherited table, the addin automatically gathers all columns of inherited tables all the way to the base table, and shows them at one place. You can tell which columns were gathered from which tables by hovering over the column's name. In general the columns are ordered from the base table to the most specific inherited table.
You can find out whether some table is inherited from some other table (and from which table) with the DB Addin. On the list of tables there is a column indicating type of each table (ttBase and ttInherited) and next column contains name of the table that it is inherited from.
Creating new records
When you want to create a new record in the database, it is important not to potentially mess up the inheritance structure. When you are creating something in the inherited tables manually (using SQL client), you have to create the matching records in all the tables all the way to the base table. Therefore it is recommended to use the DB Addin. When you open some table there, you can directly fill in the values even for the inherited tables, and the addin correctly creates rows in all necessary tables.
Even while using the DB Addin it is sometimes a bit tricky to correctly create a record in this structure (especially while dealing with items). It is important to pick a correct table where to create the new record. A rule of thumb is that you should create new records in the
leaf tables in the inheritance schema (you can see diagram of such schema for items above). Although it is not always the truth, for example when you are creating new body armor, it is supposed to be created in the armor table, which is still inherited by the helmet table.
Creating new items
The items related tables have by far the most complicated inheritance structure in the database (you can see it on the diagram above). The color indicates whether the table is potentially correct place to create a new item record or not. The green tables are the ones you are most likely going to use for creating new items. You could technically create something in the yellow tables, but it will be very rare scenario (and probably quite difficult to get working the way you want). If you would create something new directly in the red tables, it will break the database schema and then it shouldn't be possible to export it unless you create the matching record in the inheriting tables as well, which would fix the issue. But keep in mind, that the DB Addin automatically fills in the inherited tables, so the new record might be conflicting with the one created earlier.