BG BGDatabase Home Setup How To Use Videos Example Breakdown Fields Export Import Data Binding Events Code Generation Addons

Misc

← Back Data design

Description

BGDatabase uses relational database design and entity-relationship (ER) model to organize data. Approaches, which are applicable to above-mentioned concepts, should also be applicable to BGDatabase.

Basic concepts

All data is split into several tables. Each table has a set of fields (attributes,columns) and a list of rows(entities). Each row has its own value for each attribute. There are also four special relationship fields, which we refer to as "relations", that enable referencing rows from one table to another. Originally the term "relation" had different meaning, but we use it exclusively for relationship fields.

Attributes with "list" values

Sometimes, the attribute should have a list of values. If each list element is a primitive value (like int, float etc.) you can use listXXX fields, but in many cases each list element should have several values. In this case you need to create a separate table for list values and each row of this table represents one list element. There are 2 different options for this "list" table: the NESTED table, which is created by "nested" field and a regular ROW table (The difference between 2 these options is described in another section below). Here is an example from our 2D example project, about using nested table for "list" values: each scene row has a list of collectable objects ("Collectable" nested field and related "Collectable" NESTED table), each "Collectable" object(row) has a reference to a parent "Scene" row, position, the amount of attached gold and a reference (relation) to a collectable object type ("CollectableType" table).

Relationships (relations)

Relationships are very important concept to understand, they allow to organize data more efficiently. You can navigate the relations in both directions, both in the GUI and in the code. In our 2D example project, each scene's collectable object from "Collectable" table references "CollectableType" row to indicate which object type is used. Each object type ("CollectableType" row) has its own prefab and pickup sound effect.

There is no reason to put "prefab" and "audio" fields into "Collectable" table, because all objects of certain type share this information, you only need to reference "CollectableType" row to get access to "prefab" and "audio" values.

There are four different relationship fields, each varying in the number of rows or tables that can be referenced, read more here. XXXRelationSingle creates one-to-many relationship and XXXRelationMultiple creates many-to-many relationship

Usually, many-to-many relationship is implemented with a junction table and 2 foreign keys, referencing rows from two tables. In the case of XXXRelationMultiple field, there is no need for additional table if junction table has no additional attributes.

NESTED table vs ROW table

As of version 1.8.16, the difference between "nested" field + NESTED table vs regular ROW table, referencing the "parent" table with regular relation field are minimal. Actually, the nested table references parent row with a regular relationSingle field.

Since BGDatabase v.1.8.16 both setups allow to navigate between parent and children rows easily both in GUI and in the code.

NESTED
vs ROW

Here are the differences

  1. NESTED table is not shown in the tables list
  2. When parent row is deleted, all nested rows are also deleted (it's like ON DELETE CASCADE in SQL databases)
  3. NESTED table goes with a "nested" field, attached to the parent table, but it does not grant any advantages
  4. Some of the settings, applied to parent row automatically applied to the nested row, because the nested rows are considered as a part of the parent row, for example the settings for partitions or builtin controller of SaveLoad addon
  5. A regular ROW table can serve as kind-of-a-nested table for multiple tables. In our Inventory example v.1.5 "Items" table can be treated as a "nested" table for any of the tables, it references with viewRelationSingle fields

Design advices

Unfortunately, there are no strict rules, you can follow to get the best result. Many aspects can be implemented in various ways.

For instance, imagine you have a character and character's attributes (Strength, Intelligence, Agility etc.) The simplest way to add this attributes to a character is to add them to the "Character" table.

But what if you have "Dialog" table with attributes checks, similar to Fallout games? In this case, you need a separate row for an attribute, so you can reference it from "Dialogs" table

In case you have only one character, you can put characters attributes values right into "Attributes" table (Attribute.level). But if you have several characters, you need a separate table to store attribute level for each character

← Back to Misc