使用关系数据库来存储头对头（头对头）游戏的结果 [英] using a relational database to store results from head-to-head(-to-head) gaming

问题描述

我有一个网站，人们记录游戏结果。 95％的游戏都是独奏的，但是5％的比赛不会甩掉每个场景的胜利统计数据。

每场战斗都有两面，所有可能的结果是：

• Side 1 wins


• Side 2 wins


• 绘制

如果2人互相玩耍，并且都记录一场戏，你将获得2胜给定的一面，只有一个，（有意义的），它会抛出平衡评级。这些记录应该在显示战斗统计数据的时候合并。

我原来的kludge是对PHP进行编程，使得相同的播放日期+同样的结果=组合成一个结果，但是随着用户数量增加的碰撞（人们在同一天玩耍并获得相同的结果）变得越来越频繁。

随着我的编程能力的增加，我随时准备好把这个kludge替换掉，并用适当的头对头处理程序来替换它，但是遇到一个可以接受的数据库结构的麻烦。

目前，所有记录都存储在一个表格 battle_results 中，其结构如下：

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

我想给我的用户列出是否是一个独奏， to-head play，以及他们在玩什么方面，所以我打算像这样修改表：

  [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 
  

这很简单。但是用户需要能够说出他们在玩什么。起初我认为一个多对多的表会做到这一点：

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

但是我意识到当对手提交相同的记录时，你会只是反转列：

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

我真的不知道如何拉出数据并构建一个win-draw-赢得比例，将会认识到这些人都在一起玩...

我有意义吗？如何匹配这些记录？

解决方案

我会做的略有不同。

我假设你有一张桌子战斗，每场战斗可以有两个人在一起玩耍的多个角色。

battle_results将包含：

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

现在您需要一个新表参与者，列出每个玩家的参与者：

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

该表将（play_id，user_id）作为主键，所以同一用户不能同时播放两次（这解决了可以使用不同的方向将相同组合插入两次的问题。请注意，play_id对于单个播放是唯一的。所以你总是会在同一个play_id中有两行。

编辑：你可以通过将play_winner设置为0来显示所有参与者的游戏画面。 / p>

要找出一个特定用户（例如007）与谁玩的简单的话：

 
 SELECT user_id 
 FROM参与者
 WHERE play_id IN（SELECT p2.play_id 
 FROM参与者p2 
 WHERE p2.user_id ='007'）
 

要查找用户的总赢数：

 
 SELECT count （*）
从参与者
 WHERE user_id ='007'
 AND is_winner = 1 
 

找到失败率：

 
 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参与者
）T 
 WHERE user_id ='007'
 

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       DeRa001    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'

这篇关于使用关系数据库来存储头对头（头对头）游戏的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！