StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>My suggestion is that you don't keep a list of the players for each game in the same table, but rather implement a relationship between a <code>games</code> table and a <code>players</code> table.</p> <p>The new model could look like:</p> <p>TABLE Games:</p> <pre><code>id type of game timestamp 1 poker 2011-10-08 08:00:00 2 fencing 2011-10-08 08:05:00 3 tennis 2011-10-08 08:10:00 4 football 2011-10-08 08:15:00 </code></pre> <p>TABLE Players:</p> <pre><code>id name 1 a 2 b 3 c .. .. </code></pre> <p>TABLE PlayersInGame:</p> <pre><code>id idGame idPlayer current 1 1 1 true //Player a is currently playing poker </code></pre> <p>When a player starts a game, add it to the <code>PlayersInGame</code> table.</p> <p>When a player exits a game, set the <code>current</code> status to <code>false</code>.</p> <p>To retrieve the number of games played by a player, query the <code>PlayersInGame</code> table.</p> <pre><code>SELECT COUNT FROM PlayersInGame WHERE idPlayer=1 </code></pre> <p>For faster processing you need to de-normalize(not actually denormalization, but i don't know what else to call it) the table and keep track of the number of games for each player in the <code>Players</code> table. This would increase the table size but provide better speed.</p> <p>So insert column games played in Players and query after that:</p> <pre><code>SELECT * FROM Players ORDER BY games_played DESC LIMIT 10 </code></pre> <p>EDIT:</p> <p>As Ilmari Karonen pointed out, to gain speed from this you must create an <code>INDEX</code> for the column <code>games_played</code>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload