Maazin Ansari

Database Tables - Gnucash with PostgreSQL

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.

Entity relationship diagram for basic transactions

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.

Budgets

Entity relationship diagram for budgets

Gnucash has a budgeting functionality that allows you to set budget amounts for each account across any time period. This is mainly tracked by four tables.

budgets is the header table for the budgets.
recurrences contains the calendar settings for the budget.
budget_amounts contains the budget amount for each account during a particular period.
accounts is the same account table as before with the account-level details.

Scheduled transactions

Entity relationship diagram for scheduled transactions

Gnucash also allows you to create recurring transactions with the scheduled transactions editor. In the database, this process is a little complex. The tables schedxactions and recurrences contain the scheduling and calendar settings. You might notice the core transactions-splits-accounts relationship from the first diagram. There are some important distinctions for scheduled transactions:

The accounts, splits, and transactions here are templates that don't appear in the GUI
The accounts are children of the Template Root account rather than the Root Account. Their names are randomly generated varchar(32) hex strings.
The splits for each template transaction belong to one template account. Normally transactions contains splits for two different accounts.

In addition to the scheduling tables and the core transaction relationship, the slots table plays a major part in this functionality. It joins to splits and to itself to get the key details about the scheduled transaction: the real accounts and the numeric amounts.

Currency rates

Coming soon...

Business tables

Coming much later...

social