Jump to content
  • entries
    29
  • comments
    263
  • views
    9,483

CarlSpackler

3,610 views

I've struggled with what to blog, especially in a golf context. I know more about IT, programming, and databases than I do about golf. It finally hit me. I started writing a handicap tracking system. Knowing that I was moving to Florida this year, I didn't renew my membership to the golf association / GHIN, so I lost my official handicap. In my new job, they use the MySQL database system extensively, so I needed to learn it. I am a seasoned veteran in MS SQL Server, so I decided to dive in. Is there a better way to learn a database system than to write something golf related? I think not. Actually, calculating a handicap presents some interesting challenges, so it was a good project to put MySQL through its paces.

The system is pretty basic right now. Since this isn't a formal project, so I'm winging it as I go. Basically, it has four tables, course, course tees, player, and scores.

2016-08-18 08_50_15-MySQL Workbench.png

Course has basic information about a course like it's name, address, etc. Sadly, I added a field called "active" that will be set to false if a course shuts down. If a facility has multiple courses or rotations, they will be entered multiple times with the name of the facility followed by a dash and the course name (i.e. - Oakland Hills - South, Oakland Hills - North). Course tees has info about the tee boxes such as color, slope, rating. When I looked up some courses that I have played, I noticed the the USGA has different slope/rating information based on gender, so I added a gender field. Next there is the Player table which holds the players name, email, home course and whether they are active. It also has placeholders for handicap index, rounds used in the calculation, rounds posted. More on these in a minute. Finally, there is the Scores table. This holds players posted rounds by date, the course/tees played with copy of the slope and rating, score and adjusted (ESC) score. There is a calculated field showing the differential for the round. For now, I've combined nine hole rounds manually and entered, but I am thinking of adding a Score9H table to hold them with a relationship table where a player could link and submit 2 9 hole rounds into the scores table. I need to research any regulations that would impact this.

Next I wrote some basic stored procedures to insert courses/tees, players and scores. Then I wrote one to copy slope and rating from the course tee table to the scores table. The idea here is that scores will reflect the slope rating at the time the round was played. It's denormalizing the data, but if the slope/rating of the course changes, it will only impact future rounds, not ones that have already been played. I'm curious how GHIN handles this. I would think that you would need to calculate based on what the slope and rating were at the time the round was played. 

Finally, there is the key to the system, the calculate handicap stored procedure. It is the most complex portion of the system to date. For the specified player, it grabs the last 20 rounds and puts them in a temp table ordered by calculated differential. I had a major issue here that I will describe later. It counts the rounds to see how many it needs to use for the calculation. I put the calculation for this into a function. I didn't need to since this is foreseeably the only place this would be done, but I wanted to use the function feature as part of my MySQL education. It did help clean up the stored proc though. At any rate, once it determines how many rounds to use based the number of rounds available, it uses a cursor to loop through that many times and creates a running total of the best N indexes that is then divided by the number of rounds processed times .96 to come up with the players index. It stores the calculated index in the players record along with the date it was calculated, how many rounds were used, etc. If there are less than five rounds posted, it assigns the default index (40.4 for women and 36.4 for men). Then a wrote another procedure that loops through active players in the system and calculates there handicap that can be scheduled to run on the 1st and 15th. 

So that is a lot of technical mumbo jumbo that you may or may not find entertaining. I did that to hopefully inspire some comments on how I go about this as well get ideas for possible improvements. The next phase is to write a web app to maintain the data. I am hoping to make this mobile friendly, but I don't foresee (at this time at least) writing a mobile app. There are many many many apps out there already, so I really don't see a market for it. I may do it just for the hell of it. I might create a Github page and make this an open source project. I haven't done that, so it would be an interesting experience.

As far as my learning experience, I found MySQL to lack some of the features that I have become used to in SQL server. I guess I am spoiled. For instance, I discovered that you can't provide default values for input params in stored procs. Having this in SQL Server allows me call it without specifying a value for every param. I did a little work around where I set a default value for that parameter in the sp with an if statement. I also found out that when I read the calculated differential field in the scores table, it came back with a 0 value. It was working when I first started using it, but then it stopped working at some point along the way. It drove me crazy, but I ended up recalculating the differential in the sp that calculates handicap index. I found the intellisense (automatic command completion) feature to be very quirky. Sometimes it worked. Sometimes it didn't. This exercise left me yearning to use SQL Server.

