Hello, all! I have several needs that I'm attempting to accomplish with my database implementation.
- List number of wins / losses for a particular user
- Keep track of the amount of coins a user has
- Allow user to purchase items from a shop
- List profitability from all gambling-based games
For my use case, I have several games the end user can play. The "Fun" category of games does not award 'coins'; however, I want users to be able to list how many they've won. All the gambling-based games allow players to earn / lose their 'coins' when playing.
-
Fun Games:
- Rock, Paper, Scissors
- Connect Four
- TicTacToe
-
Gambling Games
- Slot Machine (Slots)
- Black Jack
- HiLo
- Coin Flip
After doing some research online, I have implemented a 'match' table to keep track of all the matches between players. With two player game sessions, two rows will be created. If I want to know how many wins a player has, I can use a sum(wins) from the match table. Additionally, from the 'gambling' match table, I can run a similar query to pull the profitability loss / gain. Is there a better way of doing this?
Additionally, if a user profited from a gambling match – I'm assuming I'd need to perform two operations:
- Add new row in GamblingMatch
- Update amount of coins a player has
At this moment, I'm not sure of a way to do that in one operation.
Best Answer
Your queries are fine.
To combine multiple row insert/updates/deletes together, use transactions.