Designing an archery statistics database
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.
- 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.
- 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.
- 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?
- 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.