[yobbo]

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]

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.

[gen220]

I think you could solve this with a `sale_id` for grouping transactions together, no?

The benefit of row-level guarantees on balancing is considerable, even if it somewhat complicates the application layer (the layer that translates your first ledger into your second ledger, and vice versa).

If you're storing a transaction that needs to balance across N rows, the only solution is at the "application level" (i.e. a stored procedure, or some middleware in code), which is unfortunately more difficult to reason about.

[yobbo]

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]

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]

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.

[HWR_14]

Isn't it more accurate to say it was two sales than one? In what way is it misleading?

[yobbo]

Distinguishing between sales and payments is required, but any confusion is basically impossible in online systems such as this schema would be used in, in real examples.

Typical accounting GUIs present transactions and ledgers such that it becomes intuitive for business users to enter sales/invoices. Entering the above transaction in those GUIs might cause confusion.

The schema here is used by an online system recording business events as a type of log of user interactions. It lives in a sql database, and thus needs to rely on row constraints to guarantee double-entry. Transactions are never entered manually or randomly.

The effect of split transactions can be achieved by using an intermediate account. Those accounts have balances which are not guaranteed to be zero, but that is unavoidable and sometimes OK and intended.

[lelanthran]

One sale is always one sale, even if it has multiple transactions associated with it[1]. It must never look like multiple sales.

[1] Lots of use-cases: People paying partly by credit card, partly in cash. People using vouchers which are recorded as a negative transaction. Group of people splitting a bill and each doing a CC transaction for their bit. None of these are multiple sales, because recording each transaction as a single sale wreaks havoc on the inventory control.

[meekaaku]

No. In the real world it is a single sale. In the accounting/finance domain it is single sale, so it should be represented as such. It also matters when you look at KPI like avg order size, num orders for customer, not to mention it is one invoice at the time of invoice creation, but payment collected in multiple methods.