Feel free to comment on any issues you see with my logic or any other features that you think would be nice to add.

16 Comments


Recommended Comments

That's pretty neat.  I'm in IT and I wrote a handicap tracker in python.   It keeps track of the courses and slope/rating and will take the last 20 in the data file that I write and calculate the handicap.   It's great for keeping track since I cannot put in rounds where I am single anymore.

I have plans to expand it to a stats tracker where I can say whether I hit the fairway, or went out of play.   Or hit the green and give percentages on what the tougher shots seem to be, but that'll probably be down the road.  Useful if you add club information because then you can pull accuracy stats for each club and overall numbers for each hole.

I don't know much about databases (I support DBAs, but am only an end user of DBs) so this is completely different to how I would approach it.  Good luck!

 

Link to comment

I'm an database guy in IT as well, and I found this very interesting.  I'm currently developing a google spreadsheet to track handicaps for me and my buddies, since we are all just starting to take the game seriously, don't have official handicaps, but still like to play games on the course.  As of now, I've just figured out how to maintain all the hole-by-hole scores, count up the rounds, and find the differentials to include.  You've now given me some ideas on how to incorporate course info so I don't have to enter it for each time someone plays that course.

This is really cool (for a fellow nerd like me), and am interested to see how it progresses.

Link to comment
1 hour ago, imsys0042 said:

That's pretty neat.  I'm in IT and I wrote a handicap tracker in python.   It keeps track of the courses and slope/rating and will take the last 20 in the data file that I write and calculate the handicap.   It's great for keeping track since I cannot put in rounds where I am single anymore.

I have plans to expand it to a stats tracker where I can say whether I hit the fairway, or went out of play.   Or hit the green and give percentages on what the tougher shots seem to be, but that'll probably be down the road.  Useful if you add club information because then you can pull accuracy stats for each club and overall numbers for each hole.

I don't know much about databases (I support DBAs, but am only an end user of DBs) so this is completely different to how I would approach it.  Good luck!

I always prefer to keep the business rules in a database instead at the application layer. It gives me the freedom to write the interface in any, and as many, environments as I want. I could make a web page in PHP or .NET, a .NET Windows app, etc. Also, it's easier to port to a new environment. For instance, I implemented an ERP system years ago. We put all the reporting logic into database stored procedures and used Crystal Reports as tool to display the information. Years later, when SQL Server Reporting Services matured, we were able to easily port the reports over with minimal testing because the logic was already verified in the database. 

58 minutes ago, bweiss711 said:

I'm an database guy in IT as well, and I found this very interesting.  I'm currently developing a google spreadsheet to track handicaps for me and my buddies, since we are all just starting to take the game seriously, don't have official handicaps, but still like to play games on the course.  As of now, I've just figured out how to maintain all the hole-by-hole scores, count up the rounds, and find the differentials to include.  You've now given me some ideas on how to incorporate course info so I don't have to enter it for each time someone plays that course.

This is really cool (for a fellow nerd like me), and am interested to see how it progresses.

It is definitely a fun little project. I already have a spreadsheet in Excel that does this. I wrote it for our golf league years ago. It leveraged a lot of macros, but it worked. 

Link to comment

This is all cool stuff that I love playing around with.

A few thoughts for Carl on nine hole score entries, courses usually have ratings for front and back. Some actually vary considerably which can and will effect the calculations of differentials used. Nine hole scores are treated the same as 18 hole rounds, the only difference,the Ghin system will combine nine hole rounds when entered in the timeframe of the last 20 rounds. Scorecards generally do not indicate the ratings for front and back, but you can find them at the USGA Database Course Ratings One last little tidbit which very few understand is using "T" in calculations. What exactly does it do and how is it implemented? I'm only mentioning it as a "Teaser" for those truly interested. I will give you a hint, it produces the "R" on a players handicap card sometimes and that's another kept secret the USGA had for many years.

