Designing a bank database schema with multiple balances per account 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.

Update

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.
Use ENGINE=InnoDB and code like this:

BEGIN TRANSACTION;
Update / insert into one account (one database table, that is)
Update / insert into another account
and any other actions that are tied to this 'transaction'
COMMIT;


In some situations, keep two tables for one account:


"Current" balance, etc
"History" of what happened -- This should only have INSERTs, no UPDATEs or DELETEs.  It is a blow-by-blow list of all the changes made to arrive at the "current" state that is shown in the other table.


So the "transaction" might include one UPDATE to the first table, and one INSERT into the other table.