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.
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:
Salary from your employee: Credit
Bank Account- the money came from your salary, and went to your bank account.
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
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.
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_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.
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.
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)
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.
- Is this design solid from a programming/performance/feature point of view?
- 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)
- Anything that I might be missing?