CODEWITHSUNDEEP
database
deltanovember
deltanovember

Reputation: 44091

What's the correct data design for recording the results of generic two player game?

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

Answers (2)

David Tang
David Tang

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

billygoat
billygoat

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

Related Questions