mysql - 讲师有2个教学科目

桌子

Instructor
    ------------------------------------------------------
    | id | firstname | lastname | subject_id | section_id |
    ------------------------------------------------------
       1    John        Smith        1             1

Subject
    -------------
    | id | name |
    -------------
      1  English
      2   Math

Section
    -------------
    | id | name |
    -------------
     1  Section A

我的问题：

SELECT ins.firstname, ins.lastname,sec.name as "Section Name", sub.name as "Subject"
    FROM instructor as ins
    JOIN section as sec
    JOIN subject as sub
    WHERE ins.section_id = sec.id AND ins.subject_id = sub.id AND ins.id =1

结果：

firstname   lastname    Section Name    Subject
John    Smith            Section A  English

我的问题是，如果那个老师约翰·史密斯也教数学，我会怎么质疑呢？我需要添加新的主题ID吗？有什么解决办法吗？
谢谢，

最佳答案

您可以添加另一个用于存储每个讲师的主题的表，例如

CREATE TABLE Instructor
(
    ID INT,
    InstructorName VARCHAR(30),
    ..... -- other fields here
);

CREATE TABLE Subject
(
    ID INT,
    SubjectNameName VARCHAR(30),
    ..... -- other fields here
);

CREATE TABLE INSTRUCTOR_SUBJECT
(
    RecID INT,
    InstructorID INT,
    SubjectID INT,
    CONSTRAINT tc_pk PRIMARY KEY (RecID),
    CONSTRAINT tc_fk1 FOREIGN KEY (InstructorID) REFERENCES Instructor(ID),
    CONSTRAINT tc_fk2 FOREIGN KEY (SubjectID) REFERENCES Subject(ID)
);

SQLFiddle Demo