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, ...
有会议,几个体育俱乐部将分别派出一些运动员:1人或以上。运动员可以参加一个以上的比赛。有几个事件:马拉松,杆子,枪,...
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.
每场比赛都有第一轮,四分之一决赛,半决赛和决赛。每个单独的活动都有1位官员记下数据,无论是时间,高度还是距离。
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.
基本上,这里与运动员和赛事有N:M的关系。 1运动员可以参加许多赛事,有很多赛事,可由一名以上的运动员输入。
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.
我认为俱乐部和运动员会有同样的事情,但我只是在运动员表中放了一个“club_id”字段来指代来自哪个俱乐部。
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.
我认为基本上就是这样。缺少的是将系列连接到结果的方法。所以我认为应该有另一个N:M关系,因为运动员可以进入决赛或者在两者之间的某个位置,但是每一轮比赛他们都需要记下他们的分数。
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'
因此,举例来说,我需要能够进行一个查询,这个查询基本上给了我'所有运动员的名字,在某一轮中某个赛事的时间比5分钟更快'
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.
我不太清楚如何添加它。
2 个解决方案
#1
Add a result field to the participation table
将结果字段添加到参与表
#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!
您的问题更多的是应用程序问题,而不是数据库问题。您的数据库设计看起来很适合您的需求。您现在需要做的是编写应用程序以查询数据库。一些SELECTS和JOINS,你应该没有问题!
#1
Add a result field to the participation table
将结果字段添加到参与表
#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!
您的问题更多的是应用程序问题,而不是数据库问题。您的数据库设计看起来很适合您的需求。您现在需要做的是编写应用程序以查询数据库。一些SELECTS和JOINS,你应该没有问题!