sql - 多种实体类型的最佳数据库设计

Question

我正在开发一个网络应用程序，我必须设计它的数据库。有一部分对我来说不是很直接，所以经过一些思考和研究后，我提出了多个想法。仍然两者似乎都不完全合适，所以我不确定要实现哪一个以及为什么。

简化的问题如下所示:
我有一张 table 老师。根据与他们的领域和学科的关系，有两种类型的教师:

与某个领域相关的教师，该领域是必须与类别

相关的

与领域无关，但直接与类别

相关的教师

我最初的想法是有两个可以为空的外键，一个到表字段，另一个到表类别。但是在这种情况下，我怎样才能确保恰好一个为空，而另一个不是呢？

另一个想法是创建一个层次结构，从表教师(是关系)派生两种类型的教师表，但我找不到任何有用的教程。

我正在使用带有 SQLite db 的 Django 开发应用程序

Answer 1

好的，你的评论说得更清楚了:

如果教师恰好属于一个类别，则应将其直接保存在教师表中:

其次，每位教师都属于“一或零”领域。如果这是肯定的，你应该使用一个可以为空的 FieldID 列。这是设置或保持为空。

Category (CategoryID, Name, ...)
Field (FieldID,Name,...)
Teacher (TeacherID,FieldID [NULL FK],CategoryID [NOT NULL FK], FirstName, Lastname, ...)

备注:这和我上一个答案的映射表差不多。唯一的区别是，您将对“完全一个”或“完全没有或一个”有严格的限制......根据我的经验，我仍然更喜欢开放的方法。使用包括 TeacherID 列在内的唯一索引很容易执行您的规则。迟早你可能不得不重新构建这个......

随着您的继续，一个类别与“零个或多个”字段相关。有两种方法:

将 CategoryID 列添加到字段表(非空 FK)。这样，您可以使用不同的 CategoryID(组合唯一索引!)多次定义一个字段。一个类别的字段列表，您只需向字段表询问具有给定 CategoryID 的所有字段即可。

在我看来更好的是一个映射表 CategoryField。如果您强制使用唯一的 FieldID，您肯定会得到，没有字段被映射两次。并在 CategoryID 和 FieldID 的组合上添加唯一索引...

SELECT 可能是这样的(SQL Server 语法，未经测试):

SELECT Teacher.TeacherID
      ,Teacher.FieldID --might be NULL
      ,Teacher.CategoryID --never NULL
      ,Teacher.[... Other columns ...]
      ,Field.Name --might be NULL

      --The following columns you pick from the right source, 
      --depending on the return value of the LEFT JOIN to Field and the related "catField"
      --the directly joined "Category" (which is never NULL) is the "default"
      ,ISNULL(catField.CategoryID,Category.CategoryID) AS ResolvedCategoryID
      ,ISNULL(catField.Name,Category.Name) AS ResolvedCategoryName
      ,[... Other columns ...]

FROM Teacher
    INNER JOIN Category ON Teacher.CategoryID=Category.CategoryID --never NULL
    LEFT JOIN Field ON Teacher.FieldID=Field.FieldID --might be NULL
        LEFT JOIN Category AS catField ON Field.CategoryID=catField.CategoryID

这是编辑之前的答案:
即使这个概念对我来说不是很清楚，我也会尽力帮助你

Teacher-Table: TeacherID, person's data (name, address...), ...
Category-Table: CategoryID, category title, ... 
Field-Tabls: FieldID, field title, ...

您说，在所有情况下，字段都绑定(bind)到一个类别。如果这在所有情况下都是相同的类别，则应将类别设置为字段表中的 FK 列。如果有最微小的机会，字段的类别可能会因上下文而异，您不应该...

与教师相同:如果教师曾经被绑定(bind)到一个单一类别，则在教师表中设置一个 FK 列，否则不要。

使用至少一个映射表将是最灵活的:

(SQL 服务器语法)

CREATE TABLE TeacherFieldCategory
(
--A primary key to identify this row. This is not needed actually, but it will serve as clustered key index as a lookup index...
 TeacherFieldCategoryID INT IDENTITY NOT NULL CONSTRAINT PK_TeacherFieldCategory PRIMARY KEY
--Must be set
,TeacherID INT NOT NULL CONSTRAINT FK_TeacherFieldCategory_TeacherID FOREIGN KEY REFERENCES Teacher(TeacherID)
--Field may be left NULL
,FieldID INT NULL CONSTRAINT FK_TeacherFieldCategory_FieldID FOREIGN KEY REFERENCES Field(FieldID)
--Must be set. This makes sure, that a teacher ever has a category and - if the field is set - the field will have a category
,CategoryID INT NOT NULL CONSTRAINT FK_TeacherFieldCategory_CategoryID FOREING KEY REFERENCES Category(CategoryID)
);
--This unique index will ensure, that each combination will exist only once.
CREATE UNIQUE INDEX IX_TeacherFieldCategory_UniqueCombination ON TeacherFieldCategory(TeacherID,FieldID,CategoryID); 

拥有一个映射表 FieldCategory 并且该表通过外键映射到上面的映射表可能是一个更好的概念。这样做可以避免无效的字段类别组合。

希望这可以帮助...