mysql - 数据库设计——多个外键

Question

我的数据库包含学校、部门和类(class)。一个学校可能有也可能没有分部，一个类(class)与一个学校有关，也可能与一个分部有关也可能不相关(学校可能没有分部，也可能类(class)交叉分部)

我的表目前设置如下:

学校:

ID       | name
--------------------
harvard  | Harvard University
mit      | MIT
ucla     | UCLA

师 (id+school=unique)

ID  | school (FK) | name
------------------------------------------
eng | harvard     | School of Engineering
arc | harvard     | School of Architecture
eng | UCLA        | UCLA Engineering

类(class):

ID | school (FK) | division | name
-------------------------------------------------
1  | harvard     | eng      | Intro to Engineering 
2  | harvard     | arc      | Intro to Architecture
3  | harvard     |          | Statistics
4  | mit         |          | Math

我对此的担忧:

• 没有验证来确保 course 中的部门存在并且与学校相关。
• 除法实际上不是fk
• 需要两次查询才能得到学校和部门

有更好的方法吗？我希望能够:

• 查询所有“harvard”类(class)
• 查询所有“哈佛工程”类(class)
• 查询所有“harvard engineering and harvard general”类(class)

Answer 1

如果总是可以创建复合外键。但是，我建议对您的设计进行以下更改:

• 为每所学校创建一个名为“General”的默认部门
• 允许同一类(class)存在于不同学校的可能性(在现实生活中，这可能会发生)。

此外，我建议对所有表使用自动递增的整数主键，而不是依赖于手动构建的名称。

考虑以下遵循上述原则的设计:

school
    id            primary key
    name

division
    id            primary key
    school_id     foreign key to school(id)
    name

course
    id            primary key
    name

course_division
    id            primary key
    course_id     foreign key to course(id)
    division_id   foreign key to division(id)

---

现在这里是使用这个模式的查询。

查询所有“harvard”类(class)

SELECT c.* 
FROM course c
INNER JOIN division d ON d.id = cd.division_id
INNER JOIN school s ON s.id = d.school_id AND s.name = 'Harvard University'

查询所有“harvard engineering”类(class)

SELECT c.* 
FROM course c
INNER JOIN course_division cd ON cd.id = c.course_id 
INNER JOIN division d ON d.id = cd.division_id AND d.name = 'School of Engineering'
INNER JOIN school s ON s.id = d.school_id AND s.name = 'Harvard University'

查询所有“harvard engineering and harvard general”类(class)

SELECT c.* 
FROM course c
INNER JOIN course_division cd ON cd.id = c.course_id 
INNER JOIN division d ON d.id = cd.division_id AND d.name IN ('School of Engineering', 'General')
INNER JOIN school s ON s.id = d.school_id AND s.name = 'Harvard University'