1 Reply Latest reply on Nov 6, 2013 8:44 AM by philmodjunk

    Battery of tests Table set up

    tomo17

      Title

      Battery of tests Table set up

      Post

           I am after some advice about what would be the best set up for a database that records a range of fitness testing. I have previously used a table that had all of the tests as fields.  Each record was based on a player and the date that the test was recorded.  Not all tests were conducted on the same day

           RecordID, PlayerID, Date, Height, Weight, 2km TT, 20m sprint, VJ etc

           This created problems when I wanted to search for results for only one of the tests.  I would get all of the records created irrespective of whether that test was conducted.

           I am considering a different approach where I have a test type field that the user chooses from a drop down menu what test the results at that time are for

           eg

           RecordID, PlayerID, Date, Test Type, Trial 1, Trial 2, cBest

           This second approach would mean that I would use a portal based on a Player layout when I wanted to look at a specific test for an individual e.g. all the 2km TT for Player X.

           A downside of this approach is that I wouldn't be able to import data that has been reported on a excel spreadsheet nor would I be able to export as an excel.

            

           I would appreciate other people's advice or experiences in identifying what approach would be best.

        • 1. Re: Battery of tests Table set up
          philmodjunk

               There's more than one way to set up your tables depending on how much variation exists between one type of test and another. If all are very similar, you might use this data model:

               Player------<Tests

               Player::__pkPlayerID = Tests::_fkPlayerID

               A field in Tests would identify the type of test taken.

               A more complex approach might look like this:

               Player-----<Tests----<2kmTT
                                         -----<20MSprint

               In other words, a different related table for each type of test would record the test results. That method would allow you to record very different types of results in each of the different test result tables.

               

                    A downside of this approach is that I wouldn't be able to import data that has been reported on a excel spreadsheet nor would I be able to export as an excel.

               Though the import process would be more complex, with either approach, you should still be able to import and export your data to/from Excel.