The following materials were created by me based on my own experience and observations of Gnucash. I have removed a lot of detail to make the diagrams easier to read. A more complete entity relationship diagram exists on the official Gnucash Wiki.
Basic transactions
Basic transactions work like this:
- A transaction row is created in
transactions with a new key.
- Two or more transaction splits are created in
splits with the same transaction key.
- These splits are each assigned an account from the
account table.
- The
splits table is at the center of this snowflake schema. It stores the details of every transaction split, such as the transaction it belongs to, the account, and the amount.
- The
transactions table is like the header table for splits. It contains high-level information such as the date, currency, and description.
- The
accounts table is the main dimension table for splits. It contains the account-level details, such as name, account code, and primary currency.
- The
commodities table contains currencies and traded commodities such as stocks. In this diagram, it is only used for currencies.
- The
slots table is used as a miscellaneous dimension table. It contains additional details for rows in other tables. I have included the slots table three times in this diagram to show the different kinds of information it contains. For example, it can be linked to transactions to get the transaction-level notes, or it can be linked to accounts to get the account-level notes or the account color.
In Gnucash, globally unique identifiers (guid) are used as keys. Technically, their data type is varchar(32). This means they are strings of length 32. They are randomly generated and contain only the hex digits 0-9 and a-f.