Cash Transaction Table

[The Little Guy]

I have been thinking of a game, and I was wondering if I were to have a bank, what would be the best way to store players in/out transactions. what kind of table structure would you do for something like that?

 

Lets say I were to transfer money to you, how would that transaction look?

 

- Would I insert 2 records into the table, one for me and one for you?

- Would I insert one record into the table and me and you share that record?

- Would you do something different, What?

[jtm62]

I have been thinking of a game, and I was wondering if I were to have a bank, what would be the best way to store players in/out transactions. what kind of table structure would you do for something like that?

 

Lets say I were to transfer money to you, how would that transaction look?

 

- Would I insert 2 records into the table, one for me and one for you?

- Would I insert one record into the table and me and you share that record?

- Would you do something different, What?

 

I would have two tables. Account_Balances (account_ID, account_Balance, timestamp) && Transaction_Records (payer, payee, amount, timestamp)

 

Then I would have three records... two updates and one insert all wrapped in a transaction to maintain the acid transaction.

 

"update Account_Balances set account_Balance=account_Balance-{$amount} where account_ID='{$transferee}';";

"update Account_Balances set account_Balance=account_Balance+{$amount} where account_ID='{$receiver}';";

"insert into Transaction_Records (payer, payee, amount) values ('{$transferee}','{$receiver}',{$amount});";

 

This assumes both accounts are at the same bank, of course.  And this design is super simple meant to address your base question... I'm sure with more time and energy I could brainstorm a whole bank, but I don't have the time or the energy... 

[PFMaBiSmAd]

- Would I insert 2 records into the table, one for me and one for you?

 

Yes. It would be a debit (-) amount for the source account and a deposit (+) amount for the destination account.

 

@jtm62, having a second table that duplicates the balance from the account table is redundant information and is not necessary (do you keep track of your checking account by keeping track of the deposits/debits in one place and keeping track of the balance in another place?)

[jtm62]

- Would I insert 2 records into the table, one for me and one for you?

 

Yes. It would be debit (-) amount for the source account and a deposit (+) amount for the destination account.

 

@jtm62, having a second table that duplicates the balance from the account table is redundant information and is not necessary (do you keep track of your checking account by keeping track of the deposits/debits in one place and keeping track of the balance in another place?)

 

I'm not duplicating anything.  I'm recording the amount, the payee, and the payer in the second table.  Personally, I want some way to know who paid who, and who received what from who... and I want a separate table to do that.  I don't feel it appropriate to record the second account id in my account balance table when making a transaction.

[PFMaBiSmAd]

There would not be a separate my account table for each person. That would indicate a bad database design.

[jtm62]

There would not be a separate my account table for each person. That would indicate a bad database design.

 

fair enough.

[The Little Guy]

So if I did that, wouldn't I need a "to member" and a "from member" column? If so, wouldn't it make it easier to do just one insert and both members share that insert?