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

Question

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

简化的问题看上去如下:我有一位餐桌老师。有两种类型的教师，根据与他们的领域和学科的关系：

1. 与某一领域相关的教师，该领域是与某一类别相关的必修课
2. 与某一领域无关的教师，但与某一类别直接相关的教师

我最初的想法是拥有两个可为空的外键，一个用于表字段，另一个用于表类别。但在这种情况下，我如何确保一个是空的，而另一个不是？

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

我正在使用Django和SQLite db开发这个应用程序

Answer 1

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

如果一名教师恰好属于某一类别，则应将其直接保存在教师的桌子上：

第二，每个教师都属于“一或零”领域。如果这是肯定的，那么您应该使用一个可空的FieldID列。这是设置或保持为空。

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

备注:这几乎和我最后一个答案的映射表一样。唯一的区别是，你会对你的“精确一”或“完全没有或一”有严格的限制.根据我的经验，我还是更喜欢开放的方法。使用独特的索引(包括TeacherID-列)来强制执行规则是很容易的。迟早你得重新构造这个.

当您继续时，有一个类别与“零或多个”字段相关。有两种方法：

向字段表( NULL )添加一个类别FK列。通过这种方式，您可以使用不同的类别index(组合唯一索引！)多次定义一个字段。一个类别的字段列表，您只需询问字段表的所有字段与给定的CategoryID。

在我看来更好的是一个映射表CategoryField。如果您强制执行一个唯一的FieldID，您肯定不会有任何字段被映射两次。并在CategoryID和FieldID的结合上添加了一个独特的索引。

SELECT可能是这样的( 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, ...

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

教师也是如此:如果教师被绑定到一个类别，在教师表中设置一个FK列，否则不要。

对于至少一个映射表，最灵活的是：

( Server语法)

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和这个表通过外键映射到上面的映射表可能是一个更好的概念。这样做可以避免无效的字段类别组合。

希望这能帮上忙。