有约束的多对一建模？

Question

我正在尝试为电影分类创建一个数据库模型，其中每部电影都可以从多个分级系统(例如BBFC、MPAA)中的每一个得到一个单独的分类。这是当前的设计，包含所有隐含的PKs和FKs：

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不是一个好的解决方案，因为这可能与Classification表中的值不同步)
• 仍然可扩展到多个分类系统(即新的分类系统不需要对表结构进行任何更改)？

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

Answer 1

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

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!

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

Answer 2

根据您的意思，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 3

如果从分类表中删除分类系统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
)

但是你买了另一个问题，一个分类可能会超出它的预期系统。