1

I'm knee deep in a multiplayer game and found out I'm doing some frowned-upon actions with MySQL (I'm a beginner with databasing and PHP).

I've got a table of users with all their pertinent information and a table for games with most of their relevant information. My problem is that I'm dynamically creating a table for each game containing a user id, their score, and other game-specific data. I know that's bad practice, so I'm looking to merge that with the single entry in my games table, but I can't figure out how to do it because of a few issues:

  • Each game can have 3-6 players, so I think I'd have to assume the max and just not fill the entries?
  • Players should be able to enter multiple games, so just having an "active_game" column in the users table wouldn't work

I've been looking for resources, but the best I could find is that it's also bad practice to include a "pseudo-array" in a column. Meaning if I did have an "active_games" column, it wouldn't be best practice to have the data be a CSV of all currently active game_ids.

Any help would be greatly appreciated!

user1982687
  • 223
  • 1
  • 2
  • 6
  • Table games, table players, table player_games. Use player games to map ID's together (1 row = 1 player / game combo). Don't use arrays, and definately do not generate a table for each game. – Twelfth Jul 21 '14 at 19:37

2 Answers2

2

You are in need of three tables:

  1. A table for your user related datasets.
  2. A table for your game related datasets.
  3. A table to assign the users to the games (Use IDs).

Reason:

  • You can set the max. amount of players for a game in the according dataset.
  • You are not in need of new tables for every game.
  • You can assign every player to every game.
  • Your database structure is normalized.
Community
  • 1
  • 1
chrisp
  • 569
  • 4
  • 24
  • Maybe there's something I'm missing, but how would I address the variable number of players? An entry per user per game? – user1982687 Jul 21 '14 at 20:45
  • You add one entry in the third table (see above) per user per game. Then you can easily check the current amount of players in a game with a `SELECT` statement. – chrisp Jul 21 '14 at 20:50
  • That's so simple! Thanks so much. Databasing is really new to me, so it's taking a while to really understand how to do it effectively. This will work elegantly, thanks so much! – user1982687 Jul 21 '14 at 21:09
1

Here is one way you could arrange the database in a simple way to hold this information:

Table 1:

PLAYERS(user_id)
Primary key: user_id

Table 2:

GAMES(game_id, game_type, etc.)
Primary key: game_id

Table 3:

PLAYER_GAME_DATA(user_id, game_id, score, active)
Primary key: user_id, game_id

So each player has an entry in PLAYERS, each game has an entry in GAMES, and you link the two with the PLAYER_GAME_DATA, where for each player in each game there is an entry. The user_id and game_id columns in PLAYER_GAME_DATA would be foreign keys pointing to the previous two tables.

You could have multiple entries in PLAYER_GAME_DATA for each player and each game and that's okay.

Hope this helps.

ktbird7
  • 56
  • 8
  • So for example, if I had a game with 3 players, I have 3 entries in the table, each containing the same game_id, but different user_ids? – user1982687 Jul 21 '14 at 20:50
  • @user1982687 Yes that is the idea. If you have the primary key of the third table set to user_id and game_id, then you can have one game with multiple players in the table, as well as one player with multiple games, but every user/game combination pair will be a unique key. Assuming that you can't have a player take up two player spots in the same game (that seems unlikely). – ktbird7 Jul 22 '14 at 18:19