I'm trying to build a schema to keep a record of the transactions made on some accounts, but those accounts are not "user accounts", instead there are orders that are being paid, and money is being transfered by the buyer to a middle man, and there to the seller, so there are two distinct balances to track.
The problem I have is that I have different kind of transactions to track, as the price of the product can change (affecting the balance for both parties), the buyer can pay part of the product (affecting just the first balance), or the buyer can pay directly to the seller (affecting both balances). There are more events to track (like canceling the account, changing the price of the product to 0 or near 0, etc).
I've been reading Writing a simple bank schema: How should I keep my balances in sync with their transaction history? but I'm not sure how does it could apply in this context.
I would like to have a design that provides a transaction history easy to read while being flexible to keep track of different kind of transactions.
I will try to provide more data about the entity types:
The main object is a Reservation, that have one buyer and one seller and a price (the buyer have to pay that price, the seller should be payed). It have two different balances, one for each.
There are different type of Transactions that affect the balances in different ways:
- a Buyer can deposit money to pay part of the reservation,
- money can be sent to the Seller,
- the Buyer can send money directly to the Seller, and
- money can be transfered from one Reservation to other Reservation.
What I'm not sure about, is what is a better approach:
Store a complex object on the database, that explicit, the kind of transaction, how much money was added/subtracted from which Reservation, if affect buyer and seller, or which one, etc.
Store multiple simple transactions that just add/subtract from each account. For example, if the buyer send money directly to the seller I would add two transactions, one for each.