Golf Score database

[JR Taylor]

Hey all,



Novice user here so please share any and all thoughts.

Personal project of mine is to create a database for my golf scores performance so I can do some critical analysis, as well as have some historcal notes when I go to play a round.

I've researched database design and have come up with the following layout. It may require some knowledge of golf for a full understanding, but my main question right now is: Am I missing anything? Are my relationships adequate or do I need to relate more information?

I've attached a screenshot but here is a text explanation of the tables:

Courses - houses course information
Nines - simply tells me how many sets of 9-holes the course has and names each (front, back; north,south..etc) (most courses have 18, but some only have 9 and some have more)
Tees - helps distinguish difficulty based on the tees being played
Holes - houses hold information like distance, par, etc
Rounds - I could play more than 1 round in a day
Players - Might include my friends at some point in the future
Scores - this is my main results table and the main point of my question - do I need additional relationships to all the other tables or are the relationships indicated in my screenshot enough?

I greatly appreciate your help with this. Also, I would be open to advice on data entry, although I got ahead of myself and already made a post on the 'forms' section of this forum.

[rpeare]

if you're still working on this...

Individual holes do not have a slope rating do they? I had always thought slope was related to the course as a whole (bogey rating - course rating) * 5.381
What is your hope with this data structure? Are you hoping, say, with a course that has 27 holes to account for 3 possible 18 hole combinations (6 possible ratings if the order of the 9's is important)?
As far as your scores go... you shouldn't need fairway oppty/result those should only be relevant for par 4/5 holes. You should only need to indicate if the fairway on a given hole had been hit.
Similarly, your greens in regulation and sand saves would be the same, a simple yes/no to indicate whether the green had been hit or not or the sand save had been achieved.

I have other questions etc but only if you're still working on it.

[JR Taylor]

Hey rpeare,

Yes I'm still working on it. I paused efforts until I received a response to this post, so thank you!

To answer your questions; No, individual holes don't have a slope rating, that is done (typically) on an 18 hole basis, but your speculation is correct, that a course with 27 holes, having 3 sets of 9, may have different 18-hole slopes and ratings. For example, A+B is 70.1, B+C is 71.2, A+C is 70.5. It's not super-important but I thought if I could solve for this in my database structure, I would.

As for fairway's, you're correct, I shouldn't need need a flag, but I also thought it might be important for me to be able to report on % of fairways hit when attempted. If, for instance, the hole is 310 yards, I'll be going for the green, and if my drive roles through the green into the rough, I didn't hit the fairway...but I also wasn't going for it. The flag, although redundant in most cases, was a simple solution. I'm open to ideas otherwise though.

The GIR flag was meant to help paint a GIR % picture. If my drive is 40 yards left in the woods, maybe I don't have a shot at the green. I wanted to be able to see how often I hit the green in total, as well as how often I hit it when I was going for it (instead of chipping out into the fairway).

I'm still learning SQL. Consider me a novice in that arena, currently working myself throgh www.sqlcourse.com. If you know of any readings that may assist with that aspect, or the creation of forms in Access, it would be greatly appreciated.

Let me know your thoughts after reading my response, as well as any additional questions I can answer.

JR

if you're still working on this...

Individual holes do not have a slope rating do they? I had always thought slope was related to the course as a whole (bogey rating - course rating) * 5.381
What is your hope with this data structure? Are you hoping, say, with a course that has 27 holes to account for 3 possible 18 hole combinations (6 possible ratings if the order of the 9's is important)?
As far as your scores go... you shouldn't need fairway oppty/result those should only be relevant for par 4/5 holes. You should only need to indicate if the fairway on a given hole had been hit.
Similarly, your greens in regulation and sand saves would be the same, a simple yes/no to indicate whether the green had been hit or not or the sand save had been achieved.

I have other questions etc but only if you're still working on it.

[ssanfu]

I want to start off by saying "You know more about Access than I do about Golf".
To me, a Tee and a Hole are the same thing, so I combined the tables.

The tables "Courses", "Nines" and "Holes (Tees)" look logically correct.

The "thing" that you are tracking (in my view) are strokes. You wanted to, at some point, be able to include friends. Your current table structure won't allow you to track (?) four-somes.

So I tried a different structure.
Attachment 20129
I did leave some field out of the table "Scores" - I was tired of typing.

To me, Scores is your main table.

For forms, I see:
a form to add Rounds,
a form to add Players,
a form to add Courses with a sub form to add Nines
a form with Nines and a sub form to add Holes
A form to add Scores (strokes).

How does this look to you?

My $0.02........

[rpeare]

I play golf so this project has stuck in my mind more than most. I would like to know what type of analysis you intend to do. I don't think it's useful to do anything with the slope/rating, particularly if you're dealing with courses that have 27 holes you could, potentially, have 6 different slope ratings (if the order you play the 9's in is important) and, realistically it doesn't affect anything about your scoring analysis (at least as far as I can tell).

For instance are you going to examine your performance on a particular hole on a particular golf course? Or are you going to do something like examine your performance on all par 4's regardless of course?

I'm not convinced, in this case, that a fully normalized structure is actually going to be that beneficial to you and I would disagree slightly with ssnafu in that your ROUND is your 'main' table, the individual hole scores would be your sub table.

[JR Taylor]

I wish I still had my old spreadsheet/workbook so I could show you the kinds of analysis I had built within it. Alas, a crashed external hard drive, without backup, means I lost it (among other things).

Both of your assumptions are correct. I won't do much with the slope/rating if we're being honest. Maybe I could just store it for reference on the course table.

As for other analysis, I'd like to be able to see (but not limited to the following list):

How I'm performing accross all par 3's, par 4's par 5's (ie. avg 3.2 accross all par 3's in the last 20 rounds...etc)
Fairways hit %
Fairway tendency (left, right, etc)
GIR %
Approach tendency (left, right, over, short, etc)
Up and down %
Sand save %

It'd be nice to be able to get specific about a course I might be playing soon.

Like being able to see that on hole # 1 I've been right of the fair way on my drive 40% of the time
Hole # 2 I've missed the green left 75% of the time and never gotten up and down

These are just some examples; some are really high-level, others are very specific and direct.



I've made a change because I've given more thought to the story I'd be telling as I enter data. Take a look at the new screenshot and follow along:



Player (me) played a round of 18 holes on 4/1/15 at course X on the Black tees in the rain. (This covers the players and rounds tables)

Here are the performance metrics for each hole played. (This covers the Scores table)

I think Scores is my main table and rounds is a support table. (as are the rest)



Thoughts about this setup? maybe I don't need a 'course table' at all if I'm not worried about slope and rating

I play golf so this project has stuck in my mind more than most. I would like to know what type of analysis you intend to do. I don't think it's useful to do anything with the slope/rating, particularly if you're dealing with courses that have 27 holes you could, potentially, have 6 different slope ratings (if the order you play the 9's in is important) and, realistically it doesn't affect anything about your scoring analysis (at least as far as I can tell).

For instance are you going to examine your performance on a particular hole on a particular golf course? Or are you going to do something like examine your performance on all par 4's regardless of course?

I'm not convinced, in this case, that a fully normalized structure is actually going to be that beneficial to you and I would disagree slightly with ssnafu in that your ROUND is your 'main' table, the individual hole scores would be your sub table.

[JR Taylor]

The more I think about it, the more I am thinking: Players don't play holes of golf...(they do, but) it's more accurate to play a ROUND of golf.

[rpeare]

Here's a sample database to get your golf courses 9's information in. You can use the same methodology to do your actual data entry for your rounds

JR_Taylor.zip

Your ROUND information would be:
(including a round identifier autonumber)
1. Golf course ID
2. Round Date
3. Weather Conditions

Your NINES information would be
(including a round nine identifer autonumber)
FK (foreign key) to your ROUND table
FK to your NINE table (the table I have stored as tblGolfNine)
This would give you a baseline for which holes you would be entering data against.

Your ROUND PARTICIPANT information would be
(including a round participant identifier autonumber)
1. FK to your participant (the table I have stored as tblPerson)
2. FK (foreign key) to ROUND table

Your SCORING information would be
(including a round scoring identifier autonumber)
1. FK to your hole information table (the table I have stored as tblGolfNineHole)
2. FK to your ROUND PARTICIPANT table
This is where you have to decide how much data you're going to collect based on your posts this is close to how I would do it (these are the fields you'd actually have to do data entry on PER HOLE):
a. Score
b. Putts
c. Strokes to Green
d. Fairway attempted (default to 'Y' or 'Yes' for anything non par 4, only mark 'No' if person went for the green)
e. Fairway Result (FK to result table i.e. 1 Hit, 2 Right, 3 Left, 4 Long, 5 Short etc only if fairway attempted is YES)
f. Approach Result (FK to same result table used in fairway result)
g. Bunker (yes or no)
h. Save (yes or no, only if bunker is yes)

