How to Build an Accounting System using SQLite

Reverse Engineering Xero to Teach SQL

Kenneth Infante

Published in

Towards Data Science

17 min readMar 18, 2020

Scratch your own itch.

This is the advice that I always give whenever someone asks me how to learn programming. Practically, it means that you have to solve things or choose projects that are relevant to you — either in your work or personal life.

Mindlessly going to tutorials in Youtube, reading programming books, copying code from Reddit posts, etc. will get you nowhere if you’re starting to learn programming.

The Best Way to Learn SQL

In this post, I’m going to show you how to build a crude accounting database using SQLite.

So, why create an accounting database? Why not just copy public data, shove them to SQLite, and practice from there?

The reason is that creating an accounting database is advance enough to cover all the aspects of databases and SQL — from queries to joins to views and CTEs.

Coming from an accounting background, I think that this is the best project to learn SQL. After all, programming is a tool to solve problems. Hence, might as well “solve” a difficult one to fully learn SQL.

I got the inspiration to create an accounting system using SQL by observing how Xero works. For those who are not familiar with it, Xero is a cloud accounting software originated in New Zealand. It has now expanded to Australia, the US, Canada, and the UK.

The good thing about Xero is that it has a nice clean interface and a lot of apps to choose from to extend its functionality.

Disclaimer: I’m not an engineer nor developer at Xero and these observations may not exactly correspond to how the system works as it is always updated. Certainly, the SQL presented here is not the exact SQL design that Xero use as their system needs to scale. But this is a very interesting project so let’s do it!

Accounting 101

Before you get excited too much, let’s have a crash course first on accounting.

The fundamental accounting equation is

Assets = Liabilities + Equity

That equation has basically three parts

Assets are all the resources of the entity.
Liabilities are what the company owes.
and Equity, the accumulation of all the owner’s investment, drawings, profit or loss.

The right-hand side describes how the assets were financed — either thru Liabilities or Equity.

We can expand the above equation to break down the Equity

Assets = Liabilities + Beginning Equity + Revenues — Expenses

These 5 accounts — Assets, Liabilities, Equity, Revenues, & Expenses — are the account types that you typically see in an accounting system.

Then there’s the concept of Debit and Credit. I could go on and discuss these two in-depth but for this post, all you need to know is that in every transaction:

Debit = Credit

These two equations generally govern what’s happening in the whole accounting cycle. These two equations will also serve as a guide in creating our own accounting database.

Coming from an accounting background, I think that this is the best project to learn SQL. After all, programming is a tool to solve problems. Hence, might as well “solve” a difficult one to fully learn SQL.

Xero’s Implementation of Accounting

The important thing to remember is that Xero is designed in such a way that it will be useful to business owners (not accountants) in the day-to-day operations of the business.

As such, it designed around transaction cycles and internal control.

Transaction Cycles

The basic transaction cycles are the following

Sales Cycle
Purchases Cycle
Cash Cycle

Xero implements these cycles as follows

Sales Cycle
Sales are entered into Xero using Invoices. Imagine the business issuing actual paper invoices for sales (cash sales or on account). This is the exact thing that Xero wants to replicate.

The invoices can be printed directly from the software and they are automatically numbered in increasing order.

Under the hood, invoices increase the Sales account and Accounts Receivable (AR) account.

Purchases Cycle
Bills are entered into Xero using Bills. Again, imagine the business issuing actual bills for purchases (cash purchases or on account). This is the usual case for utilities and Inventory. This is also the thing that Xero wants to replicate.

The bills can be printed directly from the software and can be used to supplement any approval procedures done by the business.

Under the hood, bills increase the Purchases account and Accounts Payable (AP) account.

Cash Cycle
This involves all transactions pertaining to Cash. There are 4 types

Invoice Payments — payments of the outstanding Invoices
Bill Payments — payments of the outstanding Bills
Received Money — cash receipts that are not invoice payments. This may involve cash sales but if you’re going to issue an invoice, use the Invoices feature.
Spent Money — cash disbursements that are not bill payments. This may involve cash purchases but if you’re going to issue a bill, use the Bills feature.

That’s the on the transaction cycles part.

Internal Control

For internal control, you need to understand the concept of system accounts.

Xero has a comprehensive article for understanding system accounts here. But for our purposes, we’re only going to discuss the following system accounts

Accounts Receivable
Accounts Payable
Bank Accounts (linked to Bank Feeds)

