Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2009
    Posts
    12

    simple db schema question about golf.

    I want to create some tables for storing golf scores.
    I have one table for storing the info about one particular round. I want to associate score, putts and greens on each hole to each round played.
    My question is, should I create 3 tables that stores the hole# and the score for each score, putts, and greens. Or should I create one table that stores all three but add a scoretype which is a score, green or putt to determine which score is for what?

    Or I could create a table with 18 columns, one for each hole and put the score in it's respective column. But some columns would contain NULL values if that person didn't enter a score for that hole.

    My concern is that if I get a large amount of entries then every time someone submitted a round it would store 18 rows. That could add up to a very large table after awhile.

    Any advice is greatly appreciated. Thanks.

  2. #2
    Join Date
    Jan 2009
    Posts
    23
    I think you need first identify the entities in your scenario, such as people, holes, play round and so forth. next you need understand the relationships between those entities.
    people (id, name, ...)
    hole(id, number, position,...)
    play_round(id, peopleID, holeID, score,...)

    my golf knowledge is poor, so can't figure out all things for you.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i know golf fairly well, and can answer your questions, but so far the only real issue i think you raised is whether it's okay to have one row per hole, and the answer is yes, that's the better (normalized) design

    if the tables are properly indexed, mysql can handle millions of rows with no sweat at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2009
    Posts
    12
    Quote Originally Posted by chiefman
    I think you need first identify the entities in your scenario, such as people, holes, play round and so forth. next you need understand the relationships between those entities.
    people (id, name, ...)
    hole(id, number, position,...)
    play_round(id, peopleID, holeID, score,...)

    my golf knowledge is poor, so can't figure out all things for you.
    Thanks for the reply.

    I have that part figured out already. I just wasn't sure on how to create the relationship between the holes and the round.

  5. #5
    Join Date
    Jan 2009
    Posts
    12
    Quote Originally Posted by r937
    i know golf fairly well, and can answer your questions, but so far the only real issue i think you raised is whether it's okay to have one row per hole, and the answer is yes, that's the better (normalized) design

    if the tables are properly indexed, mysql can handle millions of rows with no sweat at all
    Awesome, that's what I wanted to hear.
    Now should I create a different table for each the score, putts, and greens or create a "holetype" that is used in the "hole" table that identifies each entry as a score, putt or green?

    Thanks for the reply.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    chiefman's play_round table looks wrong

    you'd need a table for rounds which identifies the person who played the round

    in other words, the round is unique to the person who played it

    then there would be another table, for the holes played in that round

    people (id, name)

    holes (number, par) /* holes 1 through 18 */

    rounds (id, peopleID, date)
    /*a people can play more than one round per day, hence rounds has a surrogate key */

    scores (round_id,hole_no,approach_shots,green_shots,total)
    /* yes total = approach + green, so it's not really necessary */
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2009
    Posts
    12
    Actually now that I saw your last reply I could store the putts, greens, and score all in one row in that holes table. But any given scores, putts, or greens could be NULL and at least one of those 3 would have to exist in order to create a "holes" row.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what do the terms "putts, greens, and score" mean to you?

    how are they different from my approach shots, green shots, and total?

    when you say "holes" row, i think you mean "scores"

    there are only 18 holes rows, period

    hole 1 is a par 3, hole 2 is a par 5, etc.

    the scores table is used to hold the score made on a specific hole in a specific round (by a specific person)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2009
    Posts
    12
    Quote Originally Posted by r937
    what do the terms "putts, greens, and score" mean to you?

    how are they different from my approach shots, green shots, and total?

    when you say "holes" row, i think you mean "scores"

    there are only 18 holes rows, period

    hole 1 is a par 3, hole 2 is a par 5, etc.

    the scores table is used to hold the score made on a specific hole in a specific round (by a specific person)
    putts is how many putts the user had on that specific hole #.
    greens is if the user hit the green on that hole #.
    and scores is how many shots the user had on that specific hole #.

    I guess my naming convention could use some work too.

    And yes I would also need a par column.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ss1289
    putts is how many putts the user had on that specific hole #.
    greens is if the user hit the green on that hole #.
    okay, i'm going to go a bit offtopic here...

    what if you hit the green with your tee shot, and your 2nd shot (which is a putt) rolls off the green and into the bunker

    your 3rd shot goes back up onto the green, and your 4th shot goes in

    so you had 2 greens and 2 putts for a total of 4? or 1 green and 3 putts? can you call your bunker shot a putt?

    the way i would do it, all of your shots are approach shots until you hit the green, and then all your shots after that are green shots, even if you've gone off the green

    i wonder how they do it in real golf tournaments...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2009
    Posts
    12
    Quote Originally Posted by r937
    okay, i'm going to go a bit offtopic here...

    what if you hit the green with your tee shot, and your 2nd shot (which is a putt) rolls off the green and into the bunker

    your 3rd shot goes back up onto the green, and your 4th shot goes in

    so you had 2 greens and 2 putts for a total of 4? or 1 green and 3 putts? can you call your bunker shot a putt?

    the way i would do it, all of your shots are approach shots until you hit the green, and then all your shots after that are green shots, even if you've gone off the green

    i wonder how they do it in real golf tournaments...
    Then it's a green in regulation or "green" in my case. Once you hit the green in 2 or less shots than par then it's a green regardless of how poor your play is after that. And it would be 2 putts, I think.

  12. #12
    Join Date
    Aug 2009
    Posts
    3

    Golf DB

    I've been working on a golf database design off & on for over a year now, and here's what I've got. First, being an avid golfer myself, let me give you my take on the terminology:

    SCORE: Total number of strokes (shots) the user had on that specific hole - from initial tee off, to the final stroke to put the ball into the hole.

    GREEN: Short for 'Green in Regulation'. On a par 3, GIR is 1. On a par 4, GIR is 2, and on a par 5, GIR is 3. USGA rules. Of course, if you can get on in less strokes, then that counts as GIR as well. ie. On a par 4, GIR is 2, but so is getting on the green in ONE stroke. Basically, you've achieved GIR if you have COME TO REST on the green in 2 or more strokes LESS than par. So, if it rolls off, too bad! Doesn't count.

    PUTTS: # of putts AFTER ball has COME TO REST on the green (putting surface). You can certainly use your putter to hit a ball while it is in the rough, but it does NOT count as a putt until the ball is ON the putting surface. Again, USGA rules. (On the putting surface, you could putt with your driver if you wanted, and it would count as a putt.)

    I'll give my 2 cents on database design in a separate post...

  13. #13
    Join Date
    Aug 2009
    Posts
    3

    Golf DB - Database Design

    Here's how I have designed my database. As chiefman pointed out, you need to start with creating individual entities, and then relate them. I've opted to go very normalized, using system-assigned keys (IDENTITY columns).

    ENTITIES:
    ---------

    GOLF CLUB: This table contains topical info on a golf club, like golf club name, address, phone, address, etc.

    GOLF_COURSE_NINE: This table simply contains a row for each set of NINE holes. i.e. 'Masterpiece Front 9', 'MasterPiece Back 9'. Some course only have 9 holes. Some have 27. So I wanted my application to use the lowest common denominator to construct a scorecard. I've got a foreign-key (FK) reference back to the golf club.

    GOLF_COURSE_TEE: My definition of a 'tee' is a distinct set of 9 holes from each of the different tee boxes on the course. So if you look at a scorecard, the 'black' row of yardages constitutes one 'tee'. To me, it was important to distinguish this from, say, the white tees, where the yardages and handicaps may be different, especially from the women's tees. Although, there are no yardages in this table, only a 'tee name', 'tee description', boolean value to indicate whether this tee is for men or women, and a precedence value so that I can show them in the correct order in my app. Have a FK reference back to the 'GOLF_COURSE_NINE' table.

    HOLE: This is the meat. This table contain one record for EVERY HOLE in the database. It had 'hole number', 'yardage', 'handicap' and 'par value', and also a FK reference back to the 'tee' to which it belongs. As an example, take hole #16 on a scorecard I'm looking at right now. Yes, it's a physcial hole on a golf course, but for the purposes of database design, I'm storing 3 distinct instances of the hole. There's hole #16 from the BLACK tees with a yardage of 376, hcp 2, par 4. There's also hole #16 from the WHITE tees, with a ydg of 356, hcp 2, par 4. And finally, there's hole #16 from the RED (women's) tees, with a ydg of 338, hcp 2, but a par FIVE. So especially with the par values potentially being different from tee to tee, you can see why splitting a physical hole into separate 'virtual' holes becomes important for calculating scores. The difference in yardage isn't as important, but for my application, I want to capture that knowledge, so when my brother brags that he shot a 41 last month, I can quickly see that (ah hah!) he was playing from the yellow tees! No wonder!

    ROUND_OF_GOLF: Just a table to store a record for a single round of golf. Currently, I define a round as being played by 1 or more players, but someone in this forum said it should be 1 round = 1 player, and I agree. I'll change that.

    ROUND_DETAIL: This table has 1 row for each hole played per golfer. It contains a FK reference to the 'HOLE' table (which gives us the par value), a FK reference to the golfer (but I'm going to remove this and store the user in the ROUND_OF_GOLF table), score, # of putts, 'HIT_FAIRWAY' (smallint to indicate whether user hit the fairway with their drive; does not apply to par 3s), 'DRIVING_DISTANCE' field (just in case someone wants to capture this), and 'COMMENTS', which I like to use to capture comments about how I played a particular hole.

    I'm not done yet. I wanted to still address the notion that a course can change over time. They change the Master's course in Atlanta every year, so a golfer's round of golf LAST YEAR only applies to LAST YEAR's golf course setup, with LAST YEAR'S yardages. Not sure if that's going too far, but if you think about it, if you play a course 2 years in a row, you don't use the same scorecard both years, do you? No, you use different ones, and they could have different yardages, slopes, handicaps, etc. Work in progess.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •