Assume the overall game has two gamers. Each player includes a score and greatest score wins. A person table is self apparent. The very first design I figured of is the following:
match_id, user1_id, user2_id, score1, score2
The issue is suppose I would like a fast method of ranking customers by win %. Within the above table just one userid could be scattered between user1_id and user2_id. It might appear to want multiple queries and appears untidy. Another strategy is to help keep another stats table
This will make queries easy but does not appear theoretically correct since the stats table stores derive data. It is possible to method to solve this that involves a pleasant, clean theoretically correct data structure that is simple to query?
What about have one record for every user inside a match? The main secret is then your mixture of
user_id. Likewise incorporate a boolean
match_id, user_id, score, champion
Though not a part of your requirement, this easily scales as much as more gamers.
Querying win % is only a few:
SELECT winner, count(winner) FROM match_results WHERE user_id = (some_user) GROUP BY winner;
I'll possess a user table (clearly), a Join table known as Complement just match id along with a transactional table which will have user_id, match_id, score. So for the match, there'd be two records akin to two user.
Also, I'll also store a fk reference from the winning user_id within the match table. Only a quick strategy for finding % of win for every user.