sql-server - 用约束建模多对一？

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表，其约束将允许来自同一系统的多个分类，而理想情况下，它应仅允许给定系统的零个或一个分类。

是否有任何合理的方法来进行重组，以便在满足以下要求的情况下，通过数据库约束来强制实施任何给定系统中具有零分类或一分类的电影？


不要复制可能要查找的信息（即在 ClassificationSystemId表中复制 MovieClassification并不是一个好的解决方案，因为这可能与 Classification表中的值不同步）
仍然可以扩展到多个分类系统（即新的分类系统不需要更改表结构）吗？


还请注意 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!

随着分类数量的增加，这可能是低效的。或者，您可以消除分类表，然后将ClassificationSystemId移至MovieClassification表。