Tue 03rd Mar 2026

Designing a schema in MySQL For an Accounting System

Database MySQL
Image for blog: Designing a schema in MySQL For an Accounting System

In this article I get into how I designed an accounting schema for mySQL that was puporse built to fit simple accounting requirement for the system. I go into the spec sheet, the tables I created and their respective mySQL create table statments.

Background

A while back I had a sit down with a former client to collaborate with an accountant to simplify their filing process as their business needs had grown to be a bit more such that the simple tracking of invoices and purchase we had earlier setup was outgrown at that point in time.

There was a need for the data stored to be anchored in some basic accounting principles which would aid in their filing process.


The Requirement Specification

To design the accounting system’s database schema I had the following specification from the sit down earlier mentioned:

  1. Ensure I store transactions into a period(or time) they occur - this is an accrual principle in accounting, an example is an electricity bill for January is an expense incurred in January but can be paid later
  2. Be able to store all accounts with their types and optionally their sub-types - the general account types are: asset, liability, equity, income and expense
  3. Be able to react to events affecting the accounting of the business example is an invoice being generated and paid
  4. Be able to store transactions reconciling to bank statements


Getting it done


The accounts and their types

This was a table which stored data relating to an account, the official accounting term for this is a ledger. I opted to name it as chart_of_accounts since it cuts across to both laymen and seasoned professionals.

The create table sql statement:

CREATE TABLE chart_of_accounts (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(150) NOT NULL,
debit_total DECIMAL(12,2),
credit_total DECIMAL(12,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL,
account_type TINYINT(3) UNSIGNED NOT NULL,
account_sub_type TINYINT(3) UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

The account_type and account_sub_type holds an enum of values specified by the programming language I was using (you can read some more about this decision on this previous article). The account types would be either of: assets, liabilities, expenses, equity, income and expenses, the sub types are varied - for example in the case of assets, there are current assets and non-current assets


The bank statement and associated transactions

These tables were to store a listing of bank statements obtained from an excel upload from a format we agreed upon, the transactions listed in the journal would have a link to these statement transactions.



The corresponding create table statements for these tables are:

CREATE TABLE bank_statements (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (id)
);

CREATE TABLE statement_transactions (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
type TINYINT(3) UNSIGNED NOT NULL,
amount DECIMAL(12,2),
transaction_date DATE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL,
statement_id BIGINT(20) UNSIGNED NOT NULL,
PRIMARY KEY (id),
KEY `statement_id_foreign` (`statement_id`),
CONSTRAINT `statement_id_foreign` FOREIGN KEY (`statement_id`) REFERENCES `bank_statements` (`id`),
);

The type field in the statement_transactions table is used to either mark the transaction as a debit or credit


The posting protocols

‘Posting’ is more of an accounting term to add a transaction on either debit or credit side to a ledger. This table would be used to store rule sets for accounts to either debit or credit when an event occurs - eg invoice creation


The corresponding create table statements for this table is:

CREATE TABLE statement_transactions (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
event TINYINT(3) UNSIGNED NOT NULL,
debit_account BIGINT(20) UNSIGNED NOT NULL,
credit_account BIGINT(20) UNSIGNED NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL,
PRIMARY KEY (id),
KEY `debit_account_foreign` (`debit_account`),
CONSTRAINT `debit_account_foreign` FOREIGN KEY (`debit_account`) REFERENCES `chart_of_accounts` (`id`),
KEY `credit_account_foreign` (`credit_account`),
CONSTRAINT `credit_account_foreign` FOREIGN KEY (`credit_account`) REFERENCES `chart_of_accounts` (`id`)
);


The transaction journal

This is a source of truth which satisfies the first requirement specification


The equivalent CREATE TABLE sql statement is as below:

CREATE TABLE transaction_journal_entries (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
type TINYINT(3) UNSIGNED NOT NULL,
amount DECIMAL(12,2),
transaction_date DATE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL,
PRIMARY KEY (id),
account_id BIGINT(20) UNSIGNED NOT NULL,
bank_transaction_id BIGINT(20) UNSIGNED NOT NULL,
KEY `account_id_foreign` (`account_id`),
CONSTRAINT `account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `chart_of_accounts` (`id`),
KEY `bank_transaction_id_foreign` (`bank_transaction_id`),
CONSTRAINT `bank_transaction_id_foreign` FOREIGN KEY (`bank_transaction_id`) REFERENCES `statement_transactions` (`id`)
);

In the app logic, type is an enum which classifies the transaction as either debit or credit. From there it also affects an accounts debit/credit as required in the totals column in the chart_of_accounts to reduce read time when generating the “Trial Balance” report - speaking of which, let’s dive into the how to get the data for such a report


Trial Balance Report Query

For this, first - I can query for all accounts and then sum their debit and credit totals. I can then display the data in the UI to generate a report.

-- List all accounts
SELECT * FROM chart_of_accounts;

-- Summarize debit and credit totals
SELECT SUM(debit_total) as sum_debit, SUM(credit_total) as sum_credit FROM chart_of_accounts;


Do you have some feedback on this? Let me know below

Share on Twitter

Any comment or feedback please share below!

Table Of Contents