1 2 Previous Next 18 Replies Latest reply on Apr 23, 2010 7:00 AM by comment_1

    Many to many to many relationship troubles

    JoSmo

      Title

      Many to many to many relationship troubles

      Post

      I am  working on a football stats program in filemaker and I am having a little trouble comprehending some relationships.

      I have Players, Teams, and Seasons (or school year)

      One team has many players
      One team has many seasons
      One season has many players
      One player can play for many seasons, but only for one team


      I am having trouble filtering stats for a single player over multiple seasons.

       

      I am now using FMPA 11 and I would consider myself one step above a novice.





        • 1. Re: Many to many to many relationship troubles
          comment_1

          I think you are missing (at least) one additional table, to record for which team each player played in each season (a star join of your three tables). I am not sure what exactly is included in "stats", so I can't say if this can be recorded in the added table or requires another one.

          • 2. Re: Many to many to many relationship troubles
            JoSmo

             


            comment wrote:

            I think you are missing (at least) one additional table, to record for which team each player played in each season (a star join of your three tables). I am not sure what exactly is included in "stats", so I can't say if this can be recorded in the added table or requires another one.


             

             

            The stats would be player stats (yards gained, catches, passes, punts returned etc.) and team stats (games won, lost, totals of player stats, etc.  This would also include totals of all teams played against eg. our team averaged 3.9 yrds. per carry and our opponents averaged only 2.2 yrds. per carry during the 2009 season)

             

            Player stats could be cumulative for all the years he played football.

             

            I knew that I was missing join tables, but could not figure out what the join table(s) should be.


            • 3. Re: Many to many to many relationship troubles
              comment_1

              Re player stats: if these are one datum per season, then you can record them in the join table. For example:

               

              PlayerID: 123

              TeamID: 14

              SeasonID: 9

              YardsGained: 426

              Catches: 16

              ...

               

              If they are cumulative (one datum per carreer), then they can go into the Players table.

               

               

              Same thing for team stats: you'd need another join table between Teams and Seasons to record season stats.

               

              • 4. Re: Many to many to many relationship troubles
                JoSmo

                 

                OK, I am fairly novice, so let me see if I understand this correctly:
                My join table would simply contain foreign keys from PLAYER, TEAM, and SEASON.  Along with this all of my specific player stats.
                Player stats are needed for individual games, seasons, and careers.
                I suspect that I need a foreign key then to identify which game of the season specific stats were compiled also?

                 

                 

                • 5. Re: Many to many to many relationship troubles
                  comment_1

                   


                  JoSmo wrote:

                  Player stats are needed for individual games, seasons, and careers.


                  For seasons stats - no problem (as mentioned above, they'd be in the join table). For career stats, you could simply total the season stats in the Players table.

                   

                  However, for individual games … this changes the picture considerably. First, where is your Games table? And since the relationship between Players and Games is many-to-many, you will need a join table to record the player's stats for a specific game.

                   


                  • 6. Re: Many to many to many relationship troubles
                    JoSmo

                    I used the relationship between PLAYER and TEAM (M:1)  & TEAM to GAME:

                     

                    1 TEAM can have many GAMEs, but one GAME can only have two TEAMs and must have two TEAMs (one Home team, and one visitor team - which I set up as two separate table occurrences of TEAM)

                    • 7. Re: Many to many to many relationship troubles
                      comment_1

                      The relationship between Teams and Players is NOT one-to-many: a team has many players, and a player can have (over time) more than one team.

                       

                      There are several ways to look at this - I believe my preference would be something like:

                      http://www.filedropper.com/stats_1

                       

                      (note that this is an ERD, not a Filemaker relationship graph)

                      • 8. Re: Many to many to many relationship troubles
                        JoSmo

                        I thought at first that this made complete sense, but I still can't grasp the concept of Players and Teams being many to many.

                         

                        Isn't the team (or the school) always constant and isn't the variable the season played?

                         

                        Maybe I'm just looking at it all wrong...

                        • 9. Re: Many to many to many relationship troubles
                          comment_1

                          Perhaps I am misunderstanding the situation: isn't a player allowed to change teams between seasons?

                          • 10. Re: Many to many to many relationship troubles
                            JoSmo

                            Ah, that would be very rare, and typically the new team would start his stats from scratch, but I see your point.  Say in the NFL, where a player is not bound to a team such as a player who plays at the college or high school level.  I was thinking that my stats program would be used by a single school, but the thought of league stats is intriguing.  As long as the players id number remained the same couldn't his stats be tracked where ever he went?

                            • 11. Re: Many to many to many relationship troubles
                              comment_1

                               


                              JoSmo wrote:

                              As long as the players id number remained the same couldn't his stats be tracked where ever he went?


                               

                              Yes, but the association with the team couldn't. To put it in more technical terms: if the player is allowed to switch teams, then TeamID cannot be an attribute of Player. If it were, then a player could only belong to one team at all times. If you switch him to another team, then he has always belonged to the new team - which isn't true in reality.

                               

                               

                              This would also affect the team stats, since switching a player to another team would bring over all his stats to the new team - even though some of them were gained while he was playing for the old team.


                              • 12. Re: Many to many to many relationship troubles
                                JoSmo

                                Hence the join table SEASON_STATS where TEAM_ID and PLAYER_ID  are attributes of SEASON_STATS?

                                 

                                So a player has season stats, which are made up of game stats and a team has season stats but the player is tied to the season and not the team?!

                                 

                                Okay, I don't understand the relationship between SEASONS and GAMES.

                                • 13. Re: Many to many to many relationship troubles
                                  comment_1

                                   


                                  JoSmo wrote:

                                  Hence the join table SEASON_STATS where TEAM_ID and PLAYER_ID  are attributes of SEASON_STATS?


                                   

                                   

                                  Yes. I find it more useful to think of it as a table of Erollments (Player X played for Team Y in Season Z). The season stats are just riding atop of this.

                                   

                                   


                                  JoSmo wrote:

                                  but the player is tied to the season and not the team?!


                                   

                                  The player is not tied directly to either season or team. But indirectly, through the Enrollments join table, he is tied both to a season and a team (or, more precisely, he is tied to all the seasons and all the teams he played).

                                   

                                   

                                   


                                  JoSmo wrote:

                                  I don't understand the relationship between SEASONS and GAMES.


                                   

                                  Well, each game is played in a particular season, so it may be convenient to make them related directly. It's not strictly necessary, since they are also related through the join tables.

                                   




                                  • 14. Re: Many to many to many relationship troubles
                                    JoSmo

                                    This has been a real eye opener for me.  Unfortunately every answer brings up more questions!  How long are you willing to go on with this, and where do I send the check?

                                    1 2 Previous Next