1 2 Previous Next 17 Replies Latest reply on Jan 21, 2013 12:15 PM by philmodjunk

    ERD for Athlete Assessments

    LeftyKJ

      Title

      ERD for Athlete Assessments

      Post

           I'm new to databases and FM12, but have already been through a lot of educational material and catching on.  That said, I am trying to create a database where I can track a bunch of athletes (name, age, sport, etc.).  These athletes can then take an assessment which contains one or many protocols.  Assessment and protocol may seem synonymous but an assessment is based on the date and the athlete that then includes different protocols.  Protocols include a category, name and unit of measurement.

           I want to figure how to track all these and eventually build in a dashboard table where I can track the progress of athletes, compare athletes to other athletes or a group of athletes based on criteria (age, graduation year, sport, gender, etc.).  

           Attached is an image of my relationship map so far.  Performance Dashboard is empty because its for later.  

           Can someone please get me started down the right path and advise what to do next? Thank youuuuuuuuuuu

      ERD011813.png

        • 1. Re: ERD for Athlete Assessments
          philmodjunk

               Can you give some examples of the type of "tracking" that you have in mind? There are a lot of possibilities that meet that description.

               I don't think you'll need your dashboard table. Most likely, you'll be able to work from layouts that reference some combination of Athletes and Assessments.

          • 2. Re: ERD for Athlete Assessments
            LeftyKJ

                 Some tracking I would like to include is:

                 1. Top 10 scores for a test, like a leaderboard, that I can filter.  An example would be the top 10 40-yard sprint leaders.  

                 2.  Individuals progress and  Personal Records.  I can graph a person's 40 yard sprint times and also show what their all time best is.

                 I would want to be able to filter these by:

                 A. Date (all-time, past month, week, etc.)

                 B. Athlete Groups (gender, sport, age, etc.)

                  

            • 3. Re: ERD for Athlete Assessments
              philmodjunk

                   1 A filtered portal to Assessments can do that. The relationship you set up for the portal will determine the layout to use--maybe you will need that dashboard table after all. The top 10 part could be produced by having a 10 row portal where the portal or the relationship is sorted so that the 10 ten are the first 10 related recrods.

                   2. Should should be able to chart data from assessments on a layout based on Atheletes or from the assessments layout. If you do this from Athletes, you'll need to use some extra fields as part of the relationship to control the set of related assessment records that supplies data to the chart. If you do htis from a layout on Assessments, you can do this same filtering by performing a find to produce a found set of just those records. Such finds can be scripted to make them faster and more user friendly.

              • 4. Re: ERD for Athlete Assessments
                LeftyKJ

                     I appreciate it Phil.  Starting to get a grasp on what I need to do.  I want to start getting some data to mess with.  In the picture I had a scores table.  So in my assessments table, I will create a record that includes the date, athlete (drop down list from the athlete name table).

                     I will then want to add a portal to add 1- however many tests I want to perform in that assessment.  It will include the test category , the test name and unit of measurement (from protocol table).  I.e. "Speed" - "40 Yard Dash" - "seconds"

                     Do I need the separate scores table to add the score field so I can start getting scores matched up with the date, athlete, and test protocol?  Or can this be included in the assessment field?  

                     Is it a child to assessment along with protocol?

                • 5. Re: ERD for Athlete Assessments
                  philmodjunk

                       How do you intend, on paper, to work with "scores"? How does your assessment data produce such a score?

                  • 6. Re: ERD for Athlete Assessments
                    LeftyKJ

                         Not sure I exactly get what you are saying, but I'll try to explain the process. I'll set up a scenario where height, weight, and 40 yard sprint time is measured.

                         An Athlete comes in for a new Assessment.  On 1/1/2013 this athlete completes an Assessment that includes the Height Test and Weight Test.

                         Date: 1/1/2013 Athlete: Joe Blow

                                                                                                                                                                                                                                                                                                             
                                        Category                     Test                     Score                     units
                                        Body Measurement                     Height                     70                     inches
                                        Body Measurement                     Weight                     175                     pounds

                         This assessment is submitted to record.

                         On 1/15/2013 Joe comes back in for another assessment, but this time we just want to measure his weight and get his 40 yard time.

                          

                         Date: 1/15/2013 Athlete: Joe Blow

                                                                                                                                                                                                                                                                                                             
                                        Category                     Test                     Score                     units
                                        Body Measurement                     Weight                     180                     pounds
                                        Speed/Agility                     40-Yard                     4.3                     seconds

                          

                          

                         With this data I should be able to go into Joe Blows profile and see all his tests sorted by date or category. For example:

                         1/1/2013 - Body Measurement - Height - 70 - inches

                         1/1/2013 - Body Measurement - Weight - 750 -  pounds

                         1/15/2013 - Body Measurement - Weight - 180 - pounds

                         1/15/2013 - Speed/Agility - 40-Yard - 4.3 - seconds

                          

                         As I get more data for this athlete and others, it will let me graph the data for Joe and other athletes I put in.  Is Joe getting faster? losing weight?  How fast is he compared to other boys his age?

                          

                         Does that help?

                    • 7. Re: ERD for Athlete Assessments
                      philmodjunk

                           Ok, so your "score" is a measurement. Don't see any reason why a single number field in the Assessments table can't record that information. I am assuming that you would add new records to assessments each time an athlete is assessed.

                           (In my mind "score" suggested that you might be using a measured time/weight/distance to compute a "score"--possibly from multiple assessments.)

                      • 8. Re: ERD for Athlete Assessments
                        LeftyKJ

                             So if I add a field into the assessment table called score then how do i link it to different tests to make sure when I have an assessment with multiple tests that the score is associated with the appropriate test?

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                                            Category (protocol table)                     Test (protocol table)                     Score (assessment table)                     Unit (protocol table)
                                            Body Measurement                     Height                     70                     

                                                 inches

                                       
                                            Speed/Agility                     40-Yard                     4.3                     seconds
                                            Power                     Vertical Jump                     27                     inches
                                            Strength                     Bench Press 1RM                     265                     pounds

                              

                        • 9. Re: ERD for Athlete Assessments
                          LeftyKJ

                               Note that this data entry would be in the assessment table which above the test score input portal, I will have the date with the athlete name.  

                          • 10. Re: ERD for Athlete Assessments
                            philmodjunk

                                 Isn't each test linked to a different Protocol record? One protocol for each different test?

                                 I just noticed that I missed something. I've been picturing your relationships as:

                                 Athletes-------<Assessments>----------Protocols
                                 Athletes::__pkAthleteID = Assessments::_fkAthleteID
                                 Protocols::__pkProtocolID = Assessments::_fkProtocolID

                                 and these are not the relationships that you actually posted here.

                                 And your oringal post is quite clear, I just didn't read it correctly.

                                 My comments about assessments should actually refer to Protocols and your score field should be in Protocols, not assessments.

                                  

                            • 11. Re: ERD for Athlete Assessments
                              LeftyKJ

                                   I think I'm getting confused on the wording.  protocols and assessments are getting mixed up.  I dont mean to start over, but I renamed my tables to try and make them more clear.

                                   1. Athletes (hasn't changed) and is a parent to..

                                   2. Assessment Battery - This is a battery of different assessments

                                   3. Assessments - a collection of single tests that can be compiled into a battery 

                                   Imaged attached..

                              • 12. Re: ERD for Athlete Assessments
                                philmodjunk

                                     You changed more than the names. Before you had a one to many relationship from the second table to the third. Now you have many to one and the fields used as match fields have also been changed. Please note that I was not suggesting that you make such a change. As I understand it, you have many measurements to record for a given Assessement 9using your latest post when I use this name) and each of these would be a different record in Assessments.

                                • 13. Re: ERD for Athlete Assessments
                                  LeftyKJ

                                       ahhh. Sorry I set it up as you explained in the previous post.  Just re-read it and thats just how you were imagining it.  I put it back, I think how it should be with The athlete parent to the battery and the battery parent to the assessment.  the score is in the assessment table and not the battery.

                                  • 14. Re: ERD for Athlete Assessments
                                    LeftyKJ

                                         To recap because I changed the names all around. Statements are:

                                         1. An athlete can have many batteries

                                         2. A battery can contain many assessments

                                         3. An assessment is a single measureable event

                                          

                                          

                                    1 2 Previous Next