Database advice for counting wins/losses between players

Question

What is the best way to keep track of the scores between all the players from a database ? How many times a player won/lost against another.

I thought about a duel table where I would have 3 colums: duel_id, winner_id and loser_id but I believe there is a better way to do this, avoiding redundancies.

Edit: I'm very sorry for not being clear enough. The players are in fact picked randomly from a user table. I need to know who won/lost against whom. And the reason why I don't really like my solution is because when doing statistics, the whole duel table would have to be scanned in order to return the amount of wins/losses between two players.

I do not see how an association array (this is what your duel table is essentially) can be redundant. But I would say the "best" is not a universal term, it depends on your requirements. — akonsu, Jun 16 '13 at 02:06

darijan · Accepted Answer · 2013-06-16T02:01:42.227

1

Table with its own id, two foreign keys - player1_id, player2_id, and two columns for wins wins_player1, wins_player2.

--------------------------------
| id | p1_id | p2_id | w1 | w2 |
--------------------------------
| 1  |   1   |   2   |  2 |  0 |
--------------------------------
| 2  |   1   |   3   |  1 |  1 |
--------------------------------
| ...|       |       |    |    |
--------------------------------

This way, when two players play a game you don't insert a new tuple to the table, you just update their scores.

For an example, if players 1 and 2 played, and the player 1 won you would do:

UPDATE scores WHERE p1_id = 1 AND p2_id = 2 SET w1 = w1 + 1;

edited Jun 16 '13 at 02:01

answered Jun 16 '13 at 01:56

darijan

9,725
25
38

That's pretty interesting, would you also be able to use the `ON DUPLICATE` with this method would be a straight query to do it all ? – Prix Jun 16 '13 at 02:16
I believe you would be able to do it. Not sure if `ON DUPLICATE` works on foreign keys, though – darijan Jun 16 '13 at 02:22
I actually considered this solution as well. This would require checking after each game if the tuple exists for both players and if the winner's id is `p1_id` or `p2_id`. I'm going to think about it! – halpsb Jun 16 '13 at 02:29
Well, to avoid checking, you could consider a rule of a thumb: let p1_id always hold the smaller player id. – darijan Jun 16 '13 at 02:33

score 0 · Answer 2 · answered Jun 16 '13 at 01:54

If you don´t mind not knowing who dueled with whom, you can put a total_wins column in your user data table, and increment it for each user after each match.

It has the advantage of not taking too much space.

But if you have a pretty big applications your data base could lock up with so many querys and writes

Database advice for counting wins/losses between players

2 Answers2