关联表与数据完整性

Question

我正在开发一个与课堂材料相关的解决方案。类的层次结构如下：

• 课程类型(例如汽车课程)
  • 课程(例如修理汽车，驾驶汽车)
    • 课堂(如秋季学期)

我的问题涉及课堂材料。目前，我有一个包含MaterialId、Title、Href等的“资料”表。我需要将材料与4种可能的场景联系起来：

1. 全球材料(材料适用于所有课程，不论课程类型如何)
2. 课程类型材料
3. 课程材料
4. 特定类别材料

目前，我正在使用materials (IsGlobal)中的一个字段处理全局材料，而我正在使用链接表(MaterialCourseType、MaterialCourse、MaterialClass)处理其他3种场景。这个解决方案的问题在于，它要求我通过应用程序代码来增强数据完整性。例如，如果我将一个材料与一个课程类型相关联，我将需要删除MaterialCourse和MaterialClass表中对材料的所有引用(因为它包含在MaterialCourseType中将涵盖这些内容)。在我的select语句中，可以使用UNION，它将在所有4个表中自动选择不同的材料，但这将留下无效的数据。当通过前端的用户决定向整个课程提供材料，而不是为特定的类提供材料时，对我来说，不清理MaterialClass表似乎很麻烦。

我是否可以使用更好的数据库设计来增强数据完整性，或者我是否被迫在应用程序代码中处理这个问题，以便在用户以不同方式关联材料时首先删除无效条目？

Answer 1

我完全支持你不删除材料记录的倾向。关联记录加入类型/课程/类别到材料，但是，如果您不再需要关联，您将需要删除。

我推荐下面的模式，我用T编写的，因为它是我的原生SQL风格。它为您提供了创建->课程->类结构的基础，并使您能够在所有三个级别上共享一个全局材料列表，同时在每个级别上创建独特的关联。

这里有一个木琴，可以让您四处游玩并熟悉设计。

create table CourseType (
  Id int identity primary key
  ,Name nvarchar(100) not null
);

create table Course (
  Id int identity primary key
  ,CategoryId int not null foreign key references CourseType(Id)
  ,Name nvarchar(100) not null
);

create table Class (
  Id int identity primary key
  ,CourseId int not null foreign key references Course (Id)
  ,Name nvarchar(100) not null
);

create table Material (
  Id int identity primary key
  ,Name nvarchar(100) not null
);

create table CourseTypeMaterials (
  CourseTypeId int not null foreign key references CourseType(Id)
  ,MaterialId int not null foreign key references Material(Id)
  ,primary key (CourseTypeId, MaterialId)
);

create table CourseMaterials (
  CourseId int not null foreign key references Course(Id)
  ,MaterialId int not null foreign key references Material(Id)
  ,primary key (CourseId, MaterialId)
);

create table ClassMaterials (
  ClassId int not null foreign key references Class(Id)
  ,MaterialId int not null foreign key references Material(Id)
  ,primary key (ClassId, MaterialId)
);

Answer 2

所有这些在PostgreSQL中都是有效的，它支持自引用表和递归CTEs来查询表(rCTEs)。

评注

例如，如果我将一个材料与一个课程类型相关联，我将需要删除MaterialCourse和MaterialClass表中对材料的所有引用(因为它包含在MaterialCourseType中将涵盖这些内容)。

那是胡说。您只需隐藏重复的内容(比如DISTINCT)，或者提到课程和课程需要它。这听起来很有意义，也很有用。

模式

我会用一个单一的表层次结构，有类似这样的类别，

CREATE SCHEMA myedu;

CREATE TABLE myedu.categories (
   id_category  int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   id_parent    int REFERENCES myedu.categories,
   name         text
);
INSERT INTO myedu.categories VALUES
  ( 1, null, 'Car Courses' ),
  ( 2, 1, 'Fixing Cars'),
  ( 3, 1, 'Driving Cars');

您可以在本学期使用枚举类型。

CREATE TYPE myedu.semester AS ENUM ('spring', 'fall', 'summer');

我们可以创建这样的类，

CREATE TABLE myedu.classes (
  id_class    int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  id_category int REFERENCES myedu.categories,
  semester myedu.semester NOT NULL,
  year     smallint       NOT NULL,
  UNIQUE (id_category, year, semester)
);

INSERT INTO myedu.classes (id_class, id_category, semester, year)
VALUES ( 1, 3, 'fall', 2018 );

现在我们创建一个物质表和两个链接表。我们这样做是因为没有任何关于材料是分层的，书籍和铅笔之间没有直接联系，尽管使用它们的类是这样做的。您也可以将它写成JSONB列在相应的表上，这很可能是我要做的，除非您确实需要搜索材料。

CREATE TABLE myedu.materials (
  id_material    int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  material_name  text
);

CREATE TABLE myedu.material_classes    (
  id_material int REFERENCES myedu.materials,
  id_class    int REFERENCES myedu.classes
);
CREATE TABLE myedu.material_categories (
  id_material int REFERENCES myedu.materials,
  id_catagory int REFERENCES myedu.categories
);