Database design for sports meeting(in MySQL)

Question

I'm trying to design a database for a sports meeting.

I think I've got something good but I'm not quite sure.

The situation is as follows.

There is the meeting, several sportsclubs will each send a number of athletes: 1 or more. The athletes can enter in more then one event. There are several events: Marathon, pole-juming, spear throwing, ...

Each event has a first round, Quarter final, Half final and Final. Each individual event has 1 official who takes down the data, be it time, height or distance.

So basically, there's an N:M relationship here with the athletes and events. 1 Athlete can enter many events and there are many events, which can be entered by more then one athlete.

I thought there was going to be the same thing with the clubs and the athletes, but I just put a field 'club_id' in the Athletes table to refer to which club the come from.

What I have is this:

Table Clubs:
club_id int primary key auto_increment not null,
club_name varchar(50) not null

Table Athletes:
athlete_id int primary key auto_increment not null,
athlete_name varchar(40) not null,
athlete_club int not null,
index (athlete_club),
foreign key (athlete_club) references Clubs(club_id)

Table Events:
event_id int primary key auto_increment not null, (if there's a new round, it's a new event too)
event_name varchar(40) not null,
official_id int not null,
round enum('FIRST','QUARTER','HALF','FINAL')

Table Participations:
athlete_id int not null,
event_id int not null,
primary key (athlete_id,event_id),
index (athlete_id),
index (event_id),
foreign key (athlete_id) references Athletes(athlete_id),
foreign key (event_id) references Events(event_id)

And I think that's basically it. What's missing is a way to connect the series to the results. So I'm thinking there should be another N:M relation, since an athlete can go on to the final or drop somewhere in between, but every round they did, their scores need to be noted down.

So for instance, I need to be able to make a query which basically gives me 'the names of all athletes whos times were faster then 5 minutes for a certain event in a certain round'

Or a list of the rounds one athlete did in one event, or a list of all athletes who got to a certain round...

I'm not quite sure how to add this.

Answer 1

Add a result field to the participation table

Answer 2

What your asking is more of an application question then a DB question. Your DB design looks fine for what you need. What you need to do now is write your application to query the database. A few SELECTS and JOINS and you should have no problems!