Design questions

[Hyodoh]

Hey guys I was hoping I could get some help with the design of this database. If need be, I'll try to explain it best I can of what it's supposed to do, I think I have most of it set up right, I just think something is wrong with this one table and I can't figure it out.

My db is for the college I go to. It's basically to keep track of track and field stats for the track coaches. It will also be put into a MySQL db, the only reason I have it in access right now is to make sure all of the relationships work and it makes an alright ERD.

the pic attached is what the relationships look like but something is off with that 1:1 relationship and I can't pinpoit it. Btw, I'm not the greatest DB guy out there so.

ATHLETE 
     AthleteID (text 15)
     AthleteFirstName (text 15)
     AthleteLastName (text 25)
     AthleteDOB (date)
     AthleteHome (text 25)
     AthleteHighschool (text 40)
     AthleteYear (text 2)
     AthleteGender (text 1)
     AthleteWeight (number 3)
     AthleteHeight  (number 3)
     AthleteEvents (text 15)

ATHLETE_PARTICIPATION
     MeetingID (number)
     AthleteID (text 15)
     TEventID (text 20)
     EventMark (number)

MEETING
     MeetingID (Autonumber)
     EventName (text 15)
     EventLocation (text 20)
     EventDate (date)
     EventSeason (text 10)

EVENTS
     EventID (text 20)
     EventName (text 15)

UNNAMED_INTERMEDIATE_TABLE
     EventID (text 20)
     MeetingID (Number)

I put it in iCode so its easier to read, hope that's ok

[BitBlt]

I think (if I interpret your requirement correctly) that the relationship between tables ATHLETE_PARTICIPATION and MEETING should be a 1:M, with the M side on ATHLETE_PARTICIPATION.

Here's the way you can test your relationships...just say them out loud in English. So, for example, this one works:

One ATHLETE can participate in many ATHLETE_PARTICIPATIONs
One ATHLETE_PARTICIPATION must refer to one ATHLETE

This one does not:

One ATHLETE_PARTICIPATION must refer to one MEETING
One MEETING must refer to one ATHLETE_PARTICIPATION

So, to fix it, say it out loud in English like you WANT it to work and it should sort itself out:

One ATHLETE_PARTICIPATION must refer to one MEETING
One MEETING is participated in by many ATHLETE_PARTICIPATIONs

It's also helpful to try and characterize the relationship with a good strong action verb. It really makes the relationship "pop".

You might also consider looking at HOW an ATHLETE participates in a MEETING. The way you have your relationships set up, it doesn't accurately capture how EVENT gets into the mix...just that the ATHLETE was at the MEETING. I suggest that rather than have the relationship between ATHLETE_PARTICIPATION and MEETING, have it between ATHLETE_PARTICIPATION and UNNAMED_INTERMEDIATE_TABLE. Of course, you probably want to give it a better name, like MEETING_EVENT, or something like that.

Also, something else to think about...did you want to be able to capture heats for races or flights for field events? Individual marks or times as well as 1st/2nd/3rd or Gold/Silver/Bronze? (Yes, I was a track athlete in High School/College :) )

Hope that helps. Good luck!