Recording a sports team's matches - a many-to-many relationship?

I am new to Ruby on Rails, but I have created a few simple apps in the past. Now I am doing something a little more complex and I am stumped on database design.

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

1) On a specific team's page, I would like to show a lis开发者_如何转开发t of matches the team has participated in.

2) I would like to keep a record of each team's wins, losses, and ties to show on a league standings page.

On point #1 I figured this would be a many-to-many relationship, that is, a Team has many Matches, and a Match has many Teams (well, just two actually). The point I am a bit stumped on is how and where to store the stats for each team. Where do I keep the wins/losses/ties? Are they part of the Team table? If so, if I was to have a page with team standings showing each teams w/losses/ties, how would I get that information?

---

This isn't really finished, but maybe this will help you or someone else get the ball rolling here.

I'm focusing on just how to structure the relationship between Teams and Matches. At least part of the solution lies in using a polymorphic association, I believe, and part of it would perhaps be a self join. I swear it's right in front of me and I'm not seeing it.

Taking baby steps here, assuming you have a table like this for your Matches table…

  id   |   home_team_id  |  away_team_id  |  home_team_score  |  away_team_score

You can set that up in your models with these 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 there is that there are two relationships when there really should only be one. That's why I think a polymorphic association can help, but this is sort of convoluted.

See the Rails guide on polymorphic associations and see if that helps you see what I can't.

---

I would suggest against creating a traditional many-to-many relationship here. Instead, you'd have just two tables: Teams and Matches.

Each team would be identified by a row in Teams and would have a unique identifier, such as TeamId.

The Matches table would the following columns:

• MatchId - a synthetic primary key
• SeasonId - identifies the season the match took place in
• HomeTeamId - the home team
• VisitngTeamId - the away team
• HomeTeamScore
• VisitngTeamScore
• ... Any other statistics you'd want to keep for an individual match

I presume you have the notion of home and visiting teams. If not, you can just name these columns Team1Id and Team2Id, or something along those lines.

The point I am a bit stumped on is how and where to store the stats for each team. Where do I keep the wins/losses/ties?

The wins, losses, and ties are implicit in the Matches table - you can query that to get back a team's record. For instance, the following query returns the wins, loses, and ties 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 wanted to denormalize the data model and store this information for each team, you wouldn't want to do it in the Teams table because you may want to know how many wins/losses/ties a team has for a given season. (I presume a team may stick together through multiple seasons. If this is not the case, disregard.)

---

I'm going off-the-cuff here, but consider:

tblTeam
    TeamID
    TeamName
     . . . OtherTeamFields

tblMatch
    MatchID
    MatchDate
    MatchLocationID
     . . . OtherMatchFields

tblTeam_Matches
    TeamID FK on tblTeam.TeamID
    MatchID FK on tblMatchID
    TeamStanding (Win, Loss, Tie)

The structure above has some pros and cons. On the pro side, the outcome for each team involved in a match is stored properly with the team's relationship to that match. One can retrieve the results for each team through a range of matches by setting criteria for TeamID and TeamStanding (i.e. "WHERE TeamStanding = "Win").

However, there is a more complex, but probably more scalable and usefull way, where you would define a TeamScore field for tblTeam_Matches. In this case, the Match winner would be determined by a rather difficult series of sub-queries (By difficult, I mean difficult for ME. I am betting there are folks here on SO who could pop a smaple out quickly . . . But it is a bit of a brain-teaser).

I believe the second option would be a more "proper" way to do it, but I messed with puling some version of:

StatsQuery:
    TeamName
    TotalMatches
    Wins
    Losses
    Ties

I had a difficult time of it. Correlated sub-queries are NOT my strong point (yet).

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

---

I think this can be done using following way

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 total matches played by any team then

= team.games.count

if you need to find total won matches by any team then

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