Database Design

The Basic

In an accounting system, basically there are two main storages. Yes, only two. One to store the accounting transactions and one to store the chart of account. But only the simplest accounting system needs only two storages.

These two storage exist in every accounting system, from the simplest to the most complex one.

The reason is in an accounting system, everything is an accounting transaction. And every accounting transaction belongs to one of the account in the chart of account.

Let's take a look to an example. Suppose you have a sales transaction on 1 January 2001. You've just sold a TV set. Paid by cash $45. In accounting system you will record (usually in a journal book) this transaction as:

This proses of recording transactions is called journalizing.

It doesn't matter what kind of transaction you have, sales, purchases, or else, you will record the transaction in a format like the example.

You might think "Ok, that's journalizing, but an accounting system is more than journalizing transactions!".

You're right, but journalizing is the heart of an accounting system. Let's move on to the accounting processes after the journalizing. After the journalizing there will be other process called posting. Posting is transferring the journal entries (accounting transactions) to another book called ledger.

A ledger also contains the transactions only grouped by the account. Similar with the journal.

The posting proses can be eliminated if you use computer. Because it's all the same. A journal book and a ledger contains the same data. The difference between a journal and a ledger is a matter of presentation. If you use computer those two can be created using one source of data. For example using SQL queries that differ only at the GROUP BY clause.

After the posting, the accounting cycle continues to the presentation of the transactions summary. In short, generating trial balances and financial statements.*

Trial balances and financial statements is generated by summarizing the recorded transactions.

The reason why manual accounting system use separated storage for the transactions (journal book and ledger) is to ease the making of trial balances and financial statements. Trial balances and financial statements show only the accounts, not the transactions.

Using the computer, those trial balances and financial statements are easily generated from one source only. The storage that stores the transactions.

Ok, I said there are two main storage. Well, if we use only one source then there will be many redundant data. That can be reduced by dividing the source into two. One for the transactions and one for the accounts.

Here are the ilustrations:

One Source:
Transactions = {Date + Account + Account Class + Description + Dr + Cr}

Two Source:
Transactions={Date + Account + Description + Dr + Cr}
Account = {Account + Account Class}

You might think "There are still duplication there.".

You're right again. But that's too detail. I'm not trying to show the duplication here. I divided it into two because they are obviously different. The Account is not a transaction. And transaction is a transaction.

And as I said earlier, only the simplest accounting software use two storage.

Sample Database

Now we are moving into designing a simple data storage for a simple accounting software.

Before we begin, remember that this is only a sample application. If you find it that you can use it in the real life. Use it at your own risk.

The target is to provide storage for accounting transactions and to provide an ease of trial balances and financial statements generations. We'll use relational database for this one.

First the basic storage, we will need three storage for the transactions and the accounts. Two for the transactions and one for the accounts.

Transactions:
Ledger_Transactions = {TransactionNo+Source+DateStamp+Description+Posted}

Ledger_TransactionDetails = {FKID_Transaction+FKID_Account+Seq+Amount}

Accounts:
Ledger_Accounts = {AccountNo+AccountName+Notes+FKID_AccountClass}

The transactions storage is divided into two tables to reduce data duplication

Notice the FKID_AccountClass on the Ledger_Accounts, that one is for the account class like assets, revenues, etc. Actually it is enough if we just replace it with just AccountClass. But I want it to have more meaning so I added one more table for describing the account class:

SYS_AccountClasses = {AccountClassID+Name+Description}

That's enough for transactions storage. But not enough to provide the ease to generate trial balances and financial statements.

So we need three more tables.

Ledger_General = {FKID_Account+PeriodYear+Period+Debit+Credit}

Ledger_AccountBalances = {FKID_Account+PeriodYear+BeginningBalance+ Saldo1+Saldo2+...+Saldo12}

SYS_Company = {Name+Address+Phone+Fax+eMail+BeginPeriod+BeginPeriodYear+ CurrentPeriodYear+FinancialPeriodBegin}

The first two is used to store summary of transactions for each period in the financial periods. The Ledger_General is used like a ledger in manual accounting system, the difference is the Ledger_General stores only the summary for each period.The Ledger_AccountBalances is used as storage for the summary of all periods.

The last one, SYS_Company is used to generate the header for the trial balances and financial statements, and also used in some calculation for the system.

You might think that there are many duplications here. Again, you are right. It is the trade-offs between ease and pain. But you will see that we can reduce the duplication by using the tables the "good way".

The good way to use those tables is to use it periodically. Which means that periodically those tables will be cleaned-up. For example, at the end of the financial periods, the transactions storage (Ledger_Transactions and Ledger_TransactionDetails) will be erased, so that leaves the tables empty, so duplication is reduced.

The question is what if we need the data again? Well, you can't see the data again. It's gone!

But don't worry, you just need more tables if you want to be able to see the erased data.

The trick is to add history tables. So if you want to see the data again, just find it at the history tables. So there are some extra work when you clean-up the transaction storage, you don't just erase it, but also put it into the history table.

So for this one we need three more tables:

Ledger_AccountBalanceHistories = {same with Ledger_AccountBalances}

Ledger_GeneralHistories = {same with Ledger_General}

Ledger_TransactionHistories = {TransactionNo+FKID_Account+Seq+Source+DateStamp+Description+Amount}

Thats will cover the cleaning-up problem.

Back to the trial balances and financial statements generations. By using the tables you can easily build them. Use the Ledger_General for trial balances, and use Ledger_AccountBalances for financial statements.

Well, that's it. You just build a simple accounting software database. :)

* Between the trial balance and financial statements there might be other processes like adjustments, reversing entries, etc., but those processes more or less are accounting transactions too. So we can store them on the same storage like the other transactions.