Data Storage - game results

[eluser]obiron2[/eluser]
Hi guys,

I wanted to guage opinion on how you would store the following information in a database.

I am building a league system where n players will play against each other in a season.

There is no Home/Away denomination so A vs B is the same game as B vs A.

I will need to add up all the points each player has scored and show them in a grid against the players they played, with the highest scoring player first.

E.g. 5 players A-E should produce a grid like this:

Code:

.    A     B      C      D     E

A    X     5      5      4     5

B    2     X      5      5     5

C    3     2      X      4     5

D    5     1      5      X     2

E    1     0      1      5     X

I will need to find all the results for player A and all the results against player A
I will need to sum the results for each player and order them by sum


I am thinking a table with GameID, PlayerID and PlayerScore but how would you go about populating it to ensure that each player ID is populated into 4 games and each game has 2 players (i.e. that all the valid combinations are covered) I would prefer to be able to do this as a bulk insert rather than active record as there could be a lot of them.

The other alternative is a game record with player1 and player2, but this could lead to a number of indexing issues.

Advice please

Obiron

[eluser]Michael Wales[/eluser]
I would create the following table and only insert completed games. There's no need to insert games that have yet to be completed (you can place the "X" on a FALSE result set).

games
-----
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
player1 INT NOT NULL
player2 INT NOT NULL
score INT NOT NULL

To get all of the games and put into a multidimensional array so we can make our table:

Code:

$game_results = array();

$query = $this->db->get('games');
if ($query->num_rows() > 0) {
  $row = $query->row();
  $game_results[$row->player1][$row->player2] = $row->score;
  // Do the inverse, since there is no home/away
  $game_results[$row->player2][$row->player1] = $row->score;
}

Now let's create our pretty little table (not sure the looping through the array is correct in this example (no environment to test in atm), but you get the concept):

Code:

<table>
&lt;?php foreach ($game_results as $p1 => $result): ?&gt;
  <tr>
  &lt;?php foreach ($result as $p2 => $score): ?&gt;
    <td>&lt;?php echo $score; ?&gt;</td>
  &lt;?php endforeach; ?&gt;
  </tr>
&lt;?php endforeach; ?&gt;

The only concern would be on insertion - make sure you don't have the same game listed somewhere twice.