I have maintained records of over 150 different players for 11 years at my club which creates numerous database calculations on a board range of concepts. I break out individual stats along with comparisons by groups. I also can show stats based on our daily pin positions which indicate a noticeably difference in individual and group scoring history. I gave some thought to tracking weather condition, but never developed the data.

I'm presently using a web base application which also has a mobile application for users. I push out announcements along with news in relation to our golf activities and provide live scoring on days played. Users can view personal and group info for any and all days played in our events as far back as 2005. One of these days I plan to localize the stats by combining all years together which should produce an abundance of interesting stats.

Link to comment
Quote

Then I wrote one to copy slope and rating from the course tee table to the scores table. The idea here is that scores will reflect the slope rating at the time the round was played. It's denormalizing the data, but if the slope/rating of the course changes, it will only impact future rounds, not ones that have already been played. I'm curious how GHIN handles this. 

I would guess that GHIN calculates the differential at the time of score input and stores only the differential.   As you pointed out, storing the slope/rating is not the best use of a relational database.  I think just doing the calc at the time of input and storing the result should meet your needs and allow future updates to slope/rating to work as intended. 

Good luck!

Link to comment
On 8/20/2016 at 7:49 AM, Club Rat said:

This is all cool stuff that I love playing around with.

A few thoughts for Carl on nine hole score entries, courses usually have ratings for front and back. Some actually vary considerably which can and will effect the calculations of differentials used. Nine hole scores are treated the same as 18 hole rounds, the only difference,the Ghin system will combine nine hole rounds when entered in the timeframe of the last 20 rounds. Scorecards generally do not indicate the ratings for front and back, but you can find them at the USGA Database Course Ratings One last little tidbit which very few understand is using "T" in calculations. What exactly does it do and how is it implemented? I'm only mentioning it as a "Teaser" for those truly interested. I will give you a hint, it produces the "R" on a players handicap card sometimes and that's another kept secret the USGA had for many years.

I have maintained records of over 150 different players for 11 years at my club which creates numerous database calculations on a board range of concepts. I break out individual stats along with comparisons by groups. I also can show stats based on our daily pin positions which indicate a noticeably difference in individual and group scoring history. I gave some thought to tracking weather condition, but never developed the data.

I'm presently using a web base application which also has a mobile application for users. I push out announcements along with news in relation to our golf activities and provide live scoring on days played. Users can view personal and group info for any and all days played in our events as far back as 2005. One of these days I plan to localize the stats by combining all years together which should produce an abundance of interesting stats.

For now I have combined my 9 hole rounds manually and did so by pairing rounds on the same course in sequence. Probably not perfect, but it got me in. Right now I am working on the web interface. I'm trying some new stuff (for me) in C#. I've been more of a database guy, but I'm working to enhance my skills in .NET. There are some nice features in VS2015. I will bite though. What is the T in the calculations. 

On 8/22/2016 at 9:56 AM, bmartin461 said:

I would guess that GHIN calculates the differential at the time of score input and stores only the differential.   As you pointed out, storing the slope/rating is not the best use of a relational database.  I think just doing the calc at the time of input and storing the result should meet your needs and allow future updates to slope/rating to work as intended. 

Good luck!

They probably do. I opted to copy the values over to keep them in case the calculations seemed wrong. 

Here are a few screenshots to show how it is shaping up. Next is putting a datagrid control showing the scores on the Player screen and then a page to add a score. Not bad for a few days work.

2016-08-23 21_00_21-http___localhost_63411_Forms_Courses.aspx - Internet Explorer.png2016-08-23 20_59_43-http___localhost_63411_Forms_PlayerList.aspx - Internet Explorer.png

Link to comment

Ghin provides golfers with information about their scores used in the current calculation period (recent 20 rounds used to compute the index) and the score history of recent rounds played and their 12 month handicap index history.

Quote

T = Score Type (H - Home, A - Away, T - Tournament, P - Penalty,
C - Combined)
U - Used for USGA Handicap Index® Computation
Low H.I. = Lowest Handicap Index over the last 12 months.

When you sign into your account it also indicates your "trend" movement based on recent scores.

Now, the letters used when posting scores show the type, which are home and away which are most common, the letter T when the player competes in either local, regional or national sanctioned events and the dreaded P if a golf committee discovers a player is not posting when applicable.

