Creating an Accounting Information System — Part 2: Inserting Transactions

In this part we will start inserting accounts into the account table, and use those accounts to insert transactions into the transaction tables.

Our implementation is still simple and has some limitations. Namely the category table has only a few categories and doesn’t allow storing relations between accounts and creating groups.

Yet, it’ll be enough to create some accounts and use them to show how we can store transactions and prepare financial statements.

The first account we’ll create is the cash account. Looking at the structure of the account table we see the following columns:

account_id |  account_name   | description | account_code | category

The account_id is system generated, we don’t need to provide it. We only need to specify the account_name, account_code and category.

We’ll give the following codes for each account category:

Assets: 100000 to 199999

Liabilities: 200000 to 299999

Equity: 300000 to 399999

Revenue: 400000 to 499999

Expense: 500000 to 599999

Now, to insert a cash account with the code of 100100 and associate it with the asset category, we need to execute the following statement:

insert into account (account_name, account_code, category)
values ('cash', '100100', ?);

The question mark stands for the category_id of assets in the account_category table.

Since the category column in the account table is a foreign key and is linked to the category_id from account_category table,

we need to look up the asset row in the account_category table and get its id, then plug it in place of the question mark.

We get the id of the asset category by running:

select category_id from account_category where category_name = 'Asset'

This will return 1, we can substitute the question mark with 1, or even better, use this query as a subquery inside the previous one to get the category_id and insert the new account in one go.

The new statement to insert the cash account will be:

insert into account (account_name, account_code, category)
 values ('cash', '100100', 
(select category_id from account_category where category_name = 'Asset'));

Now we’ll create our second account so we can finally insert our first transaction:

insert into account (account_name, account_code, category)
values ('share capital', '302000', 
(select category_id from account_category where category_name = 'Equity'));

With these two accounts we’re ready to insert our first transaction.

So let’s insert the following transaction:

On 21/06, shareholders invested 100000 in exchange for shares.

This transaction we’ll be stored in three tables:

• The transaction table will assign a number for this transaction (it’ll be system generated), store the total value of the transaction, store a description and the date of the transaction.
• The transaction_detail table will use the transaction number created by the transaction table, and create a new row for each account affected by the transaction. In this example two rows will be created. One for the cash account, it will be debited 100000 and the row will be associated with a transaction in the transaction table using the transaction_number.
• The transaction_document table will store any documents related to a transaction using the transaction number.

Since both the transaction_detail and transaction_document tables depend on the transaction_number column from the transaction table, then we’ll have to insert a row into the transaction table first before we can insert anything into the other two tables.

First take a look at the transaction table columns:

transaction_number | value | post_date | enter_date | description

The transaction_number is system generated, we’ll only provide the value, post_date, enter_date and description.

We’ll use the function NOW() in postgres to get the current time of entering a transaction.

To insert a transaction into the transaction table we will execute the following statement:

insert into transaction (value, post_date, enter_date, description) 
VALUES (100000, '2023-06-21', NOW(), 'issuing shares in exchange for cash');

Now select all rows in the transaction table by running:

select * from transaction;

And you’ll get:

 transaction_number |    value    |      post_date      |         enter_date         |             description             
--------------------+-------------+---------------------+----------------------------+-------------------------------------
                 1 | 100000.0000 | 2023-06-21 00:00:00 | 2023-06-21 18:49:24.601231 | issuing shares in exchange for cash

The newly created row has an id of 1, we’ll use it to associate this transaction with the rows we’ll be inserting into both the transaction_detail and transaction_document tables.

Now take a look at the structure of account_detail table:

 id | account_id | value | transaction_number | description

Again, the id is system generated. We’ll provide only the account_id, value and transaction number (we’re skipping the description).

Since the previous transaction has affected two accounts, we’re going to insert two rows into the transaction_detail table.

The first row will debit the cash account by 100000, it will also store the transaction number (which is 1) to know which transaction this row belongs to.

