6 ms·

The heart of double entry accounting is extremely simple. Forget about asset/liability/expense. Money always flows from one account to another. What goes out fr

The heart of double entry accounting is extremely simple. Forget about asset/liability/expense. Money always flows from one account to another. What goes out from account_1 must go into another account(s). Typical tables: accounts (id, name) transaction (id, date) /* some call it journal */ transaction_line (id, transaction_id[fk], account_id[fk], amount) I use -ve amount for credit, +ve for debit. That way when you do SUM(trascation_line.amount) it would come to 0. This also cleanly maps to database transaction too where all transaction_line rows and transaction row should be atomic. If you want multi-currency support, instead of amount column, it needs to be currency_code, currency_rate, amount_in_currency, amount_in_basecurrency ( i know we don't need this all, but sometimes you want to record the amount as entered, eg EURO 52.10 u want to record as entered even if your base currency is USD)

meekaaku 4y ago
To build a chart of accounts, you can have a parent column in accounts table. Account balances is just: SELECT account.name, SUM(amount) balance FROM account ac INNER JOIN transaction_line tl ON tl.account_id = ac.id GROUP BY account.name You can cache this balance values with a current_balance column on accounts table Once you have that, for any real world transaction, all you need to figure out is what are the accounts to debit/credit, ie classification. That is a higher level thing and is the business logic of an accounting application.
ruuda 4y ago
This looks nice, but it doesn't enforce in the schema that all transaction lines sum to zero. Is that a problem in practice? Or is it one of those things where if you get it wrong, you tend to notice immediately because everything breaks (as opposed to silently creating or destroying currency that goes unnoticed for a long time)?
- yobbo 4y ago
  It is bad practice in terms of software engineering, but some "real world" apps are implemented like this. They do verification in the "middleware".
- meekaaku 4y ago
  Its not a problem in practice. There might be a way to enforce that through database constraints, but in practice, checking transaction lines sums to 0 in business logic is not that hard. Having said that, you can run accounting entry sanity checks on the entire database. As previously said SELECT SUM(amount) /* this should sum to zero */ FROM transaction_line --- Also to identify any non-balancing transaction is easy: SELECT tx.id, tx.date, SUM(amount) tx_sum FROM transaction tx INNER JOIN transaction_line txl ON txl.transaction_id = tx.id GROUP BY tx.id HAVING tx_sum != 0 --- This will identify them even if caused by your business logic bug, database bug, disk corruption etc. This can also be done on the single accounting transaction just after insert too and can be done within the same database transaction.
- perlgeek 4y ago
  I guess you could have a stored procedure that checks for balanced transactions before inserting (or before committing, at least), with SECURITY DEFINER, and not give anybody else permissions to insert or modify the transaction lines table. But yes, that is a downside, and if I were to write such a thing I'd make sure to have at least two mechanisms to avoid / detect errors (like, one validation in business logic and/or stored procedure, plus regular monitoring for transactions that don't add up to zero).
yobbo 4y ago
This sort of schema requires a process to verify the sum before committing, and verification is annoying to achieve with constraints. Instead, you might consider something like: transaction_line (id, transaction_id[fk], dr_account_id[fk], cr_account_id[fk], amount)
- meekaaku 4y ago
  The problem with this will be when you have 3 accounts involved on a transaction. Eg, you take a sales receipt with part bank transfer, part cash. Sales Cr $100 Cash Dr $30 Bank Dr $70 Your approach will have: Sales Cr $70, Bank Dr $70 Sales Cr $30, Cash $30 That looks like two sales, which is not really the case.
  - yobbo 4y ago
    There exists cases where it is problematic, but your example is fine. In this case, you would use individual transaction lines to represent payments, rather than sales, which is closer to reality. But you can group transactions in any way you want. With sales tax, you would have: cr: sales, dr: cash, $100 cr: cash, dr: tax, $10
    - meekaaku 4y ago
      The above looks like $70 was taken from cash and deposited to bank. Thats not whats happening in the real world. Since cash is fungible, one could ignore that. But if it was cheque + bank-transfer or something else that leaves a record, then that wont work. Or when you include VAT/GST/Sales tax. Or when you pay a loan monthly payment of $1000 that needs to be split to principal and interest. etc.
      - yobbo 4y ago
        Yes, it is best if transactions correspond to verifiable events. Complicated situations can be modelled by letting events be represented by their own "accounts", in a separate charter. In your example, you might let the monthly payment be represented by its own account, with three transactions (bank payment, interest, principal). The purpose is to make the database constraints enforce double entry verification. If you don't need this, it can be made simpler as you suggest.