And lastly, on the bottom of a players card, they show "Two Lowest Eligible Tournament Scores"

Here's where they come into play. In the event the trend is upward, Ghin will only permit an index to rise a maximum number in one period, which is indicated with the letter "R" after the index number. This calculation is determined by the average of the differentials of the "Two Lowest Eligible Tournament Scores" compared to the average of the 10 lowest differentials. When the differences is larger than 3, the letter "R" or Reduced Index will come into play.

Basically, the Ghin will only allow a Players Index to increase up to three or in some cases four with higher handicaps over the Trend. An example: a players Index is 7.9 Their next revision they would not be able to rise above 10.9

When a players trend is in a quick upward movement, either they are playing injured or have swing problems or they are intentionally sandbagging.

  • Upvote 1
Link to comment

Love it!

I liked the little discussion you threw in about denormalizing the data model to capture the slope/rating at the time the round was played, as that's something I don't think I'd ever thought through. I guess your only other option would have been to assign a date of the rating in the course_tees table into the scores table? That would certainly make the calculation of differential a bit more complex to do the join between two tables based on the correct rating (based on round date in the scores table to a date NO LATER than that round in the course_tees). Not sure I know how to do that in a JOIN command.

Anyway, great idea for topic. I think lots of us enjoy geeking out with data modeling, but are afraid to admit it. I'm also a fan of MS SQL Server, but I also felt looked down upon by Oracle or Sybase guys at work. Those of us supporting MS SQL Server were the pariahs. Curious if that division (sometimes just teasing, sometimes quite serious when choosing a platform for a new project) still exists out there in the workplace generally (OT, I know!).

  • Upvote 1
Link to comment

Ha! Don't let them beat you up. SQL Server is far superior IMO. I started as an Oracle DBA back in the early 90s. Oracle's programming environment has some slick features, but otherwise, it's a dinosaur. I'm glad that MS is becoming more open. It's surprisingly easy for me to integrate with MySQL. I was picturing much frustration, but it's been pretty easy once I developed the helper and business classes. Here is a screenshot of me connecting to the business class through either a query string and sp.

2016-08-24 07_57_20-HCAP - Microsoft Visual Studio.png

Link to comment

Here is another pic. I added a score gridview to the player screen that is activated when you select a player. But why not throw in a chart too!

Yes... Those are my scores... :8)

2016-08-24 11_11_22-http___localhost_63411_Forms_PlayerList.aspx - Internet Explorer.png

Link to comment
  • Moderator
13 hours ago, Club Rat said:

Here's where they come into play. In the event the trend is upward, Ghin will only permit an index to rise a maximum number in one period, which is indicated with the letter "R" after the index number. This calculation is determined by the average of the differentials of the "Two Lowest Eligible Tournament Scores" compared to the average of the 10 lowest differentials. When the differences is larger than 3, the letter "R" or Reduced Index will come into play.

This isn't what the USGA Handicap Manual describes.  The Tournament score evaluation has two steps.  First, the second-lowest tournament different is compared to the Handicap Index.  If the difference is 3.0 or more, continue to the second step.  Second step is to AVERAGE the lowest two Tournament differentials, and compare the average to the Handicap Index.  Using that number, along with the number of eligible Tournament scores posted in the last 12 months, you go to the Tournament Handicap Reduction Table to determine how much the Handicap Index is reduced, if at all.  If the Index is reduced, its marked with an R.  This is all here under Rule 10-3

http://www.usga.org/content/usga/home-page/Handicapping/handicap-manual.html#!rule-14389

I looked and didn't see any limit on the amount that an Index can change in a single period, but a rapid change should probably signal a review by the Handicap Committee

To respond to something else that @bmartin461 mentioned, Ghin stores everything about each round posted, including course, score type, slope, rating, score, date, and even the date and time it was posted.  They store this for a long period back, but you can usually only access the last 12 months of data on your phone app, less on the Ghin website.  However, handicap administrators can see years and years of history if they choose.

Link to comment

