2

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.

eloyesp
  • 121
  • 1
  • 4
  • parent account w/ child accounts for the secondary balances? Try writing it out on paper first and then the solution may become clearer. You may have two issues. One is a presentation layer that represents the "fictional" accounts while the true transaction system tracks actual balances. In that case, meta data (tags, etc.) about each transaction entry could solve the presentation issue. – Jonathan Fite Sep 06 '17 at 15:19
  • 1
    Sounds like you want an accounts payable/accounts receivable system of some kind. With the seller being the payable and the buyer being the receivable. And that isn't something that is easy to answer in a question on a stack exchange site as it gets complicated very fast. – Joe W Sep 06 '17 at 17:18
  • @JonathanFite So, the reservation would have two linked accounts, and the transactions will only affect one account, am I understanding it right? – eloyesp Sep 06 '17 at 18:49
  • @JoeW yes, the systems looks like that, have you any ideas how to model that? – eloyesp Sep 06 '17 at 18:53

1 Answers1

0

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.

Rick James
  • 78,038
  • 5
  • 47
  • 113