6 Replies Latest reply on Jan 17, 2010 8:46 AM by JohnnyV

    Relationships

    JohnnyV

      Title

      Relationships

      Post

      Trying to create a solution for tracking athletic games and tracking the statistics generated by them. Keeping it simple, I want to track games where each game can have two teams and there can be as many as 50 games played in a week and over 400 in a season. I'd like to track each game in a Game table with each record containing the names of each team and other information like date, time, scores, etc. After the weekly games are played I want to separate each team from the game record and the scoring information and have it in a Team table where I can generate sub-summaries that would give the averages for scores, etc. When the next week of play occurred I would like to take these averages in the Team table and have them available in the Game Table where new games are played by different teams (No two teams play each other twice unless its in the playoffs after the regular season is over).

       

      In summary, I have multiple games each week. Each game (record) has two teams (visitor and home) and the scores after they have played (visitor score and home score). After all games have been played each team in the game would become a single record in another table where the individual teams statistical profile (averages of the visitor score, home score. etc.) could be computed. Each teams statistics would then be available for the following week of play but each game would have different teams playing different opponents (no two teams would play each other twice unless it would be in the playoffs after the regular season). When a new record (game) is created and each team is put into the record, their averages would come in from the Team table.

       

      I'm rather new to creating relationship and using primary key fields, so I'm struggling on how to create the relationship for the games (Game ID) that contains two teams (no key ID), with the teams (Team ID). Also I'm not sure that even if a relationship could be created that works, whether the sub-summaries in the Team table could be displayed in the Game table. I don't think it's a Lookup as the averages will change from week-to-week. I tried to just import them from the Team table but couldn't do that, which may also have something to do with the relationships.

       

      Have been trying to solve this for some time so would appreciate any guidance. 

        • 1. Re: Relationships
          comment_1
            

          It would be best to input all teams in advance into the Teams table. Then for each game, pick the HomeTeamID and VisitorTeamID.

          The stats of each team will be available "live" through two relationships to the Games table (one for home games, one for away games).

          • 2. Re: Relationships
            philmodjunk
              

            There's a lot here you may have to research further. Feel free to post follow up questions as you hit things you don't understand or can't get to work.

             

            You're dealing with the central reason why databases should be relational databases. You don't want to enter identical data more than once. It's more work and any updates to the data become a major job.

             

            You should have at least two tables. You'll also need a third "Join" table so you can record the scores for each team for each game and any other such stats.

             

            Set up a Teams table where one record records all the information about that team. You'll want at least two tables:

            TeamID (auto-entered serial number) and TeamName

             

            Set up a Games table where one record documents a game. You could define fields here for game date, game location and other such details. You must have this field:

            GameID (serial number)

             

            Setup a participants table to link the Games and the teams to each other.

            At least Four fields:

            GameID

            TeamID

            Score

            VisitorStatus (To distinguish between host and visiting teams)

             

            Set up your relationships:

            Games::GameID = Participants::GameID

            Teams::TeamID = Participants::TeamID

             

            Enable the "Allow creation of records via this relationship" option for Participants in both these relationships.

             

            Now you can set up a portal to Participants on a Games Layout and show fields in it from Particpants and Teams.

            You can also set up a portal to Participants on a Teams layout and show fields in it from Participants and Games.

             

            Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:

            Table vs. Table Occurrence (Tutorial)

             

            Here's a link to a simple tutorial on setting up summary reports that you may find useful:

            Creating Filemaker Pro summary reports--Tutorial

            • 3. Re: Relationships
              JohnnyV
                

              Thanks. Want to make sure I'm clear. The game itself would have a GameID but would have nothing to do with the relationship. Both the visiting team and home team would each have their own ID i.e VisitorID and HomeID. The teams in the Team table would be a list of all teams that play during the season with each team having a TeamID. When a new game is created and a team is put into the Visitor field using a drop down list of teams with ID's, the averages should appear in a related field on the current layout. And the same for the home team.

               

              If I'm correct, I'll give it a try. One last question, do the records in the Team table have to be sorted every time so the averages that appear in the sub-summary are "live" in the Game table or doesn't it make any difference? 

              • 4. Re: Relationships
                comment_1
                  

                JohnnyV wrote:

                One last question, do the records in the Team table have to be sorted every time so the averages that appear in the sub-summary are "live" in the Game table or doesn't it make any difference? 


                The team's stats would not be in a sub-summary - they would be unstored calculations in the Teams table, using aggregate functions over the relationships to the Games table.

                 

                 

                It would be convenient to organize the relationships this way:

                 

                Games::HomeTeamID = HomeTeam::TeamID

                 

                Games::VisitorTeamID = VisitorTeam::TeamID

                 

                 

                Teams::TeamID  = HomeGames::HomeTeamID

                 

                Teams::TeamID  = AwayGames::VisitorTeamID

                 

                (the colors signify table occurrences of the same table)

                 

                 

                In the Teams table a calculation =

                 

                Sum ( HomeGames::HomeGoals ) + Sum ( AwayGames::AwayGoals )

                 

                would return the total goals scored by the team.


                • 5. Re: Relationships
                  JohnnyV
                    

                  Thanks. I've received several responses and I think there's enough information to help me through this.

                   

                  One other thing that I'm not clear on is how the primary and foreign keys in each table works. I understand using a serial number in the primary key field and that a new number is generated with each new record but when is a matching number generated in the foreign ket field in the related table. Since I'm new to the relationship concept I'm not sure how this is processed. I'm presuming that I should not generate any records until after I get all my fields and layouts organized. Once I start creating new records the key fields will contain matching serial numbers.

                   

                  Since I have a day job I won't get to this until later in the day. Will let you know how it goes later or tomorrow. 

                  • 6. Re: Relationships
                    JohnnyV
                      

                    Sorry I'm like a deer in headlights on this. Is it possible to send a relationship graph with the relationships you have proposed? If your OK with that I'll post an e-mail address for your use. If there's a better approach, please let me know.

                     

                    Thanks,