Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    help with database design

    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

  2. #2
    Join Date
    Apr 2003
    Location
    London, England
    Posts
    42

    Re: help with database design

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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579

    Re: help with database design

    Originally posted by jingo_man
    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
    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

  4. #4
    Join Date
    Mar 2004
    Posts
    4

    Question Re: help with database design

    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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I think you can get the "linking" you are looking for from a simple join.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •