Would it be better to have the info in both tables? Or create a third table?

Question

I have one table GAMES and another PLAYERS. Currently each "game" has a column for players_in_game but I have nothing reciprocating in the PLAYERS table. Since this column is an array (Comma separated list of the player's ID #s) I'm thinking that it would probably be better to have each player's record also contain a list of the games they are a member of. On the other hand, duplicating the information in two separate tables might actually require more DB calls.

For perspective, there aren't likely to be more then a dozen players in a game (generally 4-6 is the norm) but there could potentially be a large number of games.

Is there a good way to figure out which would be more efficient? Thanks.

Answer 1

Normalization is generally a good thing. Comma delimited lists in tables is a sign that a table is in desperate need of a foreign key. If you're worried about extra queries, check out JOINING

dbo.games
+----+----------+
| id |   name   |
+----+----------+
| 1  |    war   |
| 2  | invaders |
+----+----------+

dbo.players
+----+----------+---------+
| id |   name   | game_id |
+----+----------+---------+
| 1  |   john   |    1    |
| 2  |   mike   |    1    |
+----+----------+---------+

SELECT games.name, count(players.id) as total_players FROM games INNER JOIN players ON games.id = players.game_id GROUP BY games.name;

Result:
+-----------+--------------+
| name      |total_players |
+-----------+--------------+
| war       |       2      |
| invaders  |       0      |
+-----------+--------------+

Sidenote: Go Hokies :)

Answer 2

The right answer is a table called PlayersInGames that has a player id and a game id per row.

Answer 3

I would create a third table that links the players and games. Your comma-delimited list is effectively a third table, but parsing your list is almost certainly going to be less efficient than letting the database do it for you.

Answer 4

Ask yourself what happens if you remove a row from the GAME table. Now you'll have to loop over all the PLAYER rows, parse the list, figure out which ones contain a reference to the removed GAME, and then update all the lists.

Bad design. Let SQL do what it was born for. The query will be fast enough if you index it properly. Micro-optimizations like this are the wrong approach.

Answer 5

Oh god, please don't use CSVs!! I know it's tempting when you're new to SQL, but it becomes unqueryable...

You need 3 tables: games, players, and players_in_games. games and players should each have a primary auto-incrementing key like id, and then players_in_games needs just two fields, player_id and game_id. This is called a "many to many" relationship. A player can play many games, and a game can have many players.