Not happy with my transaction reserval database design
Scenario, User withdraw money from the system (application/game, platform etc etc) to his bank account.
The bank declines the bank transfer therefore the withdraw transaction failed. Now the system needs to return the funds back to the user minus a fee for the declined/failed transaction.
To perform the withdraw, there is 4 transactions entry (4 records created), then to perform the reversal of the transaction, there is another 6 transaction entry. In total 10 transaction entries! (10 records created!)
I feel that there may be a better way to do this! maybe could just flag the records as reserved, and add logic to the software for the reserved transaction???
Am i doing it right?
how do you reserve a transaction in a double entry accounting database design?
EDIT: reserving a transaction, is not referring to a reseval of a database transaction but rather the reserve of funds.
I'm not sure this is a programming question. The issue here is the accounting requirements. In our system we would post a debit to the AR subsidiary ledger and then a credit/debit to the general ledger. If we then get a decline, bounced check, or whatever else, we post new transactions that reverse it. Without that there would be no record of what actually happened.
We do head off some of this with credit cards because we can settle those at post time. If a transaction fails to settle, the AR batch doesn't post and the clerk has to go fix it or delete the transaction. There's a posted flag on the AR sub-ledger transaction that must be flipped before it will impact the balance, and the GL is not adjusted until a successful post. If it does post successfully but then we get a chargeback, we still use the above method.
Really it comes down to what kind of records your comptroller wants to keep. They may want to keep track of the failed transactions for reporting and possible fraud detection. The general rule is once something is posted it should never changed.