Reputation: 44091
Assume the game has two players. Each player has a score and highest score wins. A user table is self evident. The first design I thought of is as follows:
match_id, user1_id, user2_id, score1, score2
The problem is suppose I want a quick way of ranking users by win %. In the above table a single userid would be scattered between user1_id and user2_id. It would seem to require multiple queries and seems messy. The other way is to keep a separate stats table
user_id, winloss
This makes queries easy but doesn't seem theoretically correct because the stats table stores derive data. Is there a way to solve this which involves a nice, clean theoretically correct data structure which is easy to query?
Upvotes: 2
Views: 286
Reputation: 93714
How about have one record for each user in a match? The primary key is then the combination of match_id
and user_id
. Also include a boolean winner
field.
match_id, user_id, score, winner
Though not part of your requirement, this also easily scales up to more players.
Querying win % is simply a matter of:
SELECT winner, count(winner) FROM match_results
WHERE user_id = (some_user) GROUP BY winner;
Upvotes: 3
Reputation: 22004
I will have a user table (obviously), a Join table called Match with just match id and a transactional table that will have user_id, match_id, score. So for a particular match, there would be two records corresponding to two user.
Also, I will also store a fk reference of the winning user_id in the match table. Just a quick way to find % of win for each user.
Upvotes: 0