I am creating a scorecard application and needing some advice on the best setup for this application. This is the scenario: One club has multiple courses. Each course has exactly 18 holes with specific information about each hole. The scorecard will be for one specific participant. Each of the 18 holes is scored separately but uses the information about the individual course-hole as well as information about the player to make the final score calculation. The scorecard will be used in a tournament to compare against other scorecards for each of 3 rounds of the tournament. The rounds in the tournament may be playing on multiple courses and possibly at more than one club.
I have several basic questions on this setup;
1. When creating the Scorecard table should I have foreign keys for all of these tables (Club, Course, Tournament, Round ...) I will be creating as there will be quite a few?
2. Can I get information from a table that is related more than one step away via the relationship from a table that has a direct relationship?
Ex: Club -<Course-<Scorecard ( Can I get the Club name in a Scorecard layout via a foreign key in the Course table or do I need a fk in the Scorecard table that points to the Club table?)
3. Since there are 18 individual holes for each course and 18 individual scores for the Scorecard am I better off to make a table for the individual holes for the course (18 records per course) and a table for the individual holes for the scorecard (18 scores per scorecard)? There will be quite a bit of scripting to make the scoring happen as there are multiple factors that differ for each course hole as well as player info. These factors have to be taken into account for the net score on each hole.
If this is not clear I will be happy to expand but knew this could get lengthy.
Thanks - Steve