I was wondering how to handle money in a SQL database to maintain consistency. I presume the tables would look something like below.
Consider the following scenario: if two players are playing then the money they wagered has been deducted from both of their balance_usd
and added to pot_usd
. But suppose the server goes down in the middle of the hand. Consistency of the database here presumably would mean that the money in pot_usd
should be returned to the two players balance_usd
effectively canceling the hand. It doesn't seem that transactions alone can solve this problem. How should the backend of a poker server be architected to handle this?
table user
user_id
balance_usd
table game
game_id
pot_usd
...
To cope with that eventuality, you could have a table of CurrentGameStakes
with fields for the playerID and the amount staked. Then when the hand is finished, you transfer the total of the stakes to the winner, and if a game doesn't complete for some reason, you return the staked amount to each player.