mmckelvy 4y ago
What do "-ve" and "+ve" denote?
- meekaaku 4y ago
  negative and positive for credit and debit respectively. Eg: an entry of -$100 is a credit entry.
  - mmckelvy 4y ago
    Ah, ok. So the "ve" was just a placeholder for the value in the "amount" column for each entry. How does this handle the fact that credits increase the value of some accounts (e.g. sales, equity) but decrease the value for others (e.g. assets)?
    - meekaaku 4y ago
      -ve i used to denote credit, not necessarily to indicate increase/decrease. In accounting terms, if you sales increase, your entries on the credit side of sales account increase. In my implementation it means larger negative number. Hope that explains.

meekaaku 4y ago

To build a chart of accounts, you can have a parent column in accounts table. Account balances is just: SELECT account.name, SUM(amount) balance FROM account ac INNER JOIN transaction_line tl ON tl.account_id = ac.id GROUP BY account.name You can cache this balance values with a current_balance column on accounts table Once you have that, for any real world transaction, all you need to figure out is what are the accounts to debit/credit, ie classification. That is a higher level thing and is the business logic of an accounting application.

ruuda 4y ago

This looks nice, but it doesn't enforce in the schema that all transaction lines sum to zero. Is that a problem in practice? Or is it one of those things where if you get it wrong, you tend to notice immediately because everything breaks (as opposed to silently creating or destroying currency that goes unnoticed for a long time)?

yobbo 4y ago
It is bad practice in terms of software engineering, but some "real world" apps are implemented like this. They do verification in the "middleware".
meekaaku 4y ago
Its not a problem in practice. There might be a way to enforce that through database constraints, but in practice, checking transaction lines sums to 0 in business logic is not that hard. Having said that, you can run accounting entry sanity checks on the entire database. As previously said SELECT SUM(amount) /* this should sum to zero */ FROM transaction_line --- Also to identify any non-balancing transaction is easy: SELECT tx.id, tx.date, SUM(amount) tx_sum FROM transaction tx INNER JOIN transaction_line txl ON txl.transaction_id = tx.id GROUP BY tx.id HAVING tx_sum != 0 --- This will identify them even if caused by your business logic bug, database bug, disk corruption etc. This can also be done on the single accounting transaction just after insert too and can be done within the same database transaction.
perlgeek 4y ago
I guess you could have a stored procedure that checks for balanced transactions before inserting (or before committing, at least), with SECURITY DEFINER, and not give anybody else permissions to insert or modify the transaction lines table. But yes, that is a downside, and if I were to write such a thing I'd make sure to have at least two mechanisms to avoid / detect errors (like, one validation in business logic and/or stored procedure, plus regular monitoring for transactions that don't add up to zero).

yobbo 4y ago

This sort of schema requires a process to verify the sum before committing, and verification is annoying to achieve with constraints. Instead, you might consider something like: transaction_line (id, transaction_id[fk], dr_account_id[fk], cr_account_id[fk], amount)

meekaaku 4y ago
The problem with this will be when you have 3 accounts involved on a transaction. Eg, you take a sales receipt with part bank transfer, part cash. Sales Cr $100 Cash Dr $30 Bank Dr $70 Your approach will have: Sales Cr $70, Bank Dr $70 Sales Cr $30, Cash $30 That looks like two sales, which is not really the case.
- yobbo 4y ago
  There exists cases where it is problematic, but your example is fine. In this case, you would use individual transaction lines to represent payments, rather than sales, which is closer to reality. But you can group transactions in any way you want. With sales tax, you would have: cr: sales, dr: cash, $100 cr: cash, dr: tax, $10
  - meekaaku 4y ago
    The above looks like $70 was taken from cash and deposited to bank. Thats not whats happening in the real world. Since cash is fungible, one could ignore that. But if it was cheque + bank-transfer or something else that leaves a record, then that wont work. Or when you include VAT/GST/Sales tax. Or when you pay a loan monthly payment of $1000 that needs to be split to principal and interest. etc.
    - yobbo 4y ago
      Yes, it is best if transactions correspond to verifiable events. Complicated situations can be modelled by letting events be represented by their own "accounts", in a separate charter. In your example, you might let the monthly payment be represented by its own account, with three transactions (bank payment, interest, principal). The purpose is to make the database constraints enforce double entry verification. If you don't need this, it can be made simpler as you suggest.

mmckelvy 4y ago

What do "-ve" and "+ve" denote?

meekaaku 4y ago
negative and positive for credit and debit respectively. Eg: an entry of -$100 is a credit entry.
- mmckelvy 4y ago
  Ah, ok. So the "ve" was just a placeholder for the value in the "amount" column for each entry. How does this handle the fact that credits increase the value of some accounts (e.g. sales, equity) but decrease the value for others (e.g. assets)?
  - meekaaku 4y ago
    -ve i used to denote credit, not necessarily to indicate increase/decrease. In accounting terms, if you sales increase, your entries on the credit side of sales account increase. In my implementation it means larger negative number. Hope that explains.