具有约束的多对一建模？

Question

我正在尝试为电影分类创建一个数据库模型，其中每部电影都可以有来自多个评级系统（例如 BBFC、MPAA）中的每一个的单个分类。这是当前的设计，包含所有隐含的 PK 和 FK：

TABLE Movie 
( 
    MovieId INT -- PK
)

TABLE ClassificationSystem 
( 
    ClassificationSystemId TINYINT -- PK
)

TABLE Classification 
(
    ClassificationId INT,          -- PK
    ClassificationSystemId TINYINT -- FK
)

TABLE MovieClassification 
(
    MovieId INT,          -- composite PK, FK
    ClassificationId INT, -- composite PK, FK
    Advice NVARCHAR(250)  -- description of why the classification was given
)

问题在于 MovieClassification 表，其约束将允许来自同一系统的多个分类，而理想情况下它应该只允许来自同一系统的零个或一个分类给定的系统。

考虑到以下要求，是否有任何合理的方法可以对其进行重组，以便通过数据库约束强制执行具有任何给定系统的零或一个分类的电影？

• 不要重复可以查找的信息（即在 MovieClassification 表中重复 ClassificationSystemId 并不是一个好的解决方案，因为这可能与 MovieClassification 表中的值不同步>分类表）
• 保持可扩展到多个分类系统（即新的分类系统不需要对表结构进行任何更改）？

另请注意 Advice 列 - 电影到分类的每个映射都需要有一个文本描述，说明为什么为该电影赋予该分类。任何设计都需要支持这一点。

Answer 1

您可以使用调用用户定义函数的检查约束来强制执行此操作。例如：

create function dbo.ClassificationSystemCheck()
returns int
as begin
    return (select max(cnt)
    from (
        select count(*) as cnt
        from MovieClassification mc
        left join Classification c
        on c.ClassificationId = mc.ClassificationId
        group by mc.MovieId, c.ClassificationSystemId
    ) qry)
end
go
alter table MovieClassification
add constraint chk_MovieClassification
check (dbo.ClassificationSystemCheck() <= 1)
go
alter table Classification
add constraint chk_Classification
check (dbo.ClassificationSystemCheck() <= 1)
go
insert into Classification select 1,1
insert into MovieClassification select 1,1
insert into MovieClassification select 1,1 -- Boom!

随着分类数量的增加，这可能会效率低下。或者，您可以删除 Classification 表并将 ClassificationSystemId 移至 MovieClassification 表。

Answer 2

如果从分类表中删除分类系统id，只保留在电影分类上会怎么样？

TABLE Movie 
( 
    MovieId INT
)

TABLE ClassificationSystem 
( 
    ClassificationSystemId TINYINT
)

TABLE Classification 
(
    ClassificationId INT,
)

TABLE MovieClassification 
(
    MovieId INT,
    ClassificationId INT,
    ClassificationSystemId TINYINT,
    Advice NVARCHAR(250) -- description of why the classification was given
)

但你会遇到另一个问题，即分类可以在其预期系统之外使用。

Answer 3

根据您的设计，同一分类系统中的一部电影可以有多少种不同的分类？

这符合您的“分类”概念吗？

Answer 4

根据您的说法，ClassificationSystemId 是 MovieClassification 的键的一部分，因为只能有一个（或零个）MovieClassification给定电影的给定系统。

现在，Classification 表可以在三种情况下更改：

1. 您向系统添加新分类。
2. 您从系统中删除现有分类。
3. 您更改分类的元数据（您发布的架构中未显示任何元数据）。

在第一种情况下，一个例子是将新的流派添加到现有的流派系统中。您需要对属于新类型的电影进行重新分类是有道理的，因此该模型成立。

在第二种情况下，一个例子是从现有系统中删除一种流派。您需要对属于旧类型的电影进行重新分类仍然有意义，因此该模型仍然成立。

在第三种情况下，您可以更改例如流派的名称。已经被归类为该类型的电影更改其类型名称是有道理的。该模型仍然成立。

据我所知，正确的规范化是将 ClassificationSystemId 放入 MovieClassification 中，并使其成为 MovieClassification 键的一部分（并使 ClassificationSystemId 是所提供架构中 Classification 行的键的一部分）：

-- Tables Movie, ClassificationSystem not included for brevity

CREATE TABLE Classification
(
  ClassificationId INT,
  ClassificationSystemId INT,
  PRIMARY KEY(ClassificationId, ClassificationSystemId),
  FOREIGN KEY(ClassificationSystemId) REFERENCES ClassificationSystem(ClassificationSystemId)
);

CREATE TABLE MovieClassification
(
  ClassificationId INT,
  ClassificationSystemId INT,
  MovieId INT,
  Advice NVARCHAR(MAX),
  PRIMARY KEY(ClassificationId, ClassificationSystemId, MovieId),
  FOREIGN KEY(ClassificationId, ClassificationSystemId) REFERENCES Classification(ClassificationId, ClassificationSystemId),
  FOREIGN KEY(MovieId) REFERENCES Movie(MovieId),
  UNIQUE(ClassificationSystemId, MovieId)
);

Answer 5

1. 添加对分类的唯一约束 (ClassificationId,ClassificationSystemId)
2. 添加从 MovieClassification 表引用它的 FK
3. 添加对 MovieClassification 的唯一约束 (MovieId,ClassificationSystemId)

Answer 6

好的。我本来希望我的问题能引发一些思考，但我的观点似乎被忽视了。

您的分类表需要为{movieID,classificationSchemeclassification}，键为{movieIDclassificationScheme}。

它可以通过{movieID}引用电影，并且可以通过{classificationScheme分类}引用分类表。

该分类表列出/枚举/命名每个方案的所有有效分类。由于分类方案仅存在且有意义，从它具有至少一个分类的那一刻起，可能并不真正需要第四个表，其唯一目的是列出/命名/枚举所有相关分类方案。

Answer 7

老实说，我只是稍微改变一下数据模型。

create table #Movies (PK_moID int identity(1,1), moName varchar(50) primary key(PK_moID))
create table #ClassificationSystem (PK_csID int identity(1,1), csName varchar(50) primary key(PKcsID))
create table #Classification (PK_clID int identity(1,1), FK_csID int)
create table #MovieClassification (FK_moID int, FK_csID int, FK_clID int primary key (FK_moID, FK_csID))

现在，通过电影分类，您拥有电影和系统的复合 pk，因此每个系统只能获得一部电影的一个评级（即使您添加新系统）。您还可以创建从电影分类到分类表的关系以补充数据。