As I mentioned in Everything Ain’t a Nail, I’m putting together a little program to keep track of my golf league. It’s more difficult than I thought it would be, particularly the algorithms to compute the scores. As long as I’m spending so much energy on this, I might as well document my steps. I set up this category to do just that.

The first thing you need when building an app is a catchy name. Tenth Hole is the name of the league and in a flash of creative genius, I came up with Tenth Hole Tracker. A google search on that phrase turns up no hits, so I’m solid in the trademark department.

Here’s how I set up the tables

When I originally set up TblPlayers, Player was a String and was the key field. Why would I need an autonumber when I could use the player’s name? People won’t be changing their names, will they? In retrospect, that was a pretty stupid decision. First, of course people change their names. Like when women get married. Second, substitute golfers don’t write their first and last name on the score card. They just write their first name and they may write “sub” to let me know they’re not a regular. Now that I know a certain last name, I want to change it but it would be a major doing.

Also, a mistake could be made when entering the golfers’ names. Maybe I don’t notice the mistake for a few weeks, but want to change it when I do. But I have all these incorrect keys in my transactions table. What a pain.

I added the PlayerName field and changed Player to an autonumber field. It wouldn’t let me do it because I have data in the table already. So I had to copy the structure, make the change, then do an append query. A little renaming later, and I was in business. I was in the business of finding and fixing all the errors this changed caused. Relationships screwed up. SQL statements screwed up. VBA screwed up. I vow, from this moment forward, to ever use fake, contrived, meaningless, autonumber-esque key fields.

Other notes on the setup:

TblPlayers
I went with three email fields rather than a separate one-to-many table. This is a hard limit that could be unlimited if I had done the separate table. Sometimes I think it would be better if everything was unlimited, but I don’t know if it’s worth it.

The Sub field is a Boolean that’s true if the golfer isn’t a regular member of a team. I could get the same information by joining this table with TblTeams, which I probably should have done. I may delete that field.

TblCourses
The Holen fields hold the handicaps for those holes. That number is necessary to calculate the points. I added those fields when I started working on the points algorithm. Poor planning, I guess.

TblWeeks
Everything is based off a week number. I wanted to correspond the week number with a date, although I haven’t had cause to use it yet. Sometimes I underplan; sometimes I overplan.

TblScores
I started with a structure that had a Hole field and a Score field. I changed it to Hole1, Hole2, etc. In our league, we play nine holes. Never more, never less. I may regret that decision when I want to scale this to an 18 hole league, though.

By the way, I decided to keep the data in Access and build the UI in Excel. Next I’ll start showing the functions I’m writing to get the information I need. As always, comments welcome.