Design Question

[Joanne]

I want to create a database to track my golf game.

I have a Table called Course Info, with CourseID as autonumber primary
key

I think I need to create a table for each hole on the course to keep
the info regarding the distance, par etc of the hole, and also to hold
my score for the hole, including approach shots, putts, greens etc.
In this table I have HoleID as autonumber primary key.

I cannot for the life of me to figure out how to relate the tables.
I thought to use a secondary key in each Hole Info table to tie to the
Course info table, but don't know how to do it. Or perhaps I should
related courseID from the Course information table to a field called
course name in each hole table.

I want the whole app tied together so that I can create a score sheet
the way I want it, and of course, to create percentages etc to see how
well (or not) I am doing. And I want to be able to do this for each
course I golf on.

Maybe I am making this to hard and I should use one table for all 18
holes but it seems to me that this type of approach was pretty much
frowned upon when I took the access class 3 or 4 years ago.

I am pretty rusty and need a little nudge to get going forward on my
project. Your help is very much appreciated.

Golf on
Joanne

 

[Steve Schapel]

Joanne

You need all the Hole information for all the Holes for all the Courses
in one table. I'm sure this approach would not have been frowned upon
in your Access class. Having a separate table for each hole would
definitely be frowned upon. The fields would be something like this...

HoleID
CourseID
HoleNumber
Distance
Par
.. other hole-specific data

You would not keep your performance information in this table...
assuming, that is, that you intend to play any given Courcse more than
once. This needs a separate table, something like this...

PerformanceID
DatePlayed
HoleID
ApproachShots
Putts
Excuses
... etc

This way, you have a one-to-many relationship between Course and Hole,
and a one-to-many relationship between Hole and Performance. You will
be able to get your score sheet and statistics working properly with
this approach.

 

[tina]

most Access classes focus on the mechanics of the software, not proper data
normalization, so i wouldn't base decisions on how to structure the
tables/relationships on that.

you definitely should *not* create a separate table for each hole. suggest
the following three tables: tblCourses, tblCourseHoles, and tblScores.

tblCourses
CourseID (primary key)
CourseName
any other data that describes the *course*, as an entity.

tblCourseHoles
HoleID (primary key)
HoleNumberOrName (i don't know anything about golf, sorry!)
CourseID (foreign key from tblCourses)
Distance
Par
any other data that describes the *hole*, as an entity.

tblScores
ScoreID (primary key)
ScoreDate
HoleID (foreign key from tblCourseHoles)
ScoreType (data entry would be "approach shots", "putts", "greens", etc)
Score
any other data that describes the *score*, as an entity.

there is a one-to-many relationship between tblCourses (parent) and
tblCourseHoles (child). one course has many holes, but each hole is on only
one course. tblCourses will hold one record for each course that you play
at. tblCourseHoles will hold one record for each hole at each course you
play at. so if CourseA has 18 holes, CourseB has 18 holes, and CourseC has
12 holes, then tblCourses will have 3 records, and tblHoles will have 50
records, each one related to one of the three records in tblCourses.

there is a one-to-many relationship between tblCourseHoles (parent) and
tblScores (child). one hole may have many scores, but each score belongs to
only one hole. each time (date) that you a particular hole on a particular
course, you'll enter a record in tblScores to store the details of that
play. if it's possible to have more than one type of score for the same play
of the same hole, you'll enter one record for each ScoreType that you have a
score for. (hope that wasn't too confusing - again, my ignorance of golf.)

hth

 

[PC Datasheet]

TblCourse
CourseID
CourseName
ParFront9
Parback9

TblHole
HoleID
CourseID
Length
Par

TblScore
ScoreID
DatePlayed
CourseID
HoleID
Score

TblTypeOfShot
TypeOfShotID
TypeOfShot

TblShot
ShotID
ScoreID
TypeOfShotID
NumOfShots

Set up a form/subform to record Course and Holes info. Base the main form on
TblCourse and subform on TblHole. Set up a form/subform to record your
rounds of golf. Base the main form on TblScore. Use synchronized comboboxes
to enter CourseID and HoleID. The CourseID combobox should be based on
TblCourse and the HoleID combobox should be based on TblHole. Base the
subform on TblShot. Use a combobox based on TblTypeOfShot to enter
TypeOfShotID in subform.

 

[Joanne]

Thank you all for the great design instructions. I am printing them
and will incorporate the information into my app.
Do you think it will improve my gaime ; -)
Joanne

 

[PC Datasheet]

You would probably have more of a chance of improving your game by starting
to use a superball!

Steve
PC Datasheet