Table Design help

I am trying to make a golf scorecard.

I have a Course Details table that provides facts about the course,
using the CourseDetailsID as the PK.

My problem is entering in hole distance and par values for a given tee
box. For instance, players can play a course from the black, blue,
white, gold or red tee boxes. All of these can vary the yardage and
par for each hole.

I have built a table for the tee box colors with the TeeColorID as the
PK. I can enter the hole yardages for a given course with a given tee
box color. My problem is I want to restrict only one record for each
tee box color per course. That is to say, each course could have 5,
but no more than 5 different scorecards associated with it. Right now,
I can enter all the black tee box yardages I want for a course, but I
only want to be able to enter one record for the black tee color.

I hope this makes sense.

Thanks in advance for the help.

Troy

Re: Table Design help

"Phil Stanton" wrote in
news:0N-dnUuPTtG6xGTanZ2dnUVZ8qaqnZ2d@plusnet:

> 5 Tables
> 1 Clubs:- ClubID(PK), ClubName, ...Address, Type
> (Links, Hilly etc) etc etc. NoHoles
> 2 TeeColours:- TeeColourID(PK), TeeColour
> 3 Numbers:- NumberID(PK), Number. Probably only the
> numbers from 1 to 18 but possibly 1 to 36
>
Phil, what advantage would there be in creating table 3 over simply
setting a Validation rule on the column of between 1 and 18?



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Re: Table Design help

5 Tables
1 Clubs:- ClubID(PK), ClubName, ...Address, Type (Links, Hilly
etc) etc etc. NoHoles
2 TeeColours:- TeeColourID(PK), TeeColour
3 Numbers:- NumberID(PK), Number. Probably only the numbers
from 1 to 18 but possibly 1 to 36

Now the joining tables
4 JnClubTeeColours:- ClubID, TeeColourID as combined primary key.
Restricts each club to those tee colours played at that club
5 JnClubTeeColourNumber:- ClubID, TeeColourID NumberID as combined
primary key, then the bits you want like yardage, par, stroke index. This
again resticts you to the right course, the right tee colour and the correct
hole.
You will need a main form for the Club with a ComboBox to select the colour
and a subform with a ComboBox to select the whole number ant then the boxes
to fill in for distance, par. stroke index etc. Also may need to do a check
that the number of holes = NoHoles on the Club record

Have fun

Phil


wrote in message
news:20d8936e-e3c7-46ec-8f2c-a643c30ee4b7@s50g2000hsb.google groups.com...
>I am trying to make a golf scorecard.
>
> I have a Course Details table that provides facts about the course,
> using the CourseDetailsID as the PK.
>
> My problem is entering in hole distance and par values for a given tee
> box. For instance, players can play a course from the black, blue,
> white, gold or red tee boxes. All of these can vary the yardage and
> par for each hole.
>
> I have built a table for the tee box colors with the TeeColorID as the
> PK. I can enter the hole yardages for a given course with a given tee
> box color. My problem is I want to restrict only one record for each
> tee box color per course. That is to say, each course could have 5,
> but no more than 5 different scorecards associated with it. Right now,
> I can enter all the black tee box yardages I want for a course, but I
> only want to be able to enter one record for the black tee color.
>
> I hope this makes sense.
>
> Thanks in advance for the help.
>
> Troy

Re: Table Design help

troy_lee@comcast.net wrote:
> I am trying to make a golf scorecard.
>
> I have a Course Details table that provides facts about the course,
> using the CourseDetailsID as the PK.
>
> My problem is entering in hole distance and par values for a given tee
> box. For instance, players can play a course from the black, blue,
> white, gold or red tee boxes. All of these can vary the yardage and
> par for each hole.
>
> I have built a table for the tee box colors with the TeeColorID as the
> PK. I can enter the hole yardages for a given course with a given tee
> box color. My problem is I want to restrict only one record for each
> tee box color per course. That is to say, each course could have 5,
> but no more than 5 different scorecards associated with it. Right now,
> I can enter all the black tee box yardages I want for a course, but I
> only want to be able to enter one record for the black tee color.
>
> I hope this makes sense.

One option might be:

*CourseID
*HoleNbr
*TeeColorID
HoleYardage

* = PK


I built a golf course/scorecard capture/analysis system way back in '98.
Here's a screenshot of my career best (Robert Trent Jones golf trail course)
http://www.angelfire.com/linux/dfs0/good_round.png

Magic! Never got within 5 strokes of that score again.


> Thanks in advance for the help.
>
> Troy

Re: Table Design help

Hi Bob

Probably very little, but possibly prevents duplicate holes and makes it
easier to set up 9 hole or 12 hole courses.

Pinched the idea from a storage area database of mine and it works well.

Phil


"Bob Quintal" wrote in message
news:Xns9A78C6427BC25BQuintal@66.150.105.47...
> "Phil Stanton" wrote in
> news:0N-dnUuPTtG6xGTanZ2dnUVZ8qaqnZ2d@plusnet:
>
>> 5 Tables
>> 1 Clubs:- ClubID(PK), ClubName, ...Address, Type
>> (Links, Hilly etc) etc etc. NoHoles
>> 2 TeeColours:- TeeColourID(PK), TeeColour
>> 3 Numbers:- NumberID(PK), Number. Probably only the
>> numbers from 1 to 18 but possibly 1 to 36
>>
> Phil, what advantage would there be in creating table 3 over simply
> setting a Validation rule on the column of between 1 and 18?
>
>
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>