钻石模式：如何（去）规范化？

Question

假设我们有以下实体：

• 制作工作室
• 记者
• 摄影师
• 新闻镜头

在这个简单的世界中，制作工作室有许多记者和许多摄影师。每位记者都属于一个工作室。运营商也是如此。新闻片段由一名记者和一名操作员制作，两人都来自同一工作室。

这是我将此模型放入关系数据库的天真的方法：

CREATE TABLE production_studios(
  id                   SERIAL PRIMARY KEY,
  title                TEXT NOT NULL
);

CREATE TABLE journalists(
  id                   SERIAL PRIMARY KEY,
  name                 TEXT NOT NULL,
  prodution_studio_id  INTEGER NOT NULL REFERENCES production_studios
);

CREATE TABLE camera_operators(
  id                   SERIAL PRIMARY KEY,
  name                 TEXT NOT NULL,
  production_studio_id INTEGER NOT NULL REFERENCES production_studios
);

CREATE TABLE news_footages(
  id                   SERIAL PRIMARY KEY,
  description          TEXT NOT NULL,
  journalist_id        INTEGER NOT NULL REFERENCES journalists,
  camera_operator_id   INTEGER NOT NULL REFERENCES camera_operators
);

此模式形成形状优美的菱形 ERD 和一些问题。

问题是新闻镜头可以将来自不同制作工作室的记者和摄像师联系在一起。我知道可以通过编写相应的约束来解决这个问题，但为了实验的目的，让我们假设我们正在做范式数据库设计的练习。

1. 第一个问题是关于术语的：说这个模式是非规范化的是否正确？如果是，它打破了哪种范式？或者对于这种异常是否有更好的名称，例如记录间冗余、多路径关系等？

2. 如何更改此架构以使所描述的异常不可能发生？

当然，我非常感谢参考解决这个特定问题的论文。

Answer 1

天真的方法是让你的记者和摄像操作员依赖实体，依赖于他们工作的工作室。这意味着制作工作室外键成为其主键的一部分。然后，您的 news_footage 表有一个由 4 个组件组成的主键：

• Production_studio_id
• Photographer_id
• Camera_operator_id
• Footage_id

和两个外键：

• 指向记者表的 Photographer_id、Production_studio_id 和
• 指向摄像机操作员表

Easy 的camera_operator、Production_studio_id。

或者不是。现在，您已经在 ER 模型中定义了这样一个概念：摄像师或记者的存在取决于他们工作的工作室。这并不能很好地反映真实的工作：在这个模型中，人们无法改变他们的雇主。

我们不要那样做。

在您的原始模型中，您将一个人与他们扮演的角色（记者或摄像师）混淆，并且您错过了一个实际上负责新闻素材制作的短暂实体： 【工作室专用】制作团队。

我的 ER 模型看起来像这样：

create table studio
(
  id int not null primary key ,
  title varchar(200) not null ,
)

create table person
(
  id int not null primary key ,
  title varchar(200) not null ,
)

create table team
(
  studio_id          int not null ,
  journalist_id      int not null ,
  camera_operator_id int not null ,

  primary key ( studio_id , journalist_id , camera_operator ) ,

  foreign key ( studio_id          ) references studio ( id ) ,
  foreign key ( journalist_id      ) references person ( id ) ,
  foreign key ( camera_operator_id ) references person ( id ) ,

)

create table footage
(
  studio_id          int not null ,
  journalist_id      int not null ,
  camera_operator_id int not null ,
  id                 int not null ,
  description        varchar(200) not null ,

  primary key ( studio_id , journalist_id , camera_operator_id , id ) ,

  foreign key     ( studio_id , journalist_id , camera_operator_id )
  references team ( studio_id , journalist_id , camera_operator_id ) ,

)

现在你拥有一个人们可以扮演不同角色的世界：同一个人在某些情况下可能是摄影师，而在另一些情况下可能是记者。人们可以更换雇主。工作室特定团队由一名记者和一名摄像师组成。在某些情况下，同一个人可能在团队中扮演这两个角色。最后，一段新闻片段是由一个且唯一一个工作室特定团队制作的。

这更好地反映了现实世界，而且更加灵活。

编辑添加示例查询：

查找为特定工作室工作的记者：

select p.*
from studio s
join team   t on t.studio_id = s.id
join person p on p.id        = t.journalist_id
where s.title = 'my desired studio name'

这将为您提供一组正在（或已经）以记者角色与工作室相关联的人员。但应该注意的是，在现实世界中，人们为雇主工作一段时间：为了正确建模，您需要一个开始/结束日期，并且需要使用现在的相对概念来限定查询。