高效的MySQL数据库设计

Question

我正在为我的学校做一个小项目，我有一个关于以下DB设计的问题。

管理员将通过在线表格将主题分配给教师。

一个主题的例子是这样的：

• 科目字段1：(5年级数学:教学5/1、5/2和5/3)
• 科目字段2：(6年级数学:教学6/2和6/6)

我已经增加了7个主题字段，他们的要求，因为不会有超过7个科目。

每个学科领域都需要完成诸如课程计划、课程大纲等内容。

每项要求将有下列要求：

• 语言(语言1，语言2或两者兼而有之)
• 类型(打印、文件或两者兼备)
• 时间(第一学期、第二学期或两者兼而有之)

​

​

到目前为止，我已经提出了这个DB设计：

• ID (主，自动递增)
• 工贸署(教师证)
• 年
• Subject1
• Subject1Requirement1
• Subject1Requirement2
• Subject1Requirement3
• TimeSent
• TimeReviewed
• Subject2
• Subject2Requirement1
• Subject2Requirement2
• Subject2Requirement3
• TimeSent
• TimeReviewed
• Subject3
• Subject3Requirement1
• Subject3Requirement2
• Subject3Requirement3
• TimeSent
• TimeReviewed

继续讨论第7主题。

我觉得有一种更有效率的方法，但就是想不出更好的方法。

谢谢。

Answer 1

如果教师的科目之间没有任何关系，您可以设计如下3张表

Teachers    TeacherSubjects SubjectRequirements
----------  --------------- --------------------
ID          SubjectID ----> SubjectID
TID --\     SubjectName     SubjectRequirement
Year   \--> TID             Language
            TimeSent        Type
            TimeReviewed    Time

在这种设计中

• 每位教师可以有多种科目(不限于7门科目)
• 每个教师的科目有多种要求(不限于5项要求)

样本数据

INSERT INTO Teachers(ID, TID, Year) VALUES (1,'LiuYan', 2012);
INSERT INTO Teachers(ID, TID, Year) VALUES (2,'Emily',  2012);

INSERT INTO TeacherSubjects (SubjectID, SubjectName, TID) VALUES ('SubjectID_1', 'SubjectName1', 'LiuYan');
INSERT INTO TeacherSubjects (SubjectID, SubjectName, TID) VALUES ('SubjectID_2', 'SubjectName2', 'LiuYan');
-- ...
INSERT INTO TeacherSubjects (SubjectID, SubjectName, TID) VALUES ('SubjectID_N', 'SubjectNameN', 'LiuYan');

INSERT INTO TeacherSubjects (SubjectID, SubjectName, TID) VALUES ('SubjectID_N+1', 'SubjectName N+1', 'Emily');
INSERT INTO TeacherSubjects (SubjectID, SubjectName, TID) VALUES ('SubjectID_N+2', 'SubjectName N+2', 'Emily');
-- ...
INSERT INTO TeacherSubjects (SubjectID, SubjectName, TID) VALUES ('SubjectID_M', 'SubjectName M', 'Emily');

INSERT INTO SubjectRequirements (SubjectID, SubjectRequirement, Language, Type, Time) VALUES ('SubjectID_1', 'Curriculum', 'Language 1', 'Printed', 'Semester 1');
INSERT INTO SubjectRequirements (SubjectID, SubjectRequirement, Language, Type, Time) VALUES ('SubjectID_1', 'Course Syllabus', 'Language 2', 'File', 'Semester 2');
INSERT INTO SubjectRequirements (SubjectID, SubjectRequirement, Language, Type, Time) VALUES ('SubjectID_1', 'Learning Management', 'Both Language', 'Both Type', 'Both Semester');
--...

INSERT INTO SubjectRequirements (SubjectID, SubjectRequirement, Language, Type, Time) VALUES ('SubjectID_N+1', 'Curriculum', 'Language 2', 'Both Type', 'Semester 2');
--...

Answer 2

数据库的关键方面是在表之间有关系 (链接)，因此如果表(行)需要引用其他不同的东西，那么使用另一个表并与外键一起链接--这个表，在我们的例子中，TeacherSubject是一个连接表。

为了给您简要介绍我如何实现您的需求，我认为我们需要三个表，下面的SubjectsTeacher是连接这两个表的链接表。

定义每个主题和教师，然后将条目添加到TeacherSubject表中，以便将教师与主题关联起来。

​

​

要创建数据库：

CREATE TABLE `Subject` (
`SID` INTEGER NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR(100) NOT NULL ,
`Curriculum` INTEGER NOT NULL ,
`Syllabus` INTEGER NOT NULL ,
`LearnManagement` INTEGER NOT NULL ,
`Individual Analysis` INTEGER NOT NULL ,
PRIMARY KEY (`SID`)
);

CREATE TABLE `Teachers` (
`TID` INTEGER NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`TID`)
);

CREATE TABLE `TeacherSubject` (
`id` INTEGER NOT NULL AUTO_INCREMENT ,
`SID_Subject` INTEGER NOT NULL ,
`TID_Teachers` INTEGER NOT NULL ,
PRIMARY KEY (`id`)
);

然后添加外键，告诉数据库如何链接字段--这一步很重要，因为它将确保数据完整性，并且不能将坏数据插入到该数据中。

ALTER TABLE `TeacherSubject` ADD FOREIGN KEY (SID_Subject) REFERENCES `Subject` (`SID`);
ALTER TABLE `TeacherSubject` ADD FOREIGN KEY (TID_Teachers) REFERENCES `Teachers` (`TID`);

然后设置几行以进行测试。

 INSERT INTO `Subject` (`SID`, `Name`, `Curriculum`, `Syllabus`, `LearnManagement`, `Individual Analysis`) VALUES
    (1, 'Subject1', 1, 2, 3, 4),
    (2, 'Subject1', 1, 2, 3, 4),
    (3, 'Subject2', 1, 2, 3, 4),
    (4, 'Subject3', 1, 2, 3, 4),
    (5, 'Subject4', 1, 2, 3, 4);
INSERT INTO `Teachers` (`Name`) VALUES ('Teacher 1');
INSERT INTO `Teachers` (`Name`) VALUES ('Teacher 2');

INSERT INTO `TeacherSubject` (`id`, `SID_Subject`, `TID_Teachers`) VALUES
    (1, 1, 1),
    (2, 2, 1),
    (3, 3, 2),
    (4, 4, 2),
    (5, 1, 2);

最后，要找出teacher#1有哪些主题：

 select * from TeacherSubject
INNER JOIN Teachers on TID=TID_Teachers
WHERE TID_Teachers=1