1 2 Previous Next 22 Replies Latest reply on Aug 29, 2013 12:11 PM by GuyStevens

    Designing an archery statistics database

    TimWilson

      Title

      Designing an archery statistics database

      Post

           Hi everyone,
            
           I’m getting back into FileMaker after a few years away based on a desire to create a system for tracking my archery scores and results. I think it’s a fairly complicated database model, and I’ve run into a couple snags thinking it through. There’s a distinct possibility that I’m overthinking it. :-) Any suggestions would be appreciated. I’ll try to explain the basics and minimize the archery-specific terms.
            
      Extremely short version: I need to track individual statistics for an archer over time down to the scores of individual arrows including the distance shot for each arrow. There are many different types of archery rounds to be scored, and the rounds vary widely. My questions are listed at the bottom of this post.
            
           Here’s how I would describe the relationships:
             
      •           A Database user (archer) may own many types of bows and many types of arrows.
      •      
      •           An archery facility may have many archery ranges.
      •      
      •           An archery range may have many rounds.
      •      
      •           An round may have many ends.
      •      
      •           An end may have many shots.
      •      
      •           A user may have many games, and a round may have many games associated with it.
            
      Some definitions:
             
      •           Archery facilities may have a combination of indoor and outdoor ranges consisting of a variety of targets at a variety of distances.
      •      
      •           A “round” consists of a series of targets at certain distances. Sometimes you shoot all the same distance for all targets, and sometimes each target is at a different distance.
      •      
      •           To the extent that any archery range could have their targets set at almost any distance, the number of possible “rounds” could be huge.
      •      
      •           Arrows are shot in sets or “ends” with a certain number of arrows each. Usually all arrows in a given end are shot at the target from the same distance, and occasionally the arrows in an end are shot at the target from different distances.
      •      
      •           A “game” in my model is intended to resolve the many-to-many relationship between users and rounds.
            
      Complications:
             
      •           Each arrow is scored individually with totals calculated for each end and for the overall round.
      •      
      •           Different types of rounds use different target faces.
      •      
      •           I’d like to track the distance too so that I can generate statistics for average scoring at a given distance and for a given type of target face.
      •      
      •           Different types of rounds use different numbers of arrows, ends, and arrows/end.
            
      Two specific examples:
            
           One of the simplest examples is a “300 Round” consisting of 12 ends of 5 arrows each fired from 20 yards at a small target with possible scores of X, 5, 4, or 0 for each arrow. An X is the innermost part of the 5-ring and counts 5 points too. The number of X’s are counted separately as a tie breaker. The maximum possible score is 300 with 60 X’s.
            
           A more complicated round would be a 28-target “Field” round consisting of 28 ends of 4 arrows each fired from a variety of distances between 20 feet and 80 yards with possible scores of X, 5, 4, 3, or 0 for each arrow. An X counts for 5 and should be counted separately as a tie breaker. The maximum possible score is 560 with 112 X’s. All 4 arrows in a given end are *usually* fired from the same distance, but a few ends/round have each of the 4 arrows fired from a different distance at the same target.
            
           So if nothing else, anyone who has read this far has probably learned something about the sport of target archery.
            
           Here’s the ER diagram as I have envisioned it so far. 
            
            
      Questions:
             
      1.           Let’s say that my local archery facility has a range called “Loop B” with targets set for a 28-target Field Round. Each of the 112 arrows can be pre-defined to be fired at a specific distance. I may shoot this round many times per month and want to track my performance arrow by arrow over time. What’s the best way to create a template of sorts so that I can create a new game in the database with the entire Field round on Loop B ready to score?
      2.      
      3.           It seems inefficient, but would the best approach be to create a separate table for each round I could possibly shoot? For example, I would have a separate table for all the Field rounds on Loop B at my local facility. There would be another table for all the 300 rounds at my local facility. There would be a lot of redundancy in this, and it would take a lot of work to create an entire table every time I create a different round in the database. (Note, there would be at almost 20 different rounds possible at just my local archery facility. Other facilities would add many more tables to the database.) I also don’t see how I could track an individual arrow score *and* the distance from which it was fired in a table like that.
           If you’ve read this far, THANK YOU! Any suggestions would be greatly appreciated.

      X-Tracker_ERD.png

        • 1. Re: Designing an archery statistics database
          GuyStevens

               Hey Tim,

               Guy Stevens here from youtube.

               I saw this post a little while ago when you posted it and was a little alarmed by the sheer size of your questions and your project that I decided to let someone else handle it for fear of being sucked into a massive project that would take ages to finish. I don't have that much time to spend on someone elses database.

               But I got your message on youtube, searched for this and saw that sadly no-one has helped you yet.

               So let's give this a try.

               A problem that I saw is that you have created a relationship in your database between Ranges and FacilityRanges which is a Table occurrence (the word you were looking for) of the Facilities table and you related the Facility name to the Range name.

               That's never going to work because those are totally different values.

               And it's also a bad idea to make relationships based on names.

               There is an additional problem where you need to make the distinction between fixed data and data you are going to work with.

               Some fixed data are your facilities and your Ranges.

               They are there, they have some data attached to them that is basically always going to stay the same.

               I'm not an archer and haven't fully understood the connection between the ranges and the Rounds but you need to decide how that relationship works.

               Are there a certain amount of rounds and can they be played at any range? In other words, are rounds completely unrelated to the ranges?

               Or are rounds specifically linked to certain ranges?

               If that's the case a simple conditional value list will do the trick.

          Conditional value list:

               Here is the idea. You are on a layout based on your rounds table. On that table you are going to select a facility and a Range.
               The idea is that you store the ID's of these records as a foreign key and that you relate the tables so that from the rounds table you can get any data you want from both the facility and the Range table.

               This is exactly what you already did with your Facilities and ranges tables.

               The organization of your relationship dialogue could be done a little better if you would use the anchor buoy method:
          http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/
               But maybe that's a little difficult to understand.

               In your Rounds table you would have following fields:

               - Id - A number field set to auto enter a serial number. As I'm sure you have done in your other tables and already have in this rounds table.
               - Name  -  a text field
               - Description  -  A text field.
               - FacilityIdFk  -  A number field where we will store the ID of the Facility
               - RangeIdFk  -  A number field where we will store the ID of the Range

               A way to enter these foreign key ID's is by using a value list where the first field is the ID and the second field is the name.

               If you want a little trick to hide the ID in these dropdowns you can look at one of my video's:

          Dropdown without ID:

          http://www.youtube.com/watch?v=Sg4vyifqZEM

          Conditional value list

               So now let's try to understand the conditional value list part.

               The first part is easy. In the rounds table the FacilityIdFk field should have a simple value list that shows all facilities but stores the ID.

               So a value list that has the ID field from the Facility table as a first field and the Name of that table as a second field. I gues you already have all of that.

               Then the tricky part is in the Ranges. You want to see only the ranges that exist for the selected Facility.
               The facility has been selected so there is an ID in the FacilityIdFk field in the Rounds table.
               It's this field that needs to be related to the FacilitiesIdFk field in the Ranges table (or at least in a Table Occurrence of this table.

               The idea is simple and you could test this using a portal. If you select Facility "1" then you want to have ranges in your Dropdown where the FacilityidFk of the Range is also "1".

               So really simple. You create a new TO (Table Occurrence) of your Ranges and call it Ranges_Rounds.

               Then you relate the Rounds::FacilityIdFk to the Ranges_Rounds::FacilityIdFk

               Do you understand that that way, in the Ranges_Rounds TO you will only be able to see the Ranges for the Facilities that have the Same ID as the facility you selected?

               The only trick here is that in your value list options you need to set it so that you show only values related to the Rounds table. Because that's the one that contains the Facility ID.

               I hope this helps, if you keep struggling, let me know.

               There is also a way that you could show all three of these tables in portals on one layout.

               Check this video for an example:

          http://www.youtube.com/watch?v=VJqA03X33DQ

          • 2. Re: Designing an archery statistics database
            TimWilson

                 Thanks, Guy. I'll read your reply in detail and work through it. I'm not surprised that my initial post was a bit overwhelming for readers. There's so much domain-specific stuff in it, I felt like I needed to provide additional context. Plus, I suppose, writing it out helps clarify things in my own head. Thanks again for your reply. It might be another late night tonight. :-)

            • 3. Re: Designing an archery statistics database
              TimWilson

                   Guy,

                   You've hit on the central problem, I think. I'm not sure how the "rounds" fit into the overall structure of the tables. At the risk of confusing things further, I'm going to share slightly more detail about how the rounds work. I think it might help you or anyone else understand the situation.

                   As I described in my video, an archery facility may have a number of ranges associated with it. Let's say the facility "Acme Archery" has three ranges: "Loop A", "Loop B", and "Loop C". Typically, field archery loops have 14 or 28 targets, but let's say for the sake of simplicity that each of these loops has only two targets.

                   So here's the trick. There can be different kinds of round associated with those targets. In a typical "Field" round you shoot four arrows at each target, but you sometime move around so that each arrow is shot from a different distance. In my simple example, that's a total of eight arrows at the two targets. Let's say the distances for the eight arrows are like this: Target #1: 30 yds, 30 yds, 30 yds, 30yds and Target #2: 60 yds, 55 yds, 50 yds, 45 yds. I want to track each shot separately so I can accumulate statistics over time related to my performance at different distances.

                   Now, using the same physical targets, I can shoot a different round called a "Hunter" round. Same as before, four arrows/target, but the distances change even more often. So in my Hunter round my distances might be: Target #1: 41 yds, 37 yds, 33 yds, 29 yds and Target #2 53 yds, 53 yds, 47 yds, 47 yds.

                   In other words, Acme Archery might have a Loop A with a Hunter round and a Field round associated with it. You're shooting at the same physical targets in each case, but from different distances.

                   One of my biggest points of confusion, from a database design standpoint, is how to store the records that describe a particular round so that when I shoot one I can record the individual arrow scores. If I were describing it as a object-oriented programming problem I think the comparable question would be, "How do I store the properties of a Round class so I can store them after creating a new instance of the class?".

                   My latest thinking is to create a text field in my "Rounds" table that includes a CR-separated list of distances which I can draw on via a script to create the empty records in the "Ends" and "Shots" tables I need when I create a new record in the "UserRounds" table. This is super confusing, I know. I'm just not sure if I'm in the ballpark. 

                   I'll attach a screenshot of my current database Relationships if that helps. Thanks again, to anyone who can offer a suggestion.

                   -Tim

              • 4. Re: Designing an archery statistics database
                TimWilson

                     Based on my thoughts about associated my scores with a particular "UserRound" in the database, I wonder if a relationship graph like the following makes more sense. I'm not sure if it makes sense for a table intended to resolve a Many-to-Many relationship ("UserRounds" in my case) should ever be a parent to another table.

                      

                • 5. Re: Designing an archery statistics database
                  GuyStevens

                       Let's not think about scroring yet. I think that's going to be something to deal with later.

                       First let's focus on the first steps. How do we relate Facilities, Ranges, Rounds and Distances?

                       You talk about a hunter round and a Field round, are those uniquely related to a range? Or could you do a Hunter round on any range?

                       And what about the distances? Do they vary?

                       Do you enter the distance the moment you score? Or is this something that is determined in advance?

                       Because there is some fixed data that doesn't change (like the Facility and Range data) but then when you start scoring you might import distances into a new scoring table to be able to enter the scores.

                       And also don't think about creating one text field with a bunch of distances entered in there.

                       That's not proper database design; That's going to get you into a lot of trouble later on.

                       So let's take it one step at a time.

                  • 6. Re: Designing an archery statistics database
                    GuyStevens

                         Here's a really simple example of the conditional value list.

                         I hope that make it clearer:

                    https://dl.dropboxusercontent.com/u/18099008/Demo_Files_FMP12/Archery.fmp12

                    • 7. Re: Designing an archery statistics database
                      TimWilson

                           I've gone back to the drawing board a bit on the design after watching some more of Guy's videos and reviewing the anchor buoy method of describing relationships. There are three key elements (and base tables) in the design as I see it.

                           Users -- the archers who use the system and track their statistics in the database

                           Rounds -- the different combinations of targets at various distances that make up a single, scored game

                           Games -- the specific games which contain data about each shot at various targets and distances (rounds) for each archer in the database (users)

                           Here's the updated relationships graph reflecting my latest thinking. I realize that additional table occurrences will be needed to make it all work.

                            

                            

                            

                      • 8. Re: Designing an archery statistics database
                        TimWilson

                             Here's a picture that shows some examples of typical target faces. This is a key concept. Any given "round" uses the same target face throughout. Here's the key for the attached image:

                               
                        1.           "Animal" round -- Outdoor; 14 or 28 targets; 1, 2, or 3 arrows per target depending on how well you do. This is the most complicated one to score
                        2.      
                        3.           "Field" round -- Outdoor; 14 or 28 targets; 4 arrows per target at various pre-set distances between 20 feet and 80 yards
                        4.      
                        5.           "Hunter" round -- Outdoor; 14 or 28 targets, 4 arrows per target; very similar to field round
                        6.      
                        7.           "300" round -- Indoor; 12 sets of 5 arrows all from 20 yards; 5, 4, or 0 pts per arrow
                        8.      
                        9.           "FITA" round -- Outdoor; 3 dozen arrows each at 90m, 70m, 50m, and 30m; 0-10 points per arrow

                             They rotate the targets at my local archery club ("Facility"). In any given week, the Animal, Field, or Hunter target faces get mounted on the targets on a given "Range". The distances for each arrow are pre-determined according to which target face is mounted. Here are two examples of different "Rounds" at the same facility (e.g., "Rapids Archery") and range (e.g., "Loop A 1-28"):

                             Rapids Archery Loop A 1-28 Field

                             Rapids Archery Loop A 1-28 Hunter

                             Any given "Round" has a pre-determined shooting distance for each arrow. In order to track complete statistics I need to know my score and distance for each arrow. That means that the distance for each shot must be (1) stored in advance in the database for each round I define or (2) entered at the time of the shot. Option #2 would be very inconvenient.

                             That brings me back to one of my fundamental questions that I mentioned previously:

                             How to you store the specific distances in the database for each "Round" so I can record scores for each arrow (at the appropriate distance) when I enter the data for a "Game"?

                        • 9. Re: Designing an archery statistics database
                          GuyStevens

                               Hi Tim,

                               You wrote:

                               

                                    That brings me back to one of my fundamental questions that I mentioned previously:

                               

                                    How to you store the specific distances in the database for each "Round" so I can record scores for each arrow (at the appropriate distance) when I enter the data for a "Game"?

                               That doesn't seem like a big problem to me, just a related table with distance and score per arrow.

                               My question is, can you already define this distance in advance?

                               If you do a "Field" round in facility X on Range X can you then already in advance enter the distance that every single arrow will be shot from?

                               If not then you will have to enter it when you enter the score.

                               I think you are going in the right direction, just need to add a related table to rounds where you enter the distance (and any additional info like the target number or ... for every target / shot.

                               If you can enter this information in advance then when you actually go shooting you just enter the user name, select the facility, range and round. And that imports all the distances in a related table and then you just enter the score.

                               If distances are not pre-entered or variable then you enter them at the moment you enter your score.

                                

                          • 10. Re: Designing an archery statistics database
                            GuyStevens

                                 P.S. For me most of this is still chinese, I'm having trouble with the link between range and round. But I'm asuming that will be up to you to figure that one out.

                            • 11. Re: Designing an archery statistics database
                              TimWilson

                                   You said:

                                   

                                        If you can enter this information in advance then when you actually go shooting you just enter the user name, select the facility, range and round. And that imports all the distances in a related table and then you just enter the score.

                              Yes, that's correct. I know all the distances for each arrow in a given round in advance. It's just the mechanism for importing from the related table that was puzzling me. That's why in my previous thinking I was imaging that I would store the distances CR-separated in a text field in the Rounds table. Then when I create a new record in what I'm now calling the "Games" table, I would utilize a script to parse the corresponding record's distance field in the "Rounds" table and create the relevant records in what will probably be a "Shots" table. Then I would fill in the Shots table during or after my "game" to store the record of my performance. I don't see another way to do it unless I create a separate table for every type of Round I define. That can't be right.

                                   As for the "Chinese" part :-)… I'll take one more stab at it with a simplified analogy.

                                   Think of the archery "facility" like the table in your buddy's basement where you have a weekly card game. The "range" would be the deck of cards. (Some card games require a special card deck other than the standard 52-card version.) The "round" would be the specific card game you play with a particular deck at a particular table. The "game" would be the specific instance of the card game.

                                   Example:

                                   Facility: Guy's basement card table

                                   Range: Standard 52-card deck (no Jokers)

                                   Round: No-limit Texas Hold 'Em

                                   Game: Details about that night's game

                                   Maybe that makes sense. Thanks again for the help. I'm going to put some time in on this over the weekend and see how far I can go.

                              • 12. Re: Designing an archery statistics database
                                GuyStevens

                                     I have added some very crude and basic functionality of how I would see this:

                                https://dl.dropboxusercontent.com/u/18099008/Demo_Files_FMP12/Archery.fmp12

                                     It's extremely basic but might show you some interesting possibilities.

                                     The idea is that you go to the Rounds layout and enter all the distances for the targets. I made a simple version of this.

                                     Then you go to the Score layout, Select a round and a Shooter.

                                     Then import the distances and start scoring.

                                     On this scoring layout you could also have the ability to select a facility and then a range from a conditional value list, and then a round from another conditional value list.

                                     But since that's already happening on the Rounds layout I decided to use another technique here.

                                     Just to give you some ideas.

                                • 13. Re: Designing an archery statistics database
                                  TimWilson

                                       I've kept working on this and have made some good progress. My latest effort is directed at populating the "shots" table so I can enter the scores after shooting my round. Guy, I considered your suggestion from earlier, but finally convinced myself that the easiest approach would be to define all of the distances for all the shots in a particular round in a large text field all at once. Then I plan to parse that text field to pre-populate the "shots" and "ends" table. It may be the long way around, but I don't see another way to "instantiate" a new round when I got to the range to shoot for a score.

                                       Attached is a screenshot of the script in its current form. It parses the "Distances" field in the "Rounds" table and creates the correct number of records in the "Ends" and "Shots" tables. That's cool. What it's not doing properly is setting the values in the various fields based on the contents of the "Distances" field. (Check out the highlighted portion specifically.) Frankly, I have no evidence that the field is even being parsed correctly. But at least the looping logic is working right! :-)

                                       Any suggestions on how to create those records and set the relevants fields. The script is still in a simplified state, by the way. I need to add additional foreign keys, etc., but I wanted to get a proof of concept working first.

                                       If anyone would like to have a more detailed look at the script, I've uploaded a copy of the database to my dropbox here.

                                  • 14. Re: Designing an archery statistics database
                                    TimWilson

                                         Here's what the "Rounds" table looks like with the "Distances" field I'm trying to parse.

                                    1 2 Previous Next