The second row will credit the share capital account by -100000 (we use negative numbers to represent credit), it will also store the transaction number (which is 1) to know which transaction this row belongs to.

Since the transaction_detail table uses the account_id instead of the account name, we’re going to need to get the account_id of the account from the account table, and for that we’ll execute the following query:

select account_id from account where account_name = 'cash';

This will return 1. But as we did before, we’re going to use this query as a subquery nested in our insert statement.

So if we want to debit the cash account we’re going to create a new row that has the value of 1 in the account_id column, the value of 100000 in the value column (it’ll be negative in case of credit) and the value 1 in the transaction_number column.

To insert a row into the transaction_detail table, run the following statement:

insert into transaction_detail (account_id, value, transaction_number) 
VALUES ((select account_id from account where account_name = 'cash'), 
100000, 1);

The nested select statement will return 1 as the account_id of cash, 100000 is the debit value and 1 is the transaction number.

We’ll do the same thing for the second row, but this time we’ll be looking for the share capital account inside the nested query, and the value will be -100000 (again, positive for debit, negative for credit).

insert into transaction_detail (account_id, value, transaction_number)
VALUES ((select account_id from account where account_name = 'share capital'),
-100000, 1);

Now let’s see how the transaction_detail table looks like. Run:

select * from transaction_detail;

And this is the result:

 id | account_id |    value     | transaction_number | description 
----+------------+--------------+--------------------+-------------
  1 |          1 |  100000.0000 |                  1 | 
  2 |          2 | -100000.0000 |                  1 | 
(2 rows)

Every row in this table tells you something about an account that’s been affected by a transaction. The transaction with the number of 1 has affected two accounts, account number 1 (cash) and number 2 (share capital). We know that cash was debited by the fact that the value is positive, and share capital was credited by the fact that the value is negative.

Now we’re going to repeat the same transaction but we’re going to do two things differently:

• Return the transaction_number of the newly inserted row into the transaction table and store it into a variable, then use the variable in the queries that insert into transaction_detail and transaction_document tables.
• Wrap all of our related queries in a code block, this will allow us to create variables (I’m using postgres, the syntax might differ depending on whichever DBMS you’re using) and ensure that all the queries will be treated as a single transaction (a transaction in the context of a DBMS), so all information will be stored completely or nothing will be stored. This way we’ll avoid cases where a row is inserted into the transaction table but some of the rows that are related to it haven’t been successfully inserted into the transaction_detail table, which will cause imbalance when you try to prepare the trial balance because some data was lost and never recorded.

To start executing an anonymous code block and declaring our variable, we’ll type:

DO $$
DECLARE transaction_num bigint;
BEGIN

Now we can start writing our queries and make use of the transaction_num variable.

The first statement is similar to how it was before, we’ll just append <returning transaction_number into transaction_num> at the end.

insert into transaction (value, post_date, enter_date, description) 
VALUES (100000, '2023-06-21', NOW(), 'issuing shares in exchange for cash') 
returning transaction_number into transaction_num;

The statements to insert rows into the transaction_detail table are also similar to how they were before, except for the use of a variable.

insert into transaction_detail (account_id, value, transaction_number) 
VALUES ((select account_id from account where account_name = 'cash'), 
100000, transaction_num);

insert into transaction_detail (account_id, value, transaction_number)
VALUES ((select account_id from account where account_name = 'share capital'),
-100000, transaction_num);

This time we’ll make use of the transaction_document table and insert a row into it. Remember that we store paths to files inside the database, not the files themselves.

insert into transaction_document (transaction_number, document_path)
values (transaction_num, 'folder/document1.pdf');

Finally, to end the block and commit the transaction, type:

END $$;

With this we’ve come to the end of this part.

In the next part we’ll cover the ledger and financial statements, and the one after it will be tackling the problem we’ve talked about at the beginning of this article (the limitation of the account and category tables).