One should think twice about building an accounting system from scratch, right? Nah, let's plow on ahead!
Forget floats, doubles, decimals etc - just don't. Represent all your numbers as integer "cents". Eg: for "normal" currencies like Dollars and Euros, multiply the received number by 100 and store it as an integer. For currencies without cents just store the integer. Build a list somewhere that has the cent-precision definitions for each currency and some helper methods for conversion. For the examples below I have not added currency to the ledger just for simplicity.
At the core of an accounting system is a double-entry ledger. It's going to look something like this:
CREATE TABLE `ledger` (
`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT,
`date` date NOT NULL,
`account_id` integer NOT NULL,
`amount_in` integer NOT NULL DEFAULT 0,
`amount_out` integer NOT NULL DEFAULT 0,
`details` varchar(512) DEFAULT NULL
);
When you make a "transaction" you are going to insert two records into this table:
amount_out of the value of money leaving an account.amount_in of the value of money going into an account.And wrap these two inserts in a database transaction! At any point in time you should be able to make this query:
SELECT SUM(amount_in) - SUM(amount_out) FROM ledger;
And this query must always be zero. If it is not zero you have an accounting error and you must go through the ledger and look for unbalanced entries. If you want to know the balance of any given account you do this query:
SELECT SUM(amount_in) - SUM(amount_out) FROM ledger WHERE account_id = X;
It is normal and expected that this query does not return zero.
This is actually pretty tricky to wrap your head around and explain. What exactly is accounting? First, lets look at a simple example of personal day-to-day finances. You will have these accounts set up in your database with these ids and meanings:
1: Bank account - An account to represent your bank account inside the accounting system
2: Food - An account to represent how much you spend on food
3: Misc - Everything else
Now, export some transactions from your bank as some parseable statement format. These transaction statements should have positive or negative amounts for each row, and also perhaps a starting and ending balance of your account. Loop through each one, and for each row insert two records in the ledger. Let's take a shopping trip as an example:
[date] | [amount] | [bal_before] | [bal_after] | [desc]
2022-01-01 | -10.00 | 543.25 | 533.25 | Supermarket Stuff
2022-01-01 | -20.00 | 533.25 | 513.25 | Movie tickets
The ledger gonna look like this:
[id] | [date] | [acc_id] | [amount_in] | [amount_out] | [desc]
1 | 2022-01-01 | 1 | 0 | 1000 | Supermarket Stuff
2 | 2022-01-01 | 2 | 1000 | 0 | Supermarket Stuff
3 | 2022-01-01 | 1 | 0 | 2000 | Movie tickets
4 | 2022-01-01 | 3 | 2000 | 0 | Movie tickets
So the SUM select above checks out because all the negatives balance all the positives. At the end of the day of 2022-01-01 your real bank balance is 513.25 but if you make this query:
SELECT SUM(amount_in) - SUM(amount_out) FROM ledger WHERE account_id = 1;
You gonna get -3000. The way you get around this is by inserting a starting transaction in your ledger, and probably a new account to represent that:
4: Equity - An account representing money that was already there before you started recording everything
And rebuild the ledger so it looks like this:
[id] | [date] | [acc_id] | [amount_in] | [amount_out] | [desc]
1 | 2021-12-31 | 4 | 0 | 54325 | Initial equity, beginning of history
2 | 2021-12-31 | 1 | 54325 | 0 | Initial equity, beginning of history
3 | 2022-01-01 | 1 | 0 | 1000 | Supermarket Stuff
4 | 2022-01-01 | 2 | 1000 | 0 | Supermarket Stuff
5 | 2022-01-01 | 1 | 0 | 2000 | Movie tickets
6 | 2022-01-01 | 3 | 2000 | 0 | Movie tickets
Now your query:
SELECT SUM(amount_in) - SUM(amount_out) FROM ledger WHERE account_id = 1;
Will return 51325, which is your real bank account balance. And this is the way you can check that you have classified every transaction. If the balances do not match you know that you have likely not created a ledger record to represent that transaction.
A misconception I had was that accounting some somehow controlling the money, but it isn't. What controls the money is your bank card and your shopping impulse. Accounting is about monitoring money, and classifying transactions into buckets that you can use to extract useful information.
Look at the "Misc" account: It has a final balance of 2000. So it has a final balance that is positive, but we spent money - what does this mean! It turns out that it is helpful to classify accounts as being "credit-normal" or "debit-normal". These terms are not used often, but rather: "asset", "liability", "income", "expense" and "equity". Imagine we set up in our little ledger an account called "wages". Each month we would have a wage coming in on our bank statement as a positive. If we model this in our ledger it would be an amount_in to our bank account, and an amount_out from our wages account. If we get the balance of the wages account it would always be negative. But clearly this is an "income" account, and is debit-normal. Whenever we get a balance from a debit-normal account we flip the subtraction order:
SELECT SUM(amount_out) - SUM(amount_in) FROM ledger WHERE account_id = X;
Here are the classifications for the account types:
asset credit-normal
expense credit-normal
liability debit-normal
income debit-normal
equity debit-normal
The terms credit-normal and debit-normal are about how you interpret the balance of an account. Let me explain each:
This is credit-normal because if it has a "positive balance" (SUM(amount_in) - SUM(amount_out)), we have valuable assets. But we must have paid for those assets when we purchased them, so there is an amount_out from our bank account and an amount_in into our asset account. If we buy a printer and we want to list this as an asset we will take 100.00 EUR from our bank account and put it in the assets account. We now have 100.00 of assets.
The same as the asset account. We classify expenses as credit-normal because they allow us to keep on living. It's just classified differently from assets because we can not go back to the supermarket, give the food back and expect our money in return. Food is not an asset.
Now it gets more interesting. Lets say someone loans us 200.00 EUR. That will look like an amount_in on our bank statement (they transferred money to us) so we make an amount_out from our liability account. If we get the balance of the liability account we should see a positive value of liability, not a negative value. Like saying "we owe someone 100.00 EUR" rather than the incorrect: "We owe someone -100.00" - you see? So we use the "flipped" query to get the balance: SUM(amount_out) - SUM(amount_in).
Income looks like liability, and it's actually very similar! Because the account is debit-normal we see a "positive" balance. But this represents time that we owe our employer, so it is actually like a liability. If we run a a query over some time-range and see 80000.00 EUR of income, this actually represents that we owe our employer that amount of time. We probably already did that time though, since wages are paid after the work is done. In your company wages are probably modeled as expenses, so as you do work their expense account is theoretically going "negative" until they pay you for that work.
Not sure about this one, except for our little example above about the starting balance of the bank account. It's kinda like money "magicked" from somewhere. If it were a loan it would be a liability.
You see that "negative" and "positive" values for balances actually has little meaning. What is important is the positive flow of money from one account to another. The direction of that flow determines if we are increasing or decreasing. If an account SUM's to a negative number, that negative must be interpreted to understand what it means.
Let's say you have a client that has a contract with you for a regular monthly payment. You would create a debit-normal account in your accounting system like "client 1". Then create a credit-normal account something like "monthly fees". On a cron job every month create a transaction from "monthly fees" into "client 1". When they pay a bill, create a transaction from the "client 1" into your bank account.
If they don't pay their fees a SUM(amount_out) - SUM(amount_in) (debit-normal) query will show an increasing negative balance (new amount_in values are being added automatically each month by the cron job). When they pay into your account you create a transaction from "client 1" into the bank account. This increases the amount_out, thus reducing their negative balance. If they are a good client their balance should be zero. If they have a positive balance then that is money you owe them (perhaps they overpaid). Money you owe someone is like a liability, hence why the "client 1" account is debit-normal.
If you are dealing with multiple currencies... Well, to be frank I pity you. I simply do not know enough about it except stuff gets really complicated really fast with multiple currencies. Bank accounts are only in one currency (as far as I know) so you will likely be looking at different ledgers for different bank accounts. At some point you may choose to transfer some money from one account to another, and at that point there will be a currency exchange according to some exchange rate. Big compaanies may have multiple bank accounts in different countries, and may get paid in for a purchase in one currency, but then need to pay a supplier in another currency. They may choose to simply take the money in to one bank account and withdraw some money from the other account, and never actually transfer funds "internally" between those accounts. Until exchange rates are favorable. How they keep track of that internally I have no clue.