mysql - 来自同一个表的多个外键

Question

我正在构建毕业项目管理系统的数据库。学生分为小组。有小组表和教师表。每组有一名指导老师和两名考官。我在这里很困惑。我应该从教师表中创建 3 个 FK 吗？ 2 个给考官，1 个给顾问？

这是 SQL 代码:

 create table groups
(
    groupID             NUMBER                not null,
    nbStudents          NUMBER                not null,
    avgGPA              DOUBLE                NOT NULL,
    projectName         varchar(50)           not null,
    advisorID           NUMBER                
    examiner1ID         NUMBER  
    examiner2ID         NUMBER  
    primary key (groupID)
);
create table faculty
(
    name                varchar(30)           not null,
    facultyID           NUMBER(10)            not null,
    email               varchar(30)           not null,
    mobile              NUMBER(15)            not null,
    type                varchar               
    primary key (facultyID)
);

alter table groups
    add constraint FK_EX1 foreign key (examiner1ID)
       references faculty (facultyID) ;
alter table groups
    add constraint FK_EX1 foreign key (examiner2ID)
       references faculty (facultyID) ;
alter table groups
    add constraint FK_EX1 foreign key (advisorID)
       references faculty (facultyID) ;

Answer 1

编辑待处理...请参阅我的第一条评论。

只要说出找到的外键即可。

外键表示表中的列中的值必须显示为另一个(可能是相同的)表中的列的值，其中相应的列形成键。因此，在给定的设计中，只需在找到 FK 时声明它们即可。

虽然这些并不是真正的FK。首先，在 SQL 中，FK 声明实际上声明了一个外部 super 键。其次，因为这些列可以为 NULL。 SQL 说明了它将如何检查它们，并且它不会检查列何时为 NULL，而这正是您想要的。但该约束并不是外( super )键。我们只是在 SQL 数据库中这样调用它。

找到描述您的申请情况的陈述，然后进行规范化。

本身具有多个列并不是非标准化的。那是一个common misconception .

然而，至少对于理想的设计来说，它通常是禁忌的。只需找到一个参数化语句，该语句由列名参数化，用于您需要说明的有关情况的每件事。每个语句都有一个表。

// group [groupID] contains [nbStudents] students .... and has advisor [advisorID] and ...
groups(groupID,nbStudents,...,advisorID,examinerID)

使语句成立的行进入表中。找到描述您的申请情况所需的所有陈述。用使其陈述成立的行填充表格。

找到简单的语句并稍后重新排列为 NULL。

请注意，上述语句仅适用于没有 NULL 的行。但有时你想说，没有教师担任这些角色。

理想情况下你只是想要

// group [groupID] contains [nbStudents] students ... [projectName])
groups(groupID,nbStudents,...,projectName)
// [facultyID] advises [groupID]
advises(facultyID,groupID)
// [facultyID] examines [groupID]
examines(faculty,groupID)

每个小组的教职人员数量受到限制。如果你正确地编写了一个没有空值的关系设计，那么标准化你就会得到这种简单的东西。不用担心语句/表的数量。它们只是反射(reflect)了应用程序的复杂性。

但是 SQL DBMS 通常不容易支持约束。因此，出于与 SQL 或性能有关的某些原因，我们可能会重新安排。但首先要设计无空值。即选择简单的陈述然后标准化。然后重新合理安排。 (某些重新排列可能会导致非规范化，但在这种特殊情况下不会。)

空值使复杂化。

空值的一个问题是它们使表含义变得复杂。您使用 null 的设计有表 group 保存使此语句成立的行:

//*
    group [groupID] contains [nbStudents] students ....
AND (   [advisorID IS NULL  they have no advisor
    OR [advisorID] IS NOT NULL AND advisor [facultyID] advises them)
AND (   [examiner1ID IS NULL AND [examiner2ID] IS NULL and they have no examiner
    OR [examiner1ID] IS NOT NULL AND [examiner2ID] IS NULL AND [examiner1ID] examines them
    OR [examiner1ID] IS NULL AND [examiner2ID] IS NOT NULL AND [examiner2ID] examines them
    OR [examiner1ID] IS NOT NULL AND [examiner2ID] IS NOT NULL
       AND [examiner1ID] examines them AND [examiner2ID] examines them)
*//
groups(groupID,nbStudents,...,advisorID,examinerID)

除非你在查询时把空值切回上面的简单表，否则你的查询含义也像这样复杂。即查询给出的行使此类语句为真。

最重要的是，当 SQL 中保留空值时，您会得到复杂的答案，不意味着“...并且教师未知”。

人们对基表中的此类空值有直观的理解。但设计首先要简单、合理。稍后重新安排。确保在查询时正确删除无空部分并保留无空部分。