Double Entry Accounting Schema

Question

Setting up a double entry accounting system for personal use and to help manage a really small business. Trying to put a few features that seem relevant now.

Business Rules

The logic, for those not familiar with accounting, is: money is not created nor destroyed, it is only transferred from account to another. Each transaction has a Debit side, and a Credit side. A few examples:

1. Salary from your employee: Credit Salary, Debit Bank Account - the money came from your salary, and went to your bank account.

2. Pay of rent: Credit Bank Account, Debit Rent - the money came from your bank account and went to your rent account.

Accounts can be 'stock' accounts, in the sense the balance of the account is cumulative (bank accounts are a good example) or can be flux/flow accounts, in the sense that the balance of the account is non-cumulative (rent is a good example).

The logic behind the design

The idea is to have a major JournalDB table which is stores the main entries. The table JournalTx stores each account involved in the transaction. Each entry (from JournalDB) has an ID, and each a transaction (from JournalTx) is linked to a Journal Entry. The base case scenario is that there's 1 entry on JournalDB and two (or more) transactions in JournalTx. Each entry can have a cost_center, a project, and a few other attributes.

There are basically two ways of designing that (as per this question) - as a one-row per transaction style, and a two row per transaction. In the first, I would have one line with the credit account and the debit account, on the second (this one) there are n-lines, one for each account affected.

Accounts

The Accounts table is the Chart of Accounts (on accountant lingo). Is has a hierarchical structure - I used the adjacency list style. Although not very frequent, accounts will have CRUD operations. I added parent_imediate, parent_second as a really ugly solution to make aggregations (calculate the total of the Assets account, for example), but given the challenge (have no idea how to make that after a long research), it seemed like an easy way out - any input or suggestion on that matter is also welcomed.

Main Queries

Get the reports, usually montlhy: basically all acounts with the aggregated transactions that affected each one of then. The best case scenario would be of a pivot table (columns as dates), with each row is an account. I guess an "stacked" version of this would work just fine also.

Accounts are just one dimension - I may want to query by cost_center or by project for example.

Other features

I want to have the ability to budget accounts (hence the budgets table), as well as have 'goals' (I want take vacations that will cost me $ 1.000). I also want to have tags, and be able to set up recurring bills (which are "expected" transactions)

Basic relations

One entry (journal_db) have many transactions (journal_tx). One cost_center, project, etc have many entries One account has many transactions. One contact has many entries.

My main doubts

I'm just starting to learn about DB/Programming, so bear with me for obvious mistakes.

1. Is this design solid from a programming/performance/feature point of view?
2. How to implement reporting? Querying the DB (derived table) or creating a new table (like journal_reports), and creating triggers to update account balances for each entry? (read in this question that is not such a great idea)
3. Anything that I might be missing?

Answer 1

I think your question is quite broad and hard to answer in its entirety. But here are some things I noticed about your design:

General Design

Some tables in your design violate the first normal form. A good example is contact_adress which has adress1, adress2 and adress3 as columns. Usually a place has only one street address and then one adress column is enough, but in case you really want to have the ability to add multiple street adresses to one place, you should move those adresses to another table (contact_adress, street_adress). The same is true for journal_bills (detail1, detail2) and the accounts table: Instead of parent_imediate, parent_second and parent_third a single parent attribute should be enough. To get the second or the third parent you can use recursive CTEs instead.

I don't know all your business requirements, but you should check if your design allows you to enter nonsensical data: Can an account be both cash and credit? If you have currencies, what are the exchange rates? Can a zip code start with one or more zeros? What about the phone numbers? For some things there are best practices, e.g. how to deal with recurring events.

Naming

Some column names are difficult to understand (ag, acc, pmt), which can lead to maintainability problems if someone else has to work with your database. Use a style guide if you're not sure how to name things. Generally it is a good idea to stick to a consitent naming scheme, e.g. give every table a plural name.

How to implement reporting?

I would stick to simple SQL queries and try to stay away from sophisticated functions like triggers unless you really need to, which is probably never the case for a simple application.