[ruuda]

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

[perlgeek]

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]

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]

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.