使用约束条件进行多对一建模？

Question

我将尝试创建一个电影分类的数据库模型，其中每部电影可以从多个评级系统（例如BBFC、MPAA）中选择一个单一的分类。这是当前的设计，包括所有暗示的主键和外键：

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列 - 每个电影到分类的映射都需要具有说明为什么将该分类赋予该电影的文本描述。任何设计都需要支持这一点。

- Greg Beech

---

@Marcelo - 刚刚为那个添加了显式注释。 - Greg Beech

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表中。

- Andomar

Answer 2

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

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))

现在，有了电影分类，您拥有电影和系统的复合主键，因此每个系统只能获取一部电影的评分（即使添加新系统）。 您还可以从电影分类创建到分类表的关系，以强制执行数据。

- DForck42

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
)

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

- joshuacronemeyer

---

我认为这只是把一个问题换成了更糟糕的问题；分类属于一个系统，这将无法确定地确定分类属于哪个系统。 - Greg Beech

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)
);

- Håvard S

---

我的意思是，如果您更改了“分类”中的“主”版本，没有任何强制机制会更新信息的任何副本。当然，您可以通过命令方式执行此操作，但这并没有带来任何好处，因为您可以通过命令方式检查当前约束条件。 - Greg Beech

嗯，这对我来说并没有太多意义。如果你在Classification中更改“master”，那么你要么是添加一个新的分类，要么是删除一个现有的分类（或更改现有分类的列数据）。在所有情况下，只要关系保持不变，就不需要任何强制执行，据我所知。 - Håvard S

Answer 5

你的设计中，同一部电影可以被归为多少个不同的分类系统？

这是否符合你对“分类”的预期概念？

- Erwin Smout

---

这应该是一条注释，而不是一个答案。 - JohnFx

每部电影可以从多个评级系统中选择一个单一的分类。 - Greg Beech

Answer 6

1. 在分类表(Classification)上添加唯一约束(分类ID, 分类系统ID)
2. 在电影分类表(MovieClassification)中添加一个引用该唯一约束的外键
3. 在电影分类表(MovieClassification)上添加唯一约束(电影ID, 分类系统ID)

- A-K

Answer 7

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

你的分类表需要是{电影ID，分类方案，分类}，键为{电影ID，分类方案}。

它可以通过{电影ID}引用电影，并且可以通过{分类方案，分类}引用分类表。

这个分类表列出/枚举/命名每个方案的所有有效分类。由于分类方案只有在至少有一个分类时才存在并有意义，因此可能没有真正需要第四个表，其唯一目的是列出/命名/枚举所有相关的分类方案。

- Erwin Smout