hi,
i'm trying to develop a golf scorecard database (and front end app).

i have the following tables:
players(PLAYERID, playername, etc...)
courses(COURSEID, coursename, etc...)
holes(HOLEID, holepar, holelength, holecourseid[fk])
rounds(ROUNDID, rounddate, roundtotal, courseid[fk], playerid[fk])

trouble is, i also want to score each hole individually on each round. i am thinking i would need another table (round_by_hole) which would require the details of each hole from HOLES table, i.e.:

SELECT * FROM holes h, courses c WHERE c.courseid = h.holecourseid

in addition to needing all the details from the holes table, i will also need additional details, such as score (on this hole), hit_fairway, no_of_putts, etc..

not sure how to design/implement this. is this an example of a 1 to 1 relationship (between holes and round_by_hole), even though it has another FK reference to the courses table?
would i have to implement this as a stored procedure, so that when a new round is started, it fires off a piece of code to populate 18 rows in the course_by_hole table as well a new row in the rounds table?

thanks for any help...

jingo_man

>[QUOTE][SIZE=1]Originally posted by jingo_man
>hi,
>i'm trying to develop a golf scorecard database (and front end app).

>in addition to needing all the details from the holes table, i will also >need additional details, such as score (on this hole), hit_fairway, >no_of_putts, etc..
I have added some details to this Data Model on my Database Answers web site which you might find interesting :-
http://www.databaseanswers.com/data_...s_physical.htm

Barry Williams
Principal Consultant
Database Answers

I would be inclined to create a table something like: player_hole(playerid (fk), holeid (fk), roundid (fk), hit_fairway, no_of_puts) with (playerid, holeid, roundid) being a candidate key. Depending on your database engine, I might use a surrogate key to reduce the lookup time, since I suspect that you'll be using these rows quite a bit.

-PatP

I have added some details to this Data Model on my Database Answers web site which you might find interesting :-
http://www.databaseanswers.com/data_...s_physical.htm

Barry Williams
Principal Consultant
Database Answers [/SIZE][/QUOTE]



Hi Barry,

Thanks for your reply...

From your design, and the response below yours, the method used is to link the "_by_hole" table to the player id and the course id. when you are filling in the round details though, will this populate some of the fields automatically for you or not?

For example, i would like to select the player(s) in the round and the course that you are playing at. from this, it would auto-generate the next form which has the 18 holes listed for the round with par and length values, etc, etc... I can't see how this linking would offer the above capability?!? or would i have to place a stored procedure on the database/form to do this? (not ideal!!)

i am currently designing this in access, and when adding a new course in detail view, you expand the cross-referenced table and it shows all underlying holes (18) for that course. i was expecting something similar for the "_by_hole" table... note that i may not stick with access, it is purely whilst i am still modifying the design...

many thanks again...

jingo_man

I think you can get the "linking" you are looking for from a simple join.

-PatP