7 Replies Latest reply on Jan 28, 2011 11:25 AM by philmodjunk

    Please help with ERD setup for a sports league

    larsof54

      Title

      Please help with ERD setup for a sports league

      Post

      Here is my sport’s scenario:

      There are 25 weeks of play. Each week has 8 matches between 2 teams. Each team uses 5 to 7 players per match. Players may play for different teams in different matches.

      I need to calculate stats for each player and each team (relatively easy).

      How should I set up an ERD for that scenario?

      TIA, Larry W. 

        • 1. Re: Please help with ERD setup for a sports league
          philmodjunk

          You have players, matches and teams.

          Players---<Matches>---Teams

          Players::PlayerID = Matches::PlayerID
          Teams::TeamID = Matches::TeamID

          A given record in Matches links one player to one team for a given match.

          That may be all you need here, but there's a lot you don't say about how your matches function. Does one player play multiple games at a given match? Is there information about a given match that you need to record?

          • 2. Re: Please help with ERD setup for a sports league
            larsof54

            Well...

            To start let me say thank you so much for your help and advice.

            There is a 5 player lineup for each team in the match and there are 7 rounds of 5 games per round so each player will play 7 games unless a spare is put in for him/her. Spares (up to 2 per team) can be substituted for players in the initial lineup. The team with the highest score for a round gets one point for that round and the team with the highest cumulative scores gets an additional point.

            Each player has a multitude of stats calculated for them: total of scores, games won, games lost, games played, winning percentage, runouts, break and runs against, number of weeks played, and a handicap number. All of the player stats and team stats are trivial once I have access to the numbers.

            Each team is rated by their total points.

            Players may be loaned to other teams on any given night.

            So, I have many weeks, many teams, many matches and many players and I need to link everything together.

            LW

            • 3. Re: Please help with ERD setup for a sports league
              philmodjunk

              What I've described will support that, but I suspect that you'll need a Games table for tracking scores on each game. I'm not sure, but it sounds like games match individual players from each time against each other.

              You might also have data to record specific to a given match but not to each team or player. You might need to record the date and location of the match, for example. That would also require another table.

              If we rename Matches to Player_Team_Match, we can set up these relationships:

              Players::PlayerID = Player_Team_Match::PlayerID
              Teams::TeamID = Player_Team_Match::TeamID
              Matches::MatchID = Player_Team_Match::MatchID

              For a given match, Player_Team_Match would have something like 10 records all with the same MatchID but with 5 having the TeamID of one team and the other 5 would have the teamID of the opposing team.

              Assuming that I have it figured out correctly that players compete "head to head", A GameStats table could be defined to collect all data for a given player's game and linked by PlayerID and MatchID:

              GameStats::PlayerID = Player_Team_Match::PlayerID AND
              GameStats::MatchID = Player_Team_Match::MatchID

              A given game would collect stats for one player in one game.

              Note how this just keeps growing?

              You'll probably need more than this before you are done. You might, for example want yet another table to identify which players participated in a given game.

              • 4. Re: Please help with ERD setup for a sports league
                larsof54

                Actually, I have setup a ScoreSheet table and layout that will be used for entering all of the results in a match. I have already made it calculate stats for each player that played in that match. 

                Are your suggesting that each game in a match be a table? If so, that’s a lot of tables.

                25 weeks x 8 matches x 35 games = 560 tables. Wow!

                • 5. Re: Please help with ERD setup for a sports league
                  philmodjunk

                  Each game would be a different record in the same table with relationships to link a game record to the correct player records. Not in separate tables for each game--that's nearly impossible to implement in FileMaker anyway.

                  • 6. Re: Please help with ERD setup for a sports league
                    larsof54

                    Here’s where I am with this project. I have 3 tables: 

                    ScoreSheets – used for entering match data and compiling stats for the players that played in a match. There will be 8 records per week for the 8 matches played.

                    WeeklyPlayerStats – used to hold the compiled totals from the ScoreSheet records. I have set this up as 7 fields. PlayerName is the key. The other 6 fields are repeating fields with a depth of 25, one for each week in the schedule.

                    PlayerStatsSummary – used to display a print a list of players along with their stats.

                    I am hoping to dump data from the ScoreSheets records to the Weekly PlayerStats via a script.

                    Do you think I could make this work or am I totally off-base?

                    • 7. Re: Please help with ERD setup for a sports league
                      philmodjunk

                      It's not impossible, but the repeating fields may needlessly complicate things for you. Generally, a related table where you use one related record for each case where you would have used a repetition can give you much more flexibility in working with your stats.

                      PlayerName is the key

                      That can be a bit of a problem as names are not unique, can change and create issues hard to resolve should you enter a name incorrectly and then find you have to correct it some time later after multiple related records have been added.

                      I am hoping to dump data from the ScoreSheets records to the Weekly PlayerStats via a script.

                      With proper relationships a script will become unecessary for most such cases as the relationship can be used to pull the data from the individual score sheet records for summary calculations performed in the weekly stat records.

                      Bottom line here is that this is your database. I'm laboring under a major handicap in that I have no idea how you need to manage your meets and the games played at them. Thus, I can only make guesses based on basic relational database design prinicples. If relational databases and/or Filemaker is new to you, you may want to think small, start with one small portion of your project at a time and build up to more complex designs as you learn (The risk here, is that you may find you need to throw out your original design and start over should you find your original approach can't be extended the way you need to.)