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.

enter image description here

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?

migrated from codereview.stackexchange.com Oct 20 at 3:04

This question came from our site for peer programmer code reviews.

  • Can't discuss performance without seeing the queries. And knowing how big the biggest table will be. – Rick James Oct 22 at 4:29
  • Thanks. Performance is probably my last concern at the moment. It shouldn’t be to big, maybe about 15-20k lines for the biggest table... I should query pretty much all tables, most of the queries should join 2 or more tables together, but I haven’t written any of the queries yet.. – Caio Carvalho Oct 22 at 4:48
  • Is there any reason that you have used pre- and postfixes like aux_ and _db in your table names? – taffer Nov 11 at 0:31
  • Just to improve organization. I usually use Sequel Pro, and it helps to have tables organized in some kind of logic. – Caio Carvalho Nov 11 at 17:44

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.

New contributor
taffer is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Thanks a lot for your inputs. For parts: I decided to split to be able to have street name, street number, apartment number and all, but the names make it confusing, good point. I'll try to improve the design to make it more detailed. As for business requirements, I'm making validations on code as some seem a bit too complex to achieve on the DB layer. Recursive CTEs seems like a great option, I'll study it a bit more to see if I can implement it. As for naming - you're right, I should have make it more readable for a third party to understand as well, I'll try to improve it. – Caio Carvalho Nov 13 at 1:02

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.