[RESOLVED] best way to store data for a golf round

[mystrymaster]

I need to store the data of a golf course and a round

so what I am wondering from all our database guys is what is the best way to normalise this data?

I need to store the info on 4-5 sets of tees and then a users information

my thoughts were to have a course table

then have a red,blue, black, white, gold tee table that is linked with the course id field however then I would have hole 1 - hole 18 fields in those databases is that really the only way?

I mean I guess I could store the info in one field with commas and then explode the data after that, would that be too much of a performance issue?

[dgray]

To track the information for a golf course, players, and rounds played you would need seven tables for a completely normalized solution. The tables are:

player

id number Surrogate key
first_name char
last_name char
address char
city char
state char
zip number
phone char

course

id number Surrogate key
name char Name of course
description char Description of course

hole Holes on a course

id number Surrogate key
hole number 1-18
par number 3-5
course_id number Foreign key of course to which this hole belongs.

tee

id number Surrogate key
tee_color_id number Foreign key of color of tee

yards number Distance to hole
hole_id number Hole to which this tee belongs

round

id number Surrogate key
player_id number Foreign key of player who played round
course_id number Foreign key of course that was played
date_played date

hole_score Score for an individual hole. Would have 1 record for each hole played.

id number Surrogate key
round_id number Foreign key of round during which this hole was played
hole_id number Foreign key of hole which was played
tee_id number Foreign key of tee the player shot from
score number Number of strokes.

tee_color

id number Surrogate key
color text red,blue, black, white, gold

You may think that this seems like overkill, but all the tables are required for a fully normalized solution. Denormalize as you see fit. Hope this helps.

[mystrymaster]

so then to also track rating and slope of the course simply add 2 more tables for them with the course ID and tee_id as the tie that binds so to speak

Now a question on this.. Wouldn't this cause a heavy load on the server when trying to get an average score for a user? or is that something that shoudl be stored in another table and updated whenever a new round is added?

[dgray]

I'm not a golfer, so I'm somewhat fuzzy about the meaning of track and slope. What kind of relationship is there between track, slope, course and tee? Is there a one to many relationship between course and track? If so, you would put course_id in the track table. Similarly, if there is a one to many relationship between tee and course, you would put the tee_id in the track table. Use similar logic for deciding the relationship between slope and the other tables.

With regards to performance, premature optimization is the root of all evil. I would do some performance testing with sample data before attempting to denormalize the data. Once you denomalize, your program logic will need to become more complicated because you'll have data in more than one place and it all must be kept in sync.