The biggest addition to date has been the Add Score screen. Seems like a nice thing to have, right? I added a button the header of the scores grid that will open a page where you select the player (auto selected based on which player was selected on the previous screen), course, tee, score, adjusted score, date played, and score type which has been added to the scores table. The gender radio box is a filter mechanism to limit what shows up in tee selection.

2016-08-29 11_33_30-http___localhost_63411_Forms_PlayerList.aspx - Internet Explorer.png

2016-08-29 11_33_50-http___localhost_63411_Forms_AddScore.aspx - Internet Explorer.png

Link to comment
  • Administrator

You can copy and paste them into new entries. Just keep them in the same order…

Just go there and click "Add Entry" or something like that. If you see "Add comment" it's not a new entry. :-)

Link to comment
Guest
Add a comment...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



  • Want to join this community?

    We'd love to have you!

    Sign Up
  • Blog Entries

  • Posts

    • This nails it. And if he read this he’d think..’yeah…another minion.’ 
    • In the video, one of them had #7 as their favorite hole 🙂 Then sadly he liked #9 Here are my course reviews... Lac La Belle - 6.0/10 Visually this course is good. The mix of fescue, bunkers, and marshy areas outline the holes nicely. There is a lot of target golf going on here. Some of the holes were a bit wonky, like hole #2. Also, the 600 yard par 5 was kind of bad. I am just glad that the fescue was not thick and tall, it was very playable from. If not, this course would be unbearable. The massive putting green with basically a putt putt course was really fun. In the end, it's a good course, but I would not enjoy playing this course a lot. Just too much target golf.  Course Layout - 5/10, Course Condition - 8/10, Visual - 8/10, Clubhouse - N/A Lawsonia - 7.0/10 I really enjoyed this course. It has a unique style that isn’t around my area. Holes 1 thru 8 were good. For me, a bit too many blind tee shots. Holes 9-18 were more open from the tee. Luckily, there wasn’t much in the terms areas the ball would end up unplayable on the holes with blind tee shots. If you aimed over the mounds, and hit a decent shot you would have a shot at the green. There was a good variety of holes. This course was really fun to play.  Course Layout - 7/10, Course Condition - 8/10, Visual - 6/10, Clubhouse - N/A Mammoth Dunes - 8.0/10 A visually stunning course. It is much more of a friendly course than Sand Valley due to the green complexes having more areas to feed the ball into the green on errant shots. Each hole was like a problem to solve off the tee even though there was wide landing areas. I really enjoyed playing this course. The first tee shot of the day was really awesome teeing off from an elevated tee over looking over the expanse of the property. It really gives you a sense of the grand scale of the course. The bins of water on the course were nice since  you have to walk the course.  Course Layout - 8/10, Course Condition - 8/10, Visual - 8/10, Clubhouse - N/A Sand Valley - 8.5/10 A visually stunning golf course. The green complexes are tough with a premium on hitting the flat areas or areas with backstops. Balls would routinely run off the edges where severe slopes were located at. When you miss the green, you usually had a few options to play. Mostly, the high risk through the air or try to putt it to a spot on the green for a longer putt. I think the back nine is much stronger than the front nine. Overall, I prefer the test of this course.  Course Layout - 9/10, Course Condition - 8/10, Visual - 8/10, Clubhouse - N/A Bonus - Sandbox - 10/10 This little gem of a course is just a joy to play. Each green complex is unique and with the variety of pin placements and tee boxes changing daily, each time playing is slightly different. I thoroughly looked forward to starting every day at the Sandbox. 
    • I guess it's easier for some people to think that their enemy is a cult rather than a bunch of informed individuals. Saves one the bother of introspection. 🙃
    • I am watching that thread.  Just in wait & see mode
  • Today's Birthdays

    1. Armando Araujo
      Armando Araujo
      (46 years old)
    2. chris3putt
      chris3putt
      (54 years old)
    3. CrazyHorsePete
      CrazyHorsePete
      (34 years old)
    4. DHak20
      DHak20
      (44 years old)
×
×
  • Create New...

Important Information

Welcome to TST! Signing up is free, and you'll see fewer ads and can talk with fellow golf enthusiasts! By using TST, you agree to our Terms of Use, our Privacy Policy, and our Guidelines.

The popup will be closed in 10 seconds...