Jump to content
  • entries
    18
  • comments
    150
  • views
    4,836

Handicap Tracker

Sign in to follow this  
CarlSpackler

2,441 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.

Sign in to follow this  


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!

 

Share this comment


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.

Share this comment


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. 

Share this comment


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.

Share this comment


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!

Share this comment


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

Share this comment


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.

Share this comment


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!).

Share this comment


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

Share this comment


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

Share this comment


Link to comment
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.

Share this comment


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

Share this comment


Link to comment

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. :-)

Share this comment


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.



×
×
  • 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...