Are you trying to produce a found set of records in Match Stats that contain all the data for the opposing teams for the games placed by a specified team?
Coming back to my list of things to do on this database and this is causing me the most difficulty. My aim is to create a list view where i can compare the season averages for a team. It will be used to create a standings ladder of sorts. It would be such that I would see Team A - points for and points against, Team B points for and points against etc. If and once this can be achieved I would then expand this to other statistical data that I have.
The current structure of the data is that each MatchStats record only shows information related to that team:
Match 1 , Team A, goals scored
Match 1 Team B, goals scored
Do I need to restructure so that I have both teams data in one match record? (this may open up a whole new can of worms!)
I think that your current relationships resemble this:
Matches 2-----<MatchStats>--------Teams-----<Matches>-----OpposingTeams (TO of Teams)
Is that correct?
The following relationship would link to the MatchStats of the opposing team:
Teams----<Matches>---OpposingTeams-------<OpponentMatchStats (TO of MatchStats)
Your reports/calculations would start from Teams and reach through Matches, OpposingTeams to the OppponentMatchStats table occurrence of MatchStatus to access the stats of the opposing teams.
Ok - will see if i can replicate what you have suggested.
I currently have:
match - - - <Match stats>- - - Teams
which is in line with your assumption. I am unsure how to now get the calculation fields working but I think i can see where you are pointing me. I will try and get back to you with the result.
Have set up your tables as suggested. My question is which realtionship do I create for the TEAMS table to the MATCH table as their is both a Home and and Away teamID. Do i link it to one, and if so which one or do I link it to both?
Teams Table - Team ID, Team Name
Match - MatchID, Home team IDfk, Away TeamIDfk
Match Stats - Match IDfk, Team IDfk, stats fields
Teams::Team ID = Match::Home team IDfk
OpposingTeam::Team ID = Match::Away team IDfk
And OpposingTeam is a new table occurrence for the Teams table.
I am looking for a sports template for fencing and chess leagues that we run. I've had a difficult time developing one on my one and am now looking to purchase one I can own and change the field names and calculations for our specific sports.
The template you are describing is interesting to me. Do you have any interest in selling it?