using a relational database to store results from head-to-head(-to-head) gaming

I've got a site where people record game results. 95% of the games are played solo, but the 5% that aren't throw off the win-draw-win statistics for each scenario.

Each battle has 2 sides, and all possible results are:

• Side 1 wins
• Side 2 wins
• Draw

If 2 people play each other, and both record a play, you get 2 wins for a given side when there should only be one, (making sense?) and it throws off the balance rating. Those records should be merged when it comes time to display the battle's stats.

My original kludge was to program the PHP such that same play date + same result = group into one result only, however as the userbase has grown collisions (people playing on the same day and getting the same result by chance) have become more frequent.

As my programming ability has increased, I feel ready to strip out the kludge and replace it with a proper head-to-head handler, but am having trouble coming up with an acceptable database structure.

Currently, all records are stored in one table, battle_results, which has the following structure:

[play_id] [user_id] [battle_id] [play_date] [win_side]
  000001    007       DeR开发者_开发百科a001    2010-01-01     1
  000002    010       DeRa001    2010-01-01     1

I want to give my users the ability to list whether it was a solo or head-to-head play, and what side they were playing, so I plan to modify the table like so:

[play_id] [user_id] [battle_id] [play_date] [win_side] [play_type] [user_side]
  000001    007       DeRa001    2010-01-01    1           multi        2
  000002    010       DeRa001    2010-01-01    1           multi        1

that's easy enough. But the users need to be able to say who they were playing with. At first I thought a many-to-many table would do the trick:

[play_id] [user_id] [opponent_id]
  00001     007        010

but then I realized that when the opponent submits the same record you'll just be reversing columns:

[play_id] [user_id] [opponent_id]
  00001     007        010
  00002     010        007

and I really have no idea how to pull that data out and construct a win-draw-win ratio that would recognize that these people were all playing together...

Am I making sense? How do I match up those records?

---

I would do it slightly different.

I assume you have a table battle and for each battle there can be multiple plays where two people play together.

battle_results would contain:

play_id   battle_id   play_date    play_type
      1     DeRa001   2011-01-01       multi

Now you need a new table participants that lists the participants in each play:

play_id user_id is_winner position
      1     007         0    Side1
      1     010         1    Side2

That table would have (play_id, user_id) as the primary key, so the same user can't play twice for the same play (this solves the problem that the same combination can be inserted twice with a different "direction". Note that play_id is unique for a single play. So you'll have always two rows in there with the same play_id.

Edit: you can indicate a draw by setting is_winner to 0 for all participants in a play.

To find out with whom a specific user (e.g. 007) played is simple then:

SELECT user_id 
FROM participants
WHERE play_id IN (SELECT p2.play_id
                    FROM participants p2
                   WHERE p2.user_id = '007')

To find the total number of wins for a user:

SELECT count(*)
FROM participants
WHERE user_id = '007'
AND is_winner = 1

To find the win/loss ratio:

SELECT total_loss / total_wins
FROM ( 
  SELECT user_id, 
         count(CASE WHEN is_winner = 0 THEN 1 ELSE NULL) as total_loss, 
         count(CASE WHEN is_winner = 1 THEN 1 ELSE NULL) as total_wins
  FROM participants
) T
WHERE user_id = '007'

---

What's wrong with storing the data like this:

[battle_id] [opponent_1] [opponent_2] [result_1] [result_2]
    1            007         010          1          0
    2            011         007          0          1
    3            007         012          0          0

looking for 007 player wins (will result 2):

select count(*) from battles 
where 
   (opponent_1 = '007' AND result_1 = 1 and result_2 = 0) 
   OR 
   (opponent_2 = '007' AND result_1 = 0 and result_2 = 1)

looking for players 007 draws (will result 1):

select count(*) from battles 
where 
     (opponent_1 = '007' AND result_1 = 0 and result_2 = 0) 
     OR 
     (opponent_2 = '007' AND result_1 = 0 and result_2 = 0)

etc.