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.

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.

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

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.

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.

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 */

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.

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.

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.

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

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.