StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODatabase design for sports meeting(in MySQL)
text
Body
copied!<p>I'm trying to design a database for a sports meeting.</p> <p>I think I've got something good but I'm not quite sure.</p> <p>The situation is as follows.</p> <p>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, ...</p> <p>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.</p> <p>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.</p> <p>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.</p> <p>What I have is this:</p> <pre><code>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) </code></pre> <p>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.</p> <p>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'</p> <p>Or a list of the rounds one athlete did in one event, or a list of all athletes who got to a certain round...</p> <p>I'm not quite sure how to add this.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload