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:
Salary from your employer: Credit
Salary
, DebitBank Account
- the money came from your salary, and went to your bank account.Pay of rent: Credit
Bank Account
, DebitRent
- 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 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.
- 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?