Advice on DB structure for representing financial transactions

I would like to float a situation to see what other people think would be the best way to represent the data. I am trying to find a balance between making data easy to access and not repeating information. To me, the idea below seems like the best option, but perhaps someone with more experience in this sort of data can suggest something I haven’t considered.

I am representing ‘financial’ transactions.
There are 2 types of user accounts: Givers & Receivers.
Givers can load money to their personal account.
Givers can then distribute that money to Receivers.
Collected money is then sent to a Receiver at some later point.

External_transaction:
Contains details relating to payments to/from the website:
Giver’s loading money to their account.
Money sent to receiver’s account.
References the internal transaction.

Internal transactions:
Contains all transactions concerning user accounts.
Examples:

[INDENT]txn_id:100
from: external
to: giver123
amount: 10.00
giver_balance: 10.00
receiver_balance: NULL

txn_id:101
from: giver123
to: receiver111
amount: 7.00
giver_balance: 3.00
reveiver_balance: 7.00

txn_id:103
from: receiver111
to: external
amount: 5.00
giver_balance: NULL
receiver_balance: 2.00
[/INDENT]

Any advice is welcome, I have not had any exposure to data models for this sort of thing. Thanks.

That statement makes me feel much better.

Yes, the examples given were simplified, dates and currencies (if permitted) will be included. I will attempt to store as little data as possible on our end. After all, transaction details can be retrieved from Paypal. And the transaction information from paypal varies quite a bit depending on the transaction type.

Now that I have implemented the database and I am using the sandbox to test transactions I am happy with the design. Thanks for the reassurance :slight_smile:

Can a Giver also be Receiver?
How is the money loaded to giver’s account? Where’s that information kept?
What’s the currency involved with the transactions?
Are there multiple currencies involved or is there a possibility of multiple currency requirement at some later point?
What datatype are you using for storing the amounts?

As long as you have audit trail, you’re fine.
You’re just lacking the time of the transaction (or you might have just skipped that field in the example).