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.

possible duplicate of [Accounting Database - storing credit and debit?](http://stackoverflow.com/questions/4074425/accounting-database-storing-credit-and-debit) - must be accounting week on SO :-) — paxdiablo, Nov 02 '10 at 03:43
@paxdiablo: Duplicate question, yes. Same user? Yep. This one was asked 90 minutes later. — NotMe, Dec 21 '10 at 19:39

PerformanceDBA · Accepted Answer · 2010-12-21T20:00:56.803

10

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

edited Dec 21 '10 at 20:00

answered Nov 03 '10 at 10:07

PerformanceDBA

32,198
10
64
90

1

@LittleTreeX littlegreen? Are you confused about the question ? May be good to ask a question, to clear your confusion, rather than ask others for an opinion. – PerformanceDBA Dec 21 '10 at 18:49
I apologize for my comment, and I have removed it. However, I think the answer could be much more simple. The 2 choices in the question are simply not equal transactions. @Paxdiablo mentioned (in his comment) that "The first is equivalent to cash:-10, bankaccount:-10, deposit:+20" and I'll add that the second is cash:-10, deposit:+10. Therefore, the answer is no, there is no advantage to use one over the other since the options are not equivalent to begin with. – LittleTreeX Dec 22 '10 at 03:13
2

@LittleTreeX. Thanks for the insights. If you put all that info into an Answer, and post it, someone might vote for it. You have freedom to express yourself as you wish, no restrictions to 512 chars. – PerformanceDBA Dec 22 '10 at 10:49
Why not use 2 columns (debitAmount, creditAmount) and set one to 0.0, would be easy to SUM over them. – Alex Dec 18 '19 at 07:11
@Alex. 1) there is nothing preventing SUM() over them right now. Do you need to see the code ? 2) Two columns would be un-normalised, it breaks Codd's 3NF: only one column would be fully dependent on the Key,; the other column would not be, it would be NULL (zero is a NULL substitute). 3) it implements a redundant column (the column that is always Null or zero). – PerformanceDBA Dec 18 '19 at 12:46
@PerformanceDBA yes code would be nice to look at, thank you. Even maybe a sample "bucket shop" so people could see the starting schema once and forever, would make world much better. By denormalising that transactions log I trade normal form for more convenient queries and less changes in views/materialised views when I add more tx types. – Alex Dec 18 '19 at 14:31
Or just make a TransactionType(PK, name, OP=enum(debit, credit)) to simplify that balance function so there will be tiny case statement in queries, imo that enum is more convenient than magic numbers from TransactionType.PK all over codebase. – Alex Dec 18 '19 at 15:02
I would clarify: double entry, despite its name, doesn't require *one* 'from' and *one* 'to' account, just that the values must balance. Classic example is buying $2000 worth of shares with $10 brokerage (and brokerage is treated as an expense rather than included in the base cost). Although $2010 is taken (CR) from your 'bank account' asset, only $2000 is added (DR) to your 'shares at cost' asset. The other $10 is an expense (DR). That would be a single transaction with *three* transaction parts. You *could* do two independent two-part transactions but that doesn't reflect reality. – paxdiablo Mar 07 '23 at 01:45
@paxdiablo It appears that ten years after the answers, you have learned a bit more, and added some detail to yours. Fine. Your comment here lacks basic accounting knowledge (terms and methods), and thus is quite mixed up: you even fail to differentiate Actual from Exposure value; between bank account and ledger account; you have "parts" to Transactions which are Atomic (no parts by definition). I suggest you delete it and ask a Question in order to obtain the correct terms and accounting methods. – PerformanceDBA Mar 08 '23 at 02:46
@PerformanceDBA: since it appears you're not a CA/CPA and both my wife and accountant are (and they agree with me), I'll decline the offer :-) I have no idea what you're talking about regarding actual/exposure value, if you could state how that applies to double entry, that may help out. In terms of "parts" of transactions, they *are* atomic, since a transaction is atomic. An entire transaction, including the parts (each hitting a single GL code), is a single transactional unit. I was just stating that your arbitrary limitation of two accounts is not required for double entry. – paxdiablo Mar 08 '23 at 05:31
I've tried to make it clear about the atomicity in a recent update, hopefully that will clear up the confusion. – paxdiablo Mar 08 '23 at 05:36
My original comment, by the way, was merely to do with your contention that "Double Entry means every transaction has a 'To' and a 'From' account", the rest of the answer seemed okay. That's the bit I suggested you change because it's not actually what double entry means. – paxdiablo Mar 08 '23 at 05:40
@paxdiablo 1) I accept and implement the Australian CPA definitions, I have only 35 years implementing DEA in Australian banks which have strict Audit requirements, I will refrain from argument with those who don't. There are some tutorials on the web. 2) the notion that the inner GL Transaction is atomic, **and** the outer "transaction" is also atomic is too hysterical to respond to, but you may have a private definition of *Transaction* that allows such things. I am quite sanguine with the CPA definition re Accounting, and the SQL ACID definition re database operations. – PerformanceDBA Mar 09 '23 at 06:37
@paxdiablo 3) Re *To and From account*. As per SO guidelines, I have answered the question within the scope of the question. Your comment proposes that one side of the DEA Transaction is a GL Account, and the other side could be a Bank Account, Credit Card, or similar, which is fine, but that is beyond the scope of this question, one that requires a more elaborate answer, which is why I said, **Ask a new question**. You have taken my statement as doctrinal, beyond the scope of the narrow question & answer, it isn't. – PerformanceDBA Mar 09 '23 at 06:59

