Need some advice on setting up my tables correctly

[Fronzizzle]

Hi all,



I apologize if this is somewhat easy or common. I used to be really good with Access, but it's been 10+ years since I used it regularly and I'm a bit rusty.

I'm setting up a database that is strictly for my wife and I to use to track our golf scores and stats. The idea is to enter the round information, then be able to pull stats and (hopefully) track our improvement over a given time frame, look up course-specific stats and that sort of thing.

I figured I would start easy and make a table that lists all of the courses we play (and be able to enter new ones as we play new places). However, I'm already stuck because not every course is the same par or has the same amount of par 3's and par 5's (which matters for stats such as Fairways Hit). Further complicating things is that sometimes we might play just 9 holes, and the 9 could be the Front 9 or the Back 9. My initial go looked something like this:

CourseID CourseName Front9Par Front9Par3Amount Front9Par5Amount Back9Par Back9Par3Amount Back9Par5Amount TotalPar TotalPar3Amount TotalPar5Amount



From a query standpoint, that looks messy to me. I'm not even sure if I need the last 3 columns as they could be calculated from adding the other columns (Front9Par + Back9Par = TotalPar)

Then I thought that maybe I'd have three entries for each course, something like:

1 Great Oaks - Front 36 2 2
2 Great Oaks - Back 35 2 1
3 Great Oaks - Total 71 4 3

But that also looks a little messy, and if I want to pull course stats I'm going to want to see all the times I played the front, back and whole thing and I think this makes it difficult.

I feel like the right answer involves some sort of sub table, but I'm drawing a blank on how to best design this. Advice?

[madpiet]

Hi all,

I apologize if this is somewhat easy or common. I used to be really good with Access, but it's been 10+ years since I used it regularly and I'm a bit rusty.

I'm setting up a database that is strictly for my wife and I to use to track our golf scores and stats. The idea is to enter the round information, then be able to pull stats and (hopefully) track our improvement over a given time frame, look up course-specific stats and that sort of thing.

I figured I would start easy and make a table that lists all of the courses we play (and be able to enter new ones as we play new places). However, I'm already stuck because not every course is the same par or has the same amount of par 3's and par 5's (which matters for stats such as Fairways Hit). Further complicating things is that sometimes we might play just 9 holes, and the 9 could be the Front 9 or the Back 9. My initial go looked something like this:

CourseID CourseName Front9Par Front9Par3Amount Front9Par5Amount Back9Par Back9Par3Amount Back9Par5Amount TotalPar TotalPar3Amount TotalPar5Amount



From a query standpoint, that looks messy to me. I'm not even sure if I need the last 3 columns as they could be calculated from adding the other columns (Front9Par + Back9Par = TotalPar)

Then I thought that maybe I'd have three entries for each course, something like:

1 Great Oaks - Front 36 2 2
2 Great Oaks - Back 35 2 1
3 Great Oaks - Total 71 4 3

But that also looks a little messy, and if I want to pull course stats I'm going to want to see all the times I played the front, back and whole thing and I think this makes it difficult.

I feel like the right answer involves some sort of sub table, but I'm drawing a blank on how to best design this. Advice?

For each course, you have some number of holes. Each hole has a different par.
(CourseID*, HoleID*, Par) *=Primary key

Games or rounds are something like (RoundID, CourseID, Date) and then that relates to Players (RoundID, PlayerID) --- Scores (RoundID, PlayerID, HoleId, score)

But to answer your question, you need a table of Holes (HoleID, CourseID, Par). then you just record the CourseID, HoleID, PlayerID, Score for each hole (basically in a subform).

[Micron]

As a scratch golfer (I scratch out my real score and put in what looks good) it's an interesting problem. If the "amount" fields are scores in a round they belong in their own table. If they're not scores, you should explain what they are. So possibly
- table for courses (name, location, slope, maybe your ranking, things that are attributes of the course. That could include par info and counts.
- table for rounds; linked to tblCourses via CourseIDfk > tblRoundsIDpk. This holds scores. Don't total a round in a table. Calculations are almost always done in forms/reports
- table for players; linked to tblRounds via RoundIDfk > PlayerIDpk

That's off the top of my head.

BTW, if you have to repeatedly write out those field names it will become quite tedious.

EDIT - BTW, not doing calculations in tables should mean not identifying eagle, birdie, etc. in any table. How you id those could be interesting, but again, done in form or report.

[madpiet]

As a scratch golfer (I scratch out my real score and put in what looks good) it's an interesting problem. If the "amount" fields are scores in a round they belong in their own table. If they're not scores, you should explain what they are. So possibly
- table for courses (name, location, slope, maybe your ranking, things that are attributes of the course. That could include par info and counts.
- table for rounds; linked to tblCourses via CourseIDfk > tblRoundsIDpk. This holds scores. Don't total a round in a table. Calculations are almost always done in forms/reports
- table for players; linked to tblRounds via RoundIDfk > PlayerIDpk

That's off the top of my head.

BTW, if you have to repeatedly write out those field names it will become quite tedious.

EDIT - BTW, not doing calculations in tables should mean not identifying eagle, birdie, etc. in any table. How you id those could be interesting, but again, done in form or report.

If you have par for each hole as a record, then this is stupid easy. You just join on (maybe) CourseID, and HoleID, then subtract par from your score. then you do something like a switch/case/if to determine whether it's bogey, par, birdie etc. (it's static, so just use a table.) Then its just a lookup/join at that point.