GIR is not whether or not you were going for it, greens in regulation is whether or not you hit the green in par - 2 shots (1 for a par 3, 2 for a par 4, 3 for a par5) the greens in regulation method you're suggesting doesn't follow this so you may have to do something similar to the fairway, however, this gets crazy because let's say you're 200 yards out on a par 5 after your first shot, but you put the ball over the green (you have missed what you were aiming for) this gives you a false GIR statistic. Now let's say you duff a chip and are still not on, how do you record 2 missed greens on a single hole, etc. I would just record whether or not you hit the green if you did it in par - 2 shots or not.

So this would leave you with 8 data points per hole per player which is pretty hefty

[JR Taylor]

Thanks man! I'm reviewing and may need some time to digest everything you've said and created. I'll reply as soon as I have done so.

[wrightstork]

I hope even though this is an older post that I can ask a question. How do you handle it when you have the total scores on some of the rounds, but not the individual hole scores? Maybe they were lost over time or whatever the reason. The db design I see here doesn't seem to account for that.

[rpeare]

fake the hole scores to make the total come out :P The database works on the premise you have the scorecard, if you're trying to reconstruct an old round you'd be guessing no matter what you did. If you were just using the totals to calculate a handicap then fudging the scores to make the total come out would work just fine.

[wrightstork]

fake the hole scores to make the total come out :P The database works on the premise you have the scorecard, if you're trying to reconstruct an old round you'd be guessing no matter what you did. If you were just using the totals to calculate a handicap then fudging the scores to make the total come out would work just fine.

I agree. I guess the answer is to use a special HoleID with the name "N/A" for the holes in a round where only the total is available so that it is apparent that it is a place holder.