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