Categories
Database Development

MySQL Database Schema recommendations?

enter image description here

Hello, all! I have several needs that I’m attempting to accomplish with my database implementation.

  1. List number of wins / losses for a particular user
  2. Keep track of the amount of coins a user has
  3. Allow user to purchase items from a shop
  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *