There's a common case when some transactions have opposite "refund" transactions that should not be included in the income/spending statistics.
Suggested implementation
Do not touch Transactions table at all, let users do whatever they want. Create a new RefundTransactions table that will simply map "original" transactions to "refund" ones. It allows to easily support complex DB querying, multiple refunds, indexing, and data normalization. The suggested structure is:
CREATE TABLE RefundTransactions (
original_tx_id INTEGER NOT NULL,
refund_tx_id INTEGER NOT NULL,
PRIMARY KEY (original_tx_id, refund_tx_id),
FOREIGN KEY (original_tx_id) REFERENCES Transactions(id) ON DELETE CASCADE,
FOREIGN KEY (refund_tx_id) REFERENCES Transactions(id) ON DELETE CASCADE
);
This structure also allows to not bother much when transactions are editing, or removed. Editing requires only checking for the txType change (more info below), and the deletion doesn't require any move at all because of ON DELETE CASCADE.
When implementing, make sure to cover these things:
Always check that the refund tx has the opposite txType to the original one. So if the original is income, then a refund can only be expense.
Make sure that the "refund" amount cannot be greater than the "original" tx amount. This also leads to the point 3.
When editing the amount or currency of the source transactions (original or refund), make sure that new amount is not greater than the one from the opposite transaction.
When editing the txType of the source transactions (original or refund), throw an error on the API level and ask the client to unlink refund transactions first. Consider adding an autoUnlinkRefunds flag to do it under the hood on the API side.
Make sure that it works correctly cross-account, when original tx is made within account_A, and refund withing account_B.
Side quests:
Ability to return statistics with and without refund info. But most-likely users don't need it
Motivation
There's a common case when some transactions have opposite "refund" transactions that should not be included in the income/spending statistics.
Suggested implementation
Do not touch
Transactionstable at all, let users do whatever they want. Create a newRefundTransactionstable that will simply map "original" transactions to "refund" ones. It allows to easily support complex DB querying, multiple refunds, indexing, and data normalization. The suggested structure is:This structure also allows to not bother much when transactions are editing, or removed. Editing requires only checking for the
txTypechange (more info below), and the deletion doesn't require any move at all because ofON DELETE CASCADE.When implementing, make sure to cover these things:
income, then a refund can only beexpense.amountorcurrencyof the source transactions (original or refund), make sure that new amount is not greater than the one from the opposite transaction.txTypeof the source transactions (original or refund), throw an error on the API level and ask the client to unlink refund transactions first. Consider adding anautoUnlinkRefundsflag to do it under the hood on the API side.Side quests: