学校跑步俱乐部 - mySQL数据库设计

I am a PE teacher trying to make a basic mySQL database for our students and parents to see their running results. I have been doing some reading and I hope I am on the right track!

我是一名体育老师,试图为我们的学生和家长创建一个基本的mySQL数据库,以查看他们的运行结果。我一直在做一些阅读,我希望我走在正确的轨道上!

Do these tables seem appropriate?

这些表似乎合适吗?

CREATE  TABLE IF NOT EXISTS `Students` (
  `StudentID` INT NOT NULL ,
  `Firstname` VARCHAR(45) NULL ,
  `Surname` VARCHAR(45) NULL ,
  `DOB` DATE NULL ,
  `Class` VARCHAR(45) NULL ,
  `Year` VARCHAR(45) NULL ,
  `House` VARCHAR(45) NULL ,
  PRIMARY KEY (`StudentID`) )

CREATE  TABLE IF NOT EXISTS `Races` (
  `RunID` INT NOT NULL ,
  `StudentID` VARCHAR(45) NULL ,
  `Date` DATETIME NULL ,
  `Distance` INT NULL ,
  PRIMARY KEY (`RunID`) )

The end game is to query the following type of results: Weekly top 10 runners

最终游戏是查询以下类型的结果:每周前10名选手

Weekly - which class/house/year level had the most children attending and most kilometres run

每周 - 哪个班级/房屋/年级最多的孩子参加,大多数公里跑

Total KM's run etc

总KM的运行等

Any thoughts are greatly appreciated =)

任何想法都非常感谢=)

1 个解决方案

#1

Races.StudentID must be a foreign key to Students. Run following alter after creating the tables

Races.StudentID必须是学生的外键。创建表后运行以下alter

ALTER TABLE Races
ADD CONSTRAINT FK_Races_Students FOREIGN KEY (StudentID)
REFERENCES Students(StudentID);

You use a VARCHAR to store the studentid in Races table, which has performance drawbacks while joining the two tables. Instead, make the Races.StudentID to int, followed by the foreign key alter script.

您使用VARCHAR在Races表中存储studentid,这在连接两个表时存在性能缺陷。相反,将Races.StudentID设置为int,然后是外键alter script。

You might also consider adding AUTO_INCREMENT to your primary key fields.

您还可以考虑将AUTO_INCREMENT添加到主键字段。

CREATE  TABLE IF NOT EXISTS `Students` (
  `StudentID` INT NOT NULL AUTO_INCREMENT,
...

CREATE  TABLE IF NOT EXISTS `Races` (
`RunID` INT NOT NULL AUTO_INCREMENT,
...

This will automatically increment the primary keys, and you can omit them in your insert queries.

这将自动增加主键,您可以在插入查询中省略它们。

#1