paxdiablo · Answer 2 · 2023-03-08T05:36:10.893

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.

And just to be clear, an accountant would only do the two-step process using a bank account if the value itself actually passed through that bank account. If it's simply a transfer of value from one user to another, the bank account doesn't get involved.

Hence a transfer of value, user-to-user would be something like (splitting transaction parts from the transaction as a whole, to allow complex multi-account movements)⁽¹⁾:

Transactions:
    Txn#   Description
       1   Transfer from Alice to Bob
TransactionParts:
    Txn#   Account                Value
       1   Alice account (liab)      10
       1   Bob account   (liab)     -10

One where the value passed through your bank account could be:

Transactions:
    Txn#   Description
       1   Transfer from Alice via account
TransactionParts:
    Txn#   Account                Value
       1   Alice account (liab)      10
       1   Bank account (asset)     -10
       1   Bank account (asset)      10
       1   Bob account   (liab)     -10

That's keeping in mind the, to the laymen, weirdness of assets/liabilities having negative values when they're good for you :-)

⁽¹⁾ To avoid confusion (see comments), I should stress that the separation into parts is for the allowance for a transaction to easily hit a multitude of account codes (various assets, expenses, and so on). The transaction itself is still a single atomic operation.

Edited question, the question is, 1) store record as double entry like how an accountant likes it! or 2) store record as a single entry like how a developer likes it! :) — 001, Nov 02 '10 at 03:52
See my link. Unless your accountant is going to be looking _inside_ your database at the raw data, don't even _consider_ separating them :-) Databases are for storing data, not presentation information (ignoring the possibility that your data may actually _be_ presentation information, but that's not the case here). — paxdiablo, Nov 02 '10 at 03:56
Its just 1 table, option 1, uses a total of 4 records, whereas option 2 uses only 2 records. — 001, Nov 02 '10 at 03:59
Those aren't the same thing. The first is equivalent to cash:-10, bankaccount:-10, deposit:+20. And, unless you want the IRS on your tail, you need to document it as it happened. That means if John paid $20 into an account and Jill withdrew it, that's _not_ a John/Jill transaction, it's a John/Jill/Account transaction (yes, with four individual components). — paxdiablo, Nov 02 '10 at 04:02
No, option 1 and option 2 is not the same thing, accountants call this double entry and single entry. Option 1 (double entry), cash is deposited into the system by john, we see that it came from john's bank account, and then lastly it is credited into john's virtual account. Option 2 (single entry), cash is deposited into the system and then credited into john's virtual account. We do not know where the cash came from, or if its been settled we will record it under a detail column etc..but it will only use up TWO records not FOUR like in option 1. either options would work! — 001, Nov 02 '10 at 04:14

Accounting Database - storing a transaction

1

2

2 Answers2

Linked