Recording sports matches - many-to-many relationships?

Question

I'm new to Ruby on Rails, but in the past I have created some simple applications. Now I'm doing something a little more complicated, and I'm at a standstill in database design.

I am creating a sports league manager, and I need advice on how to model the relationship between teams and games in order to point me in the right direction. Each time a game is played between two teams, a match is recorded. I would like to be able to do the following:

1) On a specific page of the team I would like to show a list of matches in which the team participated.

2) I would like records of each victory, loss and connections of each team to be shown on the page in the league.

At point number 1, I realized that this would be a "many to many" relationship, that is, the team has many matches, and there are many teams in the match (well, there are only two in fact). The fact that I'm a little fixated is how and where to store statistics for each team. Where can I save wins / losses / ties? Are they part of the Team table? If so, if I had a page with team stats showing each team with losses / connections, how would I get this information?

Answer 1

This is actually not finished, but perhaps it will help you or someone else get the ball moving here.

I focus only on how to structure relationships between teams and matches. At least part of the solution is to use a polymorphic association, I suppose, and part of it might be a self-connection. I swear it right in front of me, and I do not see it.

Taking the baby's steps here, assuming you have a table similar to this table for your matches ...

id | home_team_id | away_team_id | home_team_score | away_team_score 

You can set this on your models using the following associations:

 class Match belongs_to :home_team, :class_name => :team belongs_to :away_team, :class_name => :team end class Team has_many :home_matches, :foreign_key => :home_team_id, :class_name => :matches has_many :away_matches, :foreign_key => :away_team_id, :class_name => :matches end 

The obvious problem is that there are two connections when there really should be only one. That's why I think polymorphic association might help, but it's kind of confusing.

See the Rails manual on polymorphic associations and see if this helps what I can't.

Answer 2

I suggest not creating a traditional many-to-many relationship. Instead, you will have only two tables: Teams and Matches .

Each team will be identified by a line in Teams and will have a unique identifier, for example TeamId .

The Matches table would have the following columns:

• MatchId - Synthetic Primary Key
• SeasonId - determines the season when the match took place in
• HomeTeamId - home team
• VisitngTeamId - a team of guests.
• HomeTeamScore
• VisitngTeamScore
• ... Any other statistics you want to keep for a single match

I assume you have the concept of home and guest teams. If not, you can simply name these columns Team1Id and Team2Id , or something on these lines.

The point at which I am a little puzzled is how and where to store statistics for each team. Where can I save wins / losses / ties?

Wins, losses and relationships are implicit in the Matches table - you can request this to return a report about the team. For example, the following query returns winnings, losses, and bindings for team X:

 -- Wins SELECT COUNT(*) FROM Matches WHERE SeasonID = @SeasonID AND (HomeTeamId = X AND HomeTeamScore > VisitingTeamScore) OR (VisitingTeamId = X AND VisitingTeamScore > HomeTeamScore) -- Loses SELECT COUNT(*) FROM Matches WHERE SeasonID = @SeasonID AND (HomeTeamId = X AND HomeTeamScore < VisitingTeamScore) OR (VisitingTeamId = X AND VisitingTeamScore < HomeTeamScore) -- Ties SELECT COUNT(*) FROM Matches WHERE SeasonID = @SeasonID AND (HomeTeamId = X OR VisitingTeamId = X) AND VisitingTeamScore = HomeTeamScore 

Even if you want to denormalize the data model and store this information for each team, you do not want to do this in the Teams table, because you can know how many wins / losses / connections the team has for a given season. (I believe that a team can fight in several seasons. If it is not, do not pay attention.)

Answer 3

I'm leaving here, but think:

 tblTeam TeamID TeamName . . . OtherTeamFields tblMatch MatchID MatchDate MatchLocationID . . . OtherMatchFields tblTeam_Matches TeamID FK on tblTeam.TeamID MatchID FK on tblMatchID TeamStanding (Win, Loss, Tie) 

There are some pros and cons to the structure above. From the point of view of professionals, the result for each team participating in the match is correctly stored with the team's attitude to this match. You can get results for each team using a series of matches by setting the criteria for TeamID and TeamStanding (ie "WHERE TeamStanding =" Win ").

However, there is a more complex, but probably more scalable and useful way, in which you would define a TeamScore field for tblTeam_Matches. In this case, the winner of the match will be determined by a rather complicated series of subqueries (complicated, I mean, it's hard for me. I'm sure there are people here who could quickly jump out ... But this is a little brain teaser).

I believe that the second option would be a more “correct” way to do this, but I messed up a few options:

 StatsQuery: TeamName TotalMatches Wins Losses Ties 

It was hard for me. Correlated subqueries are NOT my forte (yet).

Anyway, hope this gives you some food for thought.,

Answer 4

I think it can be done as follows

 class Team < ActiveRecord::Base has_many :games has_many :matches,:through => :games end class Matche < ActiveRecord::Base has_many :games has_many :teams,:through => :games end class Game < ActiveRecord::Base belongs_to :team belongs_to :match end 

and if you need to find the final matches played by any team, then

 = team.games.count 

if you need to find the final matches won by any team, then

 = team.games.where(:winner => true).count