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 employer: 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 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.


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?
  • 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.. Commented Oct 22, 2018 at 4:48

2 Answers 2


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.


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.

  • 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. Commented Nov 13, 2018 at 1:02

I would favor storing currency using BIGINT rather than DECIMAL(10,5).

The problem arises when extracting this data from the database, and performing arithmetic on floating point numbers. If you never plan to perform calculations outside of the DB, then NUMERIC is probably simpler.

For example, something that might surprise you, in javascript (and probably other languages):

0.1 + 0.2 = 0.30000000000000004 
1.1 + 0.2 = 0.3
0.2 + 0.3 = 0.5
0.2 + 0.4 = 0.6000000000000001

So when using ints, $1.00 would actually be stored as 100.

100 + 200 = 300 // equivalent to $1 + $2 = $3

This is an approach used, for example, by Stripe. See https://stripe.com/docs/currencies#zero-decimal. It removes the problem of handling floating point values.

  • Doesn't using decimal also remove "the problem of handling floating point values"? I'm pretty sure it does, so are there any other advantages of using integers?
    – mustaccio
    Commented Sep 19, 2023 at 18:26
  • @mustaccio if you have either a nodejs backend connecting to this database, or sending floats to a web (javascript) frontend, or anybody using your api throug JS, you will run into problems when performing arithmetics. Just open your browser developer console, and type 0.1 + 0.2 The result is 0.30000000000000004, which means that (0.1+0.2)-0.3 != 0. Lots of opportunities for shooting oneself in the foot right there. This problem entirely disappears when using integers. This is notoriously famous with NodeJS, but might also be true for other languages.
    – Ludovic C
    Commented Sep 20, 2023 at 9:18
  • And how is this a database problem?
    – mustaccio
    Commented Sep 20, 2023 at 12:09
  • OP mentions learning db/programming. Not (I assume) just sketching a theoretical DB schema, but part of something bigger. It's not a DB problem per se if you take it in isolation. But it's still something, in the real world, when building actual apps that use dbs and apis and stuff, that drive people crazy, it's a gotcha, and is worth mentioning, and is interesting, and should not be downvoted. If programs pulling these values from the DB must convert them to ints to avoid floating point arithmetic pain, might as well store it as an int. Would be simpler to use and also probably faster.
    – Ludovic C
    Commented Sep 21, 2023 at 10:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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