Double Entry Accounting Schema Design

database-design

We are implementing a software that allows to send "virtual" money between user internally. We have started our database with a single entry as follow (simplified table). We only one record one transaction, where the user initiated the transaction (send money) will be recorded. So it will always be a debit. 

TransactionLog
------------------
TransactionLogID
UserIDFrom
UserIDTo
CurrencyID
CurrencyAmount 
Remarks
TransactionDateTime

Then a requirement changes that say we need a double entry accounting schema in our database. Then i have make a new table as follow.

TransactionLogDoubleEntry
----------------------------
TransactionLogDoubleEntryID
TransactionLogID
UserID
CounterPartyUserID
CreditOrDebit
CurrencyID
CurrencyAmount
Remarks
TransactionDateTime

My question:
1) Am i doing this in a correct direction that i add TransactionLogDoubleEntry table with credit/debit it means it already become a double entry accounting schema?

2) Should i remain the table TransactionLog? Or using TransactionLogDoubleEntry is sufficient enough?

3) If i were to remain TransactionLog table, is it necessary that i duplicate the currencyID, currencyAmount, remarks, TransactionDateTime in TransactionLogDoubleEntry? Or is that enough to get the corresponding value from TransactionLog table.

Best Answer

Most double entry accounting systems would implement this using 2 tables. One table that is the "header" table, which stores common fields & a second "lines" table that stores the actual detail of the transaction.

In case you didn't know, in double entry accounting, the sum of all transaction lines in a given transaction always adds up to zero.

Anyway, based upon your current design, I'd do something along the lines of:

Transaction
------------------
TransactionID
CurrencyID
Remarks
TransactionDateTime


TransactionLines
----------------
TransactionID
LineNo
UserID
DebitAmount
CreditAmount

To transfer $5.00 from UserID 100 to UserID 250, you'd have the following entries:

Transaction: 

10000, 'USD', 'Test transaction', '2019/09/15 12:34:56'

TransactionLines: 

10000, 1, 100, 500, 0
10000, 2, 250, 0, 500

You asked about a case where there might be multiple transaction lines:- To transfer $9.00 from UserID 100, with $8.50 going to UserID 123 & $0.50 going to UserID 456, you'd have the following entries:

Transaction: 

10001, 'USD', 'Test transaction', '2019/09/15 12:34:56'

TransactionLines: 

10001, 1, 100,   900,   0
10001, 2, 123,   0,     850
10001, 3, 456,   0,     50

It doesn't matter from a mathematical/accounting point of view what order the lines are in, as long at the total is always net 0.

Proper accounting systems would have a G/L Account column in each line, which would dictate where the transaction posted to, along with the posting period and other essential information at the header level.

I'd seriously consider learning some accounting fundamentals if the system you're developing is dealing with real money.

This is obviously at a high level. If you require further help, please just ask.

Related Question