These accounts cannot be used in Manual journals. This means that Xero wants you to use Invoices, Bills, and Cash Transactions (Invoice Payments, Bill Payments, Received Money, and Spent Money) to support the balance of these accounts. This is Xero’s implementation of internal control if you will.

Of course, you can use the non-system version of the AR, AP, and Bank accounts by creating them in the Chart of Accounts (COA). However, you cannot use the AR, AP, and Bank account types for them.

The important thing to remember is that Xero is designed in such a way that it will be useful to business owners (not accountants) in the day-to-day operations of the business.

Caveat: We’re Not Going Down The Rabbit Hole

Designing an accounting is really complex. It will take multiple blog posts just to cover this topic. Hence, for simplicity, we’re going to create the following assumptions (not exactly how Xero implements these)

Invoice and Bill Payments
Invoice Payments can pay two or more invoices wholly. Meaning, we’re not going to allow partial payments. The same is true with Bill Payments.
Inventory
We’re not going to use inventory items here. For sales or purchases, we’re going to use the Inventory account directly rather than create inventory items that mapped to the Inventory account.

That’s it for our assumptions. After designing our database, the reader can lift these assumptions and try mimicking Xero as much as possible.

SQL Basics

Now before reconstructing our version of Xero’s database structure, let’s have a crash course on databases.

A database is a collection of tables. Each table consists of rows of data called records. The columns are called fields.

The program to work with a database is called a Database Management System or DBMS. As a simple analogy, DBMS is to Excel program, database is to Excel workbook and table is to an Excel worksheet.

There are two main differences between a database and an Excel workbook.

Data presentation is separate from data storage.

Meaning, you cannot edit data in a database by going over the directly to the data and editing it. (Other DBMS programs have GUIs that allow you to directly access data in a database and edit it like a spreadsheet. But under the hood, that action issues an SQL command).

Tables are usually linked to each other to form a relationship.

Relationships can be one-to-one, one-to-many, or many-to-many.

One-to-one relationship means “a table row is related to only one row in another table and vice versa”. An example would be employee name to tax identification number.

This kind is usually included in a single table Employees as there’s really no benefit of separating the data into two tables.

One-to-many on the hand means “a table row is related to only one or more rows in another table but not vice versa”. An example is Invoices to InvoiceLines. An invoice may have multiple lines but an invoice line belongs only to a particular invoice.

And as you might have guessed it, many-to-many means “a table row is related to only one or more rows in another table and vice versa”. An example would be a system that implements partial payments.

An invoice may be paid partially by different payment transactions and a payment may pay different invoices partially.

How would a database knows these relationships?
It’s simple. It’s through the use of primary and foreign keys.

Primary keys are necessary to distinguish one row from another. They uniquely identify each row of data in a table.

Foreign keys, on the other hand, are primary keys from another table. Hence, by relating the primary keys and foreign keys, the database relationships are persisted.

For one-to-many, the “one” side contains the primary key and the “many” contains this primary as its foreign key. In our above example, to get all the lines belonging to an invoice, we query the InvoiceLines table where the foreign key equals a particular invoice number.

For many to many, the relationship is broken down into two one-to-many relationships through a use of a third table called the “joining” table. For example, our partial payment system will have the Invoices table, Payments table, and InvoicePayments table as the joining table. The primary keys of the InvoicePayments table will be a composite key consisting of the primary keys for the Invoices and Payments table as follows

Tables for implementing partial Invoice payments

Take note that the joining table does not contain any other data as it does not have any other purpose aside from joining the Invoices and Payments tables.

To get the invoices paid by a certain payment transaction, say PAY 1, we join the Invoices and Payments tables through the joining table and query for the payment_id = “PAY 1”.

That’s it for the basics of a database. We’re now ready to design our database.

As a simple analogy, DBMS is to Excel program, database is to Excel workbook and table is to an Excel worksheet.

Designing our Implementation of Xero

Now that we have a basic understanding of Xero to start creating a rough sketch of its database structure. Take note that I’m going to use Table_Name format. That’s is capitalized-first-letter words separated by underscores. I’m also going to use pluralized names for the table names.

For the Sales Cycle, we’re going to have the following tables

Invoices
Customers — a customer can have many invoices but an invoice can’t belong to many customers
Invoice_Payments — remember our assumption for now that there’s a one-to-many relationship between Invoice_Payments and Invoices respectively (no partial payments)
Invoice_Lines — this is the joining table between Invoices and COA. An account may appear in multiple invoices and an invoice may have multiple accounts.
Chart of Accounts (COA)

