need to know whether my DB structure is ok?

[tezza05]

Hi Guys

I'm developing a website for a golf society where by the member logs in, selects which golf course they played, adds their score for each hole and from that their handicap is calculated plus a member will have access to all the rounds they've played.

The update score page is going to use a score card form template from which the fields are populated with details from each individual golf course and from the members score IE (Hole) 1 - (PAR) 4 - (SI) 5 - (MEMBERS SCORE) 5

The member can play a course on different tee colours which equal different yardages.

Could you tell whether I'm going the right way for the DB Stucture?

Members Table
--------------
->members_id
->name
->handicap
....other details
--------------

Golf Club Table
--------------
->golf_club_id
->name
--------------

Golf Course Table (A golf club may have more than 1 course)
--------------
->golf_course_id
->name
->total yards
->par
->stroke index
->golf_club_id
--------------

Golf Tees Played Off Table (Competition, Mens etc)
--------------
->tee_id
->tee_colour
--------------

Table for the par on each hole
--------------
->pars_table_id
->hole1
->hole2
->hole3
....
->hole18
->tee_id
->course_id
--------------


Table for the yardage on each hole
--------------
->yardage_table_id
->hole1
->hole2
->hole3
....
->hole18
->tee_id
->course_id
--------------


Table for the SI on each hole
--------------
->SI_table_id
->hole1
->hole2
->hole3
....
->hole18
->tee_id
->course_id
--------------

Scores Table
--------------
->score_id
->date
->hole1
->hole2
->hole3
....
->hole18
->tee_id
->course_id
->members_id
--------------

hope this makes sense I'm not whether I'm going the right or whether I'm way off, any help would be much appreciated.

Kind Regards

Tez

p.s sorry about long post

[r937]

p.s sorry about long post

no prob

your design is fine

don't listen to anyone who says your 18 similar columns are not in first normal form and should be split off into 18 rows in some other table

what's SI?

[tezza05]

Thank you very much for the quick reply, that gives me confidence, just wanted to check with the experts first!!

SI is Stroke Index its a scale of how hard the hole is (1-18) 18 being the easiest

Thanks again

Tez

[innovatn]

I see a few potential problems with the last 3 or so tables you mentioned.

First off, here is what I would do:

Table called holes
----------------
-> hole_id (pk)
-> course_id (fk)
-> par
-> yardage
-> SI

Now here is my reasoning:
There is no need to duplicate so many fields when you will need to do JOINS just to get all information about a particular hole. Also in setting it up the way I have shown, you can have as many or as few holes per course as needed. Thinking of executive courses with only 9 holes etc...

One query can pull all information in order for the entire course:
SELECT hole_id, par, yardage, SI
FROM holes
WHERE course_id = 10
ORDER BY hole_id

just my .02

Marc