Accounting Database - storing a transaction

Question

You make a gaming website where the user can buy gaming credits and the funds are deposited/credited into the user's virtual account to play some game etc...etc..

1

If you got an accountant to record the transaction, it would be recorded like this (maybe a bit more complex but you get the point)

TRANSACTION
PK_ID1 Cash      - $10 (System)
PK_ID2 Deposit        $10 (System)

TRANSACTION
PK_ID3 Bank Account      - $10 (John)
PK_ID4 Deposit        $10 (John)

2

As a developer, do you really need to waste 2 extra records? why not just record it like this…(then you might store information where the funds came from, status in other columns under the same deposit record)

TRANSACTION
PK_ID1 Cash      - $10 (system)
PK_ID2 Deposit        $10 (John)

Is there any real advantage of option #1 over option #2 and vice visa?

EDIT: modified question, removed CR, DR and replaced with a sign.

Answer 1

(Answering your question, but also responding some points raised in paxdiablo's answer.)

It is nothing to do with the accountant looking inside your database. With Double entry, errors are easy to trace; it is an Accounting and IRS requirement, so really, you do not have a choice, you need double entry for any system that deals with public funds.

• (Please do not try to tell me what "double entry" is; I have written double entry systems for banks, to Audit requirements.) Double entry is an accounting method, based on a set of accounts. Every financial transaction is Journal Entry; if all the transactions were re-applied from the beginning, all the accounts would at their exact same balance as they are today.

• Double Entry means every transaction has a "To" and a "From" account; money never leaves the system or enters the system. Every Credit has a Debit attached to it.

• Therefore (1) is not the "double entry" version of (2), they cannot be readily compared. The double entry version of John's transaction is (one financial transaction), in logical accounting terms:

  • From: JohnAccount To: SystemAccount Amount: 10.00 (dollars)

  • That may well be two rows in a table, one a credit and the other a debit, the two inserts wrapped in an SQL Transaction.

• That is it for the Accounting system, which is internal, and deals with money. We are done.

• But you are additionally marrying the accounting system to a purchase/sale system (without having explicitly declared it). Of course for the ten bucks you took from John, you need to give him whatever he purchased for it, and record that. John bought ten bucks worth of gaming credits, if you are tracking that, then yes, you also need:

  • From: SystemGamingAccount To: JohnGamingAccount Amount: 100 (credits)
    or,expressed in dollars:

  • From: SystemGamingAccount To: JohnGamingAccount Amount: 10.00 (dollars)

  • That, too, may well be two rows in a table, one a credit and the other a debit, the four inserts wrapped in an SQL Transaction.

• To be clear, if you were selling widgets instead of gaming credits, the second (widget tracking) transaction would be:

  • From: Warehouse To: PublicSale Amount: 1 (widgets)

  • and since you are tracking Units in the warehouse but not how many widgets John Q Public has in his pocket, that is two inserts plus one update (UPDATE Part SET QtInStock = QtyInStock - 1 WHERE PartCode = "Widget"), all wrapped in a SQL transaction.

And there IS an Account for each user, right. Virtual, esoteric or physical, it is a Legal Entity, against which transactions are made. So let's not pretend it does not exist because it is virtual. For gaming, one dollar Account plus one gaming (credit) Account.

Credit/Debit

I would put the CR/DB back in; not CHAR (2), but boolean. It will help you later when the table is large,

    WHERE IsCredit = 1  

is much faster than

    WHERE Amount >= 0.

Note that with ">=" you have to ensure that every code segment is coded the same way, not ">" sometimes. Boolean or char does not have that problem.

Answer 2

In terms of the data (which is what you're asking), no. You should store it as a signed value. Double-entry bookkeeping is not something the mob does so it can hide the real profits from the IRS :-)

It means transaction have to be balanced (value is never created or destroyed, just transformed). And it'll be a lot easier to balance transactions (and the books) if you just store them in one column with a sign.

In terms of visual presentation, some accountant may like them in separate columns but the vast majority will generate reports with the "negatives" simply indicated differently (such as surrounding them with parentheses).

It may well be that (like many other accounting things), the dual columns are carried forward from many moons ago. It would be easier to add up two columns then subtract the negative total from the positive total to get the current position (as opposed to adding and subtracting in a intermixed fashion). But that's supposition on my part.

See also here.