For the Purchases Cycle, we’re going to have the following tables

Bills
Suppliers — a supplier can have many bills but a bill can’t belong to many suppliers
Bill_Payments — remember our assumption for now that there’s a one-to-many relationship between Bill_Payments and Bills respectively
Bill_Lines — this is the joining table between Bills and COA. An account may appear in multiple bills and a bill may have multiple accounts.
COA — same with the above in the Sales Cycle. Just putting here for completeness.

For the Cash Cycle, we’re going to have the following tables (Payment tables we’re already created above)

Received_Moneys — may have an optional Customer
Received_Money_Lines — this is the joining table between Received_Moneys and COA
Spent_Moneys — may have an optional Supplier
Spent_Money_Lines — this is the joining table between Spent_Moneys and COA

Conceptually, our database structure is as follows

This diagram is called Entity-Relationship Diagram or ERD in the database parlance. One-to-many relationships are designated by 1 — M and many-to-many by M — M.

The joining tables are not shown in the above diagram as they are implicit in the tables with many-to-many relationships.

Implementing Our Version in SQL

Now it’s time to implement our model in SQL. Let’s start by defining some conventions first.

The primary key will have field/column name of id, and the foreign key will have the format table_id where table is the name of the table of the “many” side in singular form. For example, in the Invoices table, the foreign key will be customer_id.

Time for the SQL code. Here it is.

