Rock-Paper-Scissors-Spock-Lizard statistics DB design, is there a better way to achieve this? I am working in a player versus computer web application which is a version of "Rock-Paper-Scissors-Spock-Lizard".

I should be able to provide some statistics like for example:


How many times the user has won, lost and draw?
How many times the computer has won, lost and draw?
How many times the user using the player Lizard has won, lost and draw?
How many times the computer using the player Lizard has won, lost and draw?


And basically the same goes to each player type. 

I have made this small DB containing only two tables but I am not sure of it because I need to make a lot of COUNT queries to obtain what I am looking for.



Posibles values for result are:


0: user won
1: computer won
2: draw


And the player types are as follow:


1: Lizard
2: Paper
3: Rock
4: Scissors
5: Spock


So for example, to find "how many times the user has won, lost and draw" I need to run the following queries:

SELECT COUNT(*) FROM match WHERE result=0; // won
SELECT COUNT(*) FROM match WHERE result=1; // lost
SELECT COUNT(*) FROM match WHERE result=2; // draw  


But if I want to find "how many times the user using the player Lizard has won, lost and draw" then I need to run the following queries: 

SELECT COUNT(*) FROM match WHERE user_player_id = 1 AND result=0; // won using Lizard
SELECT COUNT(*) FROM match WHERE user_player_id = 1 AND result=1; // lost using Lizard
SELECT COUNT(*) FROM match WHERE user_player_id = 1 AND result=2; // draw using Lizard


And I need to keep repeating the same queries all the time for get those types of statistics.

Is there any other approach or a better way to achieve this? I am using MySQL 5.6.
To increase efficiency, you could run the query and get all 3 results at once:

SELECT COUNT(*) AS cnt, result 
FROM match 
GROUP BY result


That should give you 3 rows in this situation, which you can deal with in the application layer however you want.

As by the example above, you would get something like the following:

Cnt | Result
10 | 0
15 | 1
25 | 2


I would personally say do the following with structure:



The Matches table holds one record per match, with references to each other table.

The Player table holds one record for each player (Whether they are a computer or user), with a reference to the PlayerType table

The PlayerTypes table holds the ID and the type of player (User, or computer etc)

The ResultTypes table holds a record for each possible result of a match, with the TypeName column being the text for that result (IE 'Won', 'Loss' etc)

I've not added any foreign keys etc - should be fairly easy to work out from the names though. http://sqlfiddle.com/#!9/865078

The below query would then give you your result:

SELECT COUNT(Matches.ID), ResultTypes.TypeName
FROM Matches
INNER JOIN ResultTypes ON Matches.ResultID = ResultTypes.TypeID
GROUP BY ResultTypes.TypeName