4 ms·

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

by 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).

by ruuda 4y ago

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).