DROP TABLE IF EXISTS `COA`;CREATE TABLE IF NOT EXISTS `COA` (
    id INTEGER PRIMARY KEY,
    name TEXT
);DROP TABLE IF EXISTS `Customers`;CREATE TABLE IF NOT EXISTS `Customers` (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    contact_person TEXT,
    email TEXT,
    phone TEXT,
    fax TEXT,
    address TEXT
);DROP TABLE IF EXISTS `Invoice_Payments`;CREATE TABLE IF NOT EXISTS `Invoice_Payments` (
    id INTEGER PRIMARY KEY,
    tran_date DATE NOT NULL,
    description TEXT,
    reference TEXT,
    total DECIMAL(20,2) NOT NULL,
    coa_id INTEGER NOT NULL, -- automatically Bank
    FOREIGN KEY(`coa_id`) REFERENCES `COA`(`id`)
);DROP TABLE IF EXISTS `Invoices`;CREATE TABLE IF NOT EXISTS `Invoices` (
    id INTEGER PRIMARY KEY,
    tran_date DATE NOT NULL,
    due_date DATE,
    description TEXT,
    reference TEXT,
    total DECIMAL(10,2) NOT NULL,
    status BOOLEAN,
    customer_id INTEGER,
    invoice_payment_id INTEGER,
    coa_id INTEGER NOT NULL, -- automatically AR
    FOREIGN KEY(`customer_id`) REFERENCES `Customers`(`id`),
    FOREIGN KEY(`invoice_payment_id`) REFERENCES `Invoice_Payments`(`id`),
    FOREIGN KEY(`coa_id`) REFERENCES `COA`(`id`)
);DROP TABLE IF EXISTS `Received_Moneys`;CREATE TABLE IF NOT EXISTS `Received_Moneys` (
    id INTEGER PRIMARY KEY,
    tran_date DATE NOT NULL,
    description TEXT,
    reference TEXT,
    total DECIMAL(20,2) NOT NULL,
    customer_id INTEGER,
    coa_id INTEGER NOT NULL, -- automatically Bank
    FOREIGN KEY(`customer_id`) REFERENCES `Customers`(`id`),
    FOREIGN KEY(`coa_id`) REFERENCES `COA`(`id`)
);DROP TABLE IF EXISTS `Invoice_Lines`;CREATE TABLE IF NOT EXISTS `Invoice_Lines` (
    id INTEGER PRIMARY KEY,
    line_amount DECIMAL(20,2) NOT NULL,
    invoice_id INTEGER,
    line_coa_id INTEGER NOT NULL,
    FOREIGN KEY(`invoice_id`) REFERENCES `Invoices`(`id`),
    FOREIGN KEY(`line_coa_id`) REFERENCES `COA`(`id`)
);DROP TABLE IF EXISTS `Received_Money_Lines`;CREATE TABLE IF NOT EXISTS `Received_Money_Lines` (
    id INTEGER PRIMARY KEY,
    line_amount DECIMAL(20,2) NOT NULL,
    received_money_id INTEGER,
    line_coa_id INTEGER NOT NULL,
    FOREIGN KEY(`received_money_id`) REFERENCES `Received_Moneys`(`id`),
    FOREIGN KEY(`line_coa_id`) REFERENCES `COA`(`id`)
);DROP TABLE IF EXISTS `Suppliers`;CREATE TABLE IF NOT EXISTS `Suppliers` (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    contact_person TEXT,
    email TEXT,
    phone TEXT,
    fax TEXT,
    address TEXT
);DROP TABLE IF EXISTS `Bill_Payments`;CREATE TABLE IF NOT EXISTS `Bill_Payments` (
    id INTEGER PRIMARY KEY,
    tran_date DATE NOT NULL,
    description TEXT,
    reference TEXT,
    total DECIMAL(20,2) NOT NULL,
    coa_id INTEGER NOT NULL, -- automatically Bank
    FOREIGN KEY(`coa_id`) REFERENCES `COA`(`id`)
);DROP TABLE IF EXISTS `Bills`;CREATE TABLE IF NOT EXISTS `Bills` (
    id INTEGER PRIMARY KEY,
    tran_date DATE NOT NULL,
    due_date DATE,
    description TEXT,
    reference TEXT,
    total DECIMAL(10,2) NOT NULL,
    status BOOLEAN,
    supplier_id INTEGER,
    bill_payment_id INTEGER,
    coa_id INTEGER NOT NULL, -- automatically AP
    FOREIGN KEY(`supplier_id`) REFERENCES `Suppliers`(`id`),
    FOREIGN KEY(`bill_payment_id`) REFERENCES `Bill_Payments`(`id`),
    FOREIGN KEY(`coa_id`) REFERENCES `COA`(`id`)
);DROP TABLE IF EXISTS `Spent_Moneys`;CREATE TABLE IF NOT EXISTS `Spent_Moneys` (
    id INTEGER PRIMARY KEY,
    tran_date DATE NOT NULL,
    description TEXT,
    reference TEXT,
    total DECIMAL(20,2) NOT NULL,
    supplier_id INTEGER,
    coa_id INTEGER NOT NULL, -- automatically Bank
    FOREIGN KEY(`supplier_id`) REFERENCES `Suppliers`(`id`),
    FOREIGN KEY(`coa_id`) REFERENCES `COA`(`id`)
);DROP TABLE IF EXISTS `Bill_Lines`;CREATE TABLE IF NOT EXISTS `Bill_Lines` (
    id INTEGER PRIMARY KEY,
    line_amount DECIMAL(20,2) NOT NULL,
    bill_id INTEGER,
    line_coa_id INTEGER NOT NULL,
    FOREIGN KEY(`bill_id`) REFERENCES `Bills`(`id`),
    FOREIGN KEY(`line_coa_id`) REFERENCES `COA`(`id`)
);DROP TABLE IF EXISTS `Spent_Money_Lines`;CREATE TABLE IF NOT EXISTS `Spent_Money_Lines` (
    id INTEGER PRIMARY KEY,
    line_amount DECIMAL(20,2) NOT NULL,
    spent_money_id INTEGER,
    line_coa_id INTEGER NOT NULL,
    FOREIGN KEY(`spent_money_id`) REFERENCES `Spent_Moneys`(`id`),
    FOREIGN KEY(`line_coa_id`) REFERENCES `COA`(`id`)
);

A couple of things here:

SQL commands are not case-sensitive, CREATE TABLE is same as create table
The IF EXISTS and IF NOT EXISTS are optional. I’ve just used them to prevent errors in my SQL commands. For example, if I drop a non-existing table, SQLite will give an error. Also, I put IF NOT EXISTS on the create table command so that we don’t accidentally override any existing table.
Be careful with the DROP TABLE command! It will delete an existing table without warning even if it has contents.
Table names can also be written all caps or not. If table names have spaces, they should be enclosed with backticks (`). They are not case-sensitive. SELECT * FROM Customers is same as select * from customers.

Even though SQL is a bit relax with regards to syntax, you should strive to maintain consistency in your SQL code.

Take note also of the relationships shown in the ERD above. Remember also that the foreign key is on the many side.

Order is important as some tables serve as a dependency to another because of the foreign key. For example, you have to create first Invoice_Payments first before the Invoice table as the former is a dependency of the latter. The trick here is to start with the edges of the ERD as those are the ones with the least number of foreign keys.

You could also download a sample database in SQLite with no content in this link.

To view it, you can use the free and open-sourced SQLite Browser. Download it here!

Adding Contents to Our Database

Now that we have the sample database, let’s input data to it. Sample data can be downloaded from here — just break it down to CSVs as needed.

Take note that credits are shown as positives and credits as negatives.

For this post, I just used DB Browser’s import feature to import CSVs from the above Excel file. For example, to import Customers.csv

Select the Customers table

Go to File > Import > Table from CSV file and choose the Customers.csv

Click Ok/Yes to all succeeding prompts to import the data.

If you issue the following SQL command, it should show all the Customers in our database

Creating Financial Reports from Our Database

To prove that our database works as a crude accounting system, let’s create the Trial Balance.

The first step is to create the transaction views for our Invoices, Bills, Received_Moneys, and Spent_Moneys transactions. The code will be as follows

DROP VIEW IF EXISTS Invoice_Trans;CREATE VIEW IF NOT EXISTS Invoice_Trans ASwith recursiveitrans as (SELECT
    'INV'||i.id as `tran_id`,
    i.tran_date,
    i.coa_id as ar_account,
    -- ABS(total) as `total`,
    'Accounts Receivable' as `coa_name`,
    i.total,
    il.id  as `line_id`,
    il.line_coa_id,
    il.line_amount,
    ip.id,
    ip.coa_id as bank_account,
    'Business Bank Account' as `bank_name`,
    i.status
from Invoices as i
left join Invoice_Lines as il on i.id = il.invoice_id
left join COA as c on i.coa_id = c.id
left join Invoice_Payments as ip on i.invoice_payment_id = ip.id
)select
itrans.*,
c.name as `line_coa_name`
from itrans
left join COA as c on itrans.line_coa_id = c.id;SELECT * from Invoice_Trans;********************************************************************DROP VIEW IF EXISTS Bill_Trans;CREATE VIEW IF NOT EXISTS Bill_Trans ASwith recursivebtrans as (SELECT
    'BILL'||b.id as `tran_id`,
    b.tran_date,
    b.coa_id as ap_account,
    -- ABS(total) as `total`,
    'Accounts Payable' as `coa_name`,
    b.total,
    bl.id  as `line_id`,
    bl.line_coa_id,
    bl.line_amount,
    bp.id,
    bp.coa_id as bank_account,
    'Business Bank Account' as `bank_name`,
    b.status
from Bills as b
left join Bill_Lines as bl on b.id = bl.bill_id
left join COA as c on b.coa_id = c.id
left join Bill_Payments as bp on b.bill_payment_id = bp.id
)select
btrans.*,
c.name as `line_coa_name`
from btrans
left join COA as c on btrans.line_coa_id = c.id;SELECT * from Bill_Trans;********************************************************************DROP VIEW IF EXISTS Received_Money_Trans;CREATE VIEW IF NOT EXISTS Received_Money_Trans AS
SELECT
    'RM'||rm.id as `tran_id`,
    tran_date,
    coa_id,
    'Business Bank Account' as `coa_name`,
    total,
    rml.id  as `line_id`,
    rml.line_coa_id,
    c.name as `line_coa_name`,
    rml.line_amount
from Received_Moneys as rm
left join Received_Money_Lines as rml on rm.id = rml.received_money_id
left join COA  as c on c.id = rml.line_coa_id;SELECT * from Received_Money_Trans;********************************************************************DROP VIEW IF EXISTS Spent_Money_Trans;CREATE VIEW IF NOT EXISTS Spent_Money_Trans AS
SELECT
    'SM'||sm.id as `tran_id`,
    tran_date,
    coa_id,
    'Business Bank Account' as `coa_name`,
    total,
    sml.id  as `line_id`,
    sml.line_coa_id,
    c.name as `line_coa_name`,
    sml.line_amount
from Spent_Moneys as sm
left join Spent_Money_Lines as sml on sm.id = sml.spent_money_id
left join COA  as c on c.id = sml.line_coa_id;SELECT * from Spent_Money_Trans;

In the first two statements, I’m using a CTE (with the keyword recursive). CTEs are useful as I’m combining 4 tables to get a single view for Invoice transactions and corresponding payments. You could learn more above CTEs in SQLite here.

After executing the above command, your database should have the following 4 views.

Finally, we create the code for the Trial Balance or TB for short. Note that TB is just a collection of the balances of our transactions taking note of the rules we laid down when we designed our database.

The code is as follows

DROP VIEW IF EXISTS Trial_Balance;
CREATE VIEW IF NOT EXISTS Trial_Balance as
-- CREATE TB
-- select all sales
select
    line_coa_id as acct_code,
    line_coa_name as acct_name,
    (case when sum(line_amount) > 0 then sum(line_amount) else 0 end) as debit_bal,
    (case when sum(line_amount) < 0 then sum(line_amount) else 0 end) as credit_bal
from Invoice_Trans
group by line_coa_id-- select all purchases
union allselect
    line_coa_id as acct_code,
    line_coa_name as acct_name,
    (case when sum(line_amount) > 0 then sum(line_amount) else 0 end) as debit_bal,
    (case when sum(line_amount) < 0 then sum(line_amount) else 0 end) as credit_bal
from Bill_Trans
group by line_coa_id-- select all received money
union allselect
    line_coa_id as acct_code,
    line_coa_name as acct_name,
    (case when sum(line_amount) > 0 then sum(line_amount) else 0 end) as debit_bal,
    (case when sum(line_amount) < 0 then sum(line_amount) else 0 end) as credit_bal
from Received_Money_Trans
group by line_coa_id-- select all spent money
union allselect
    line_coa_id as acct_code,
    line_coa_name as acct_name,
    (case when sum(line_amount) > 0 then sum(line_amount) else 0 end) as debit_bal,
    (case when sum(line_amount) < 0 then sum(line_amount) else 0 end) as credit_bal
from Spent_Money_Trans
group by line_coa_id-- select all AP
union allselect
    ap_account as acct_code,
    coa_name as acct_name,
    -(case when sum(line_amount) < 0 then sum(line_amount) else 0 end) as debit_bal,
    -(case when sum(line_amount) > 0 then sum(line_amount) else 0 end) as credit_bal
from Bill_Trans
where status = "0"-- select all AR
union allselect
    ar_account as acct_code,
    coa_name as acct_name,
    -(case when sum(line_amount) < 0 then sum(line_amount) else 0 end) as debit_bal,
    -(case when sum(line_amount) > 0 then sum(line_amount) else 0 end) as credit_bal
from Invoice_Trans
where status = "0"-- select all bill_payments
union allselect
    bank_account as acct_code,
    bank_name as acct_name,
    -(case when sum(line_amount) < 0 then sum(line_amount) else 0 end) as debit_bal,
    -(case when sum(line_amount) > 0 then sum(line_amount) else 0 end) as credit_bal
from Bill_Trans
where status = "1"-- select all invoice_payments
union allselect
    bank_account as acct_code,
    bank_name as acct_name,
    -(case when sum(line_amount) < 0 then sum(line_amount) else 0 end) as debit_bal,
    -(case when sum(line_amount) > 0 then sum(line_amount) else 0 end) as credit_bal
from Invoice_Trans
where status = "1"-- select all received_money
union allselect
    coa_id as acct_code,
    coa_name as acct_name,
    -(case when sum(line_amount) < 0 then sum(line_amount) else 0 end) as debit_bal,
    -(case when sum(line_amount) > 0 then sum(line_amount) else 0 end) as credit_bal
from Received_Money_Trans-- select all spent_money
union allselect
    coa_id as acct_code,
    coa_name as acct_name,
    -(case when sum(line_amount) < 0 then sum(line_amount) else 0 end) as debit_bal,
    -(case when sum(line_amount) > 0 then sum(line_amount) else 0 end) as credit_bal
from Spent_Money_Transorder by acct_code

The above code contains multiple SQL queries joined by the command union all. I’ve annotated each query to show what each is trying to achieve.

For example, the first query tries to get all the credits for the Invoice transactions (mostly Sales). The second one for the debits of the Bill transactions (mostly Purchases) and so on.

Executing it should result in the following TB.

You can put this in Excel to check that debits equal to credits (which I did). Total debits and credits are 14115 and -14115 respectively.

Hooray! You’ve Made It

Creating an accounting system is really complex. We essentially explored the whole gamut of database design — from concepts to ERD to creation to querying it. Pat yourself on the back for reaching this far.

Take note that we deliberately limited our database to focus more on the concepts. You can lift these and try to build another one without the restrictions.

That’s it! You’re now an SQL ninja! Congratulations!