Database Documentation

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.

Inheritance schema of the items related tables in the game database

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.

Example: On the picture below is portion of the misc table opened in DB Addin (some columns are hidden for better readability), which is used to define various quest items. Here the column item_name is from the base table item, columns weight and price are from the pickable_item table (inherited from the item table), ui_name and ui_info are from the player_item table (which is inherited from the pickable_item), is_quest column is from the questible_item table (inherited from player_item), and misc_type_id and misc_subtype_id are from the misc table (inherited from the questible_item table). The column item_id is the unique identifier that exists in each of these tables, and is used to match the individual rows across all these tables together.

Portion of the misc table opened in DB Addin showing various properties inherited from other tables

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.