7 Replies Latest reply on Nov 23, 2014 2:38 PM by JorgeVillalba

    Table Relationship setup - Golf Scoring

    Bazz

      Title

      Table Relationship setup - Golf Scoring

      Post

           Hi All,

           I'm trying to create a golf scoring relational database in Fm Pro12

           I'm planning to use the following tables: Players, Scores, Courses and Holes with the Scores table forming the main 'join' table for the input of scoring data by course and by hole.

           I'm having difficulty in trying to design/understand the relationships between the Courses table and the Holes table especially as a single golf course can really be represented as four different courses when the different tee configurations are considered. (For non-golfers a similar example would be relating a particular horse racing venue to a number of different race distances (or tracks) at that venue.)

           Do I need to create a separate (sub??) table for each different tee configuration? In other words If I want to include 25 golf courses in my database, do I need to create 100 sub tables to represent 4 potentially different layouts for each course?

           Hope this makes sense...and thanks in advance,

           Bazz

            

            

            

        • 1. Re: Table Relationship setup - Golf Scoring
          philmodjunk

               especially as a single golf course can really be represented as four different courses when the different tee configurations are considered.

               While I don't golf traditionally (I disc golf) , I've been on a course where I saw a "womans tee" and  "mens tee" many years ago. Is that what you mean by different "Tee configurations"? Wouldn't that be specific to the Hole instead of the entire course?

               Either way, you could include a second pair of "tee configuration" match fields for either the course, the hole or both as well as in the Scores table so that you can look at your data for a given tee configuration as well as the course or the hole.

          • 2. Re: Table Relationship setup - Golf Scoring
            Bazz

                 Hi Phil, 

                 thanks very much for the reply. Yes, a men's tee & ladies tee is very similar to what I mean and yes they would be specific to the hole.

                 I've started to go down the track of just creating a different hole id for each course,  eg creating a "mens course" and a "ladies course" ......probably not the best way to do it but it should suffice as I only want to enter a few courses anyway.

                 I've created the attached relationship graph.  I believe I have a novice grasp of the parent / child concept and the relevant pk / fk conventions etc and also portal information.  However I'm struggling big time with which layout to actually enter the final hole score information into. 

                 I don't know if I should be entering the score into the 'Scores' table or whether I need to use the 'ScoreEntry' table. I tried using the 'Score Entry' table by joining it between the 'Holes' and 'Scores' tables (since removed) but I found I couldn't access the Payer table and other tables via that relationship. Do I need to have a more direct relationship between (e.g.) the 'Players' table and the 'Score Entry' table or do I need some form of 'TO' to link the 'Players' table to the 'ScoreEntry' table??    ........I thought I understood the standard customers/invoices/ (join)invoicelineitems /products concept but I now realise I struggle with the relational concept when I get down to 6 tables as per the attached. Pleeeeease help crying smiley

                 Cheers,

                 Bazz

            • 3. Re: Table Relationship setup - Golf Scoring
              philmodjunk

                   Well you have to enter your score into the scores table, but I think the real question is how to design a layout to make entering a score quick and easy.

                   Could this be something that you plan to use with FM GO on an iPad or iPhone?

                   Given that set of relationships, how will you specify the Tee configuration when you go to record a score?

                   My original suggestion was to use two pairs of match fields such as:

                   Holes::__pkHoleID = Scores::_fkHoleID AND
                   Holes::Tee = Scores::Tee

                   This would enable you to use a portal to Scores on your Holes layout to record scores. You'd format Holes::Tee with a value list of Tee configurations so that you'd select a value in it then record your score. (And a script could be set up to select the same configuration for all holes of the golf round.)

              • 4. Re: Table Relationship setup - Golf Scoring
                Bazz

                     Hi again Phil,

                     Yes, my project is designed for use with FM Go on an IPhone. It needs to be finished for 20 users next Friday. The good news is that thank's to your help it's almost complete and it works quite well.

                     But………it desperately needs one missing component, hence I'm back, asking your assistance again :)

                     The user enters scores into the Scores table via a 'Scores' layout. The problem I have is that a user can enter a score more than once for the same player on the same hole. (Incorrect course selection is not a problem as that  field is locked down by the Admin).

                     The user selects the 'player' field; then the 'hole' field'; then the 'score' field and then hits the "enter score" button on the layout which creates a new record ready for the next score entry. I'm guessing that I need some form of script on the "enter score" button to prevent a user (accidentally) entering a repeat score record?  The user does however need the ability to alter a score record and does need the ability  to enter the scores out of ' hole ' order' if required. An example of this would be starting the round on the 10th hole instead of the 1st hole.

                     Can you assist directly or point me to an appropriate thread please?

                     Cheers,

                     Bazz

                      

                      

                      

                      

                • 5. Re: Table Relationship setup - Golf Scoring
                  FentonJones

                        

                       I created a golf database, in 2008. Because of the years, personal injury, and the fact that I've never played golf myself :-/, I cannot remember the details; not to mention they had a lot of tweaks added, due to where they played, etc..

                       But the basics seem very similar to what you've done; I guess golf is golf. I say, and vaguely remember that I used a Timestamp set when they "began," so they could not change if it became too late, tested using Get (CurrentTimeStamp) Looked like:

                       Get ( CurrentTimeStamp ) - reg_Scores~RegID::TimeStamp ≤ reg_Events~EventID::_gTimeLimit_Scores or Get ( CurrentPrivilegeSetName ) ≠ "User"

                  • 6. Re: Table Relationship setup - Golf Scoring
                    philmodjunk

                         It looks like something that you can easily trap for with a hidden field that uses both an auto-enter calculation and a unique values validation option on that same field to pop up an error message if a user records a second score for the same player and hole. The following is probably too simple, but should work as an example from which to generalize:

                         Set up this auto-enter calculaiton:

                         HoleID & " " & PlayerID   //you may need to combine other data such as a course id and/or a date....

                         Select the unique values validation and put in a custom validation message telling the user not to enter this data twice for the same hole.

                    • 7. Re: Table Relationship setup - Golf Scoring
                      JorgeVillalba

                      Hi, I am trying to do something similar to what you did... could you help me?