2

I'm trying to code a dice game where users log in and then challenge each other. I already made a version of this game without login but only one game could be played at a time.

So now, there will be the possibility of several games going on simultaneously between users.

In the non-login version I needed 4 tables for each game, one for the players' names, one for each player's previous scores, and one to store their current throws. They auto updated and cleared as the game progressed.

My idea for the login version is to give each game an id so the first game which is initialized will be game1, the second, game2 etc. and for each game generate the same 4 tables. In each game they will have the same names (player1_subtotal, player2_subtotal for example) but in game 1, each table will have a game_id which identifies it as belonging to game 1, and likewise for game 2 etc.

Just to reiterate, this would mean that there would be several tables with the same name, but each with a different game_id. And there would be several tables with different names, but tied together via the same game_id.

Is this possible or am I going about this in the wrong way? I'm only using PHP and MySQL for this and I haven't learned any other languages apart from Javascript and a little bit of Java.

Thanks

Improve this question
1
  • You are supposed to do the data filtering on row level, not on table level. Doing what you suggest is a big no-no.
    – Andy
    Oct 20, 2016 at 9:48

2 Answers 2

Reset to default
1

To my mind it sounds as though the data model is incorrect. It's rarely a good idea to create temporary tables (IMO).

You should be able to set things up so that you have something like the following tables:

  • Players with Player_ID as the pk and anything specific to the player e.g. e-mail address.
  • Games with Game_ID as the pk and anything specific to the game e.g. date played.
  • Game_Players with Game_Id and Player_ID as a unique key (There would be two rows per completed game, one for each player) and the Players' total score for that game etc.
  • Current_Games with Game_ID Player_ID and Turn_No as a Unique key and information about the players' turn i.e. score

Then to start a game you generate a new Game_ID probably by inserting one row into Games (assuming you've used an autoincrementing field for the Game_ID - hint, you should), Two rows into Game_Players and then keep putting the turns into the Current_Games table. If you keep filtering on Game_ID the table only contains the rows pertaining to the current game. Then once the game is complete, you update Game_Players with the final scores and delete from Current_games where game_ID = ... to clear the current game - if that proves necessary. You may find it desirable to keep a full record of each game and only purge old games when space becomes an issue.

Improve this answer
1
  • I suspect this is precisely the solution I was looking for. I will try it out. Thanks a lot!
    – user122374
    Oct 20, 2016 at 9:55
0

Not sure I quite follow, but are you suggesting creating a new set of tables for each new game? If so this is probably a bad idea for a number of reasons, e.g.

  • a table has a minimum amount of space it uses
  • tables take time to build
  • your database will probably get slow as more tables are built and run out of space

The way I'd design this is use a game id as a key for the game and each table will have a number of rows, you then select the rows you need

e.g.

Player[player_id, player_name, player details...]

Game[game_id, player_1, player_2, results...]

Improve this answer
2
  • Yes, my idea is to create a new set of tables for each game. I can't think of another way. I wouldn't know beforehand how many rows I need as it depends on the user's throws. The player throws the die, and can keep the score or roll again and if he rolls the same or higher, the scores add until he ends turn. If they roll lower they get 0. Then it's the other player's turn etc. The winner is the first to reach 50. Could I store all these throws within player 1's table? Player[player_id, player_name, player_throws...] I would have one player_name for multiple player_throws...
    – user122374
    Oct 20, 2016 at 9:39
  • Maybe add a GameTurn table like GameTurn[game_id, player_id, turn_id, dice_result] so you'd have a game table which gives you the players in the game, then a GameTurn table which has the list of dice throws and the value for each player in each game, or maybe just the running total.
    – daven11
    Oct 20, 2016 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.