主页 / dba / 问题 / 332939
Accepted
Cidos
Asked: 2023-11-09 23:49:49 +0800 CST

正确（如标准化）方式来表达具有复杂（？）约束的 3 个表之间的关系？

  • 772

这是我向这个网站提出的第一个问题，如果这是重复的，我很抱歉，我只是没有受过足够的教育，不知道用于搜索这个概念的正确术语，如果这样的概念是一个已知的模式，我会如果答案有一个名字，我将不胜感激。

现在，回答问题。我将使用 postgres 作为示例，但如果需要，请随意显示其他数据库中的示例。

简化的架构：

create table factory (
  id serial primary key,
  detail text not null
);

create table process (
  id serial primary key,
  detail text not null
);

create table item (
  id serial primary key,
  detail text not null
);

为了简洁起见，我将工厂称为 [F]，流程称为 [P]，项目称为 [I]。

该模式具有以下概念关系：

  • 每个进程专属于一个工厂，因此 [P] n -> 1 [F]
  • 每个项目都专属于一个工厂，因此 [I] n -> 1 [F]
  • 每个项目可以由多个进程制作，因此 [P] n -> 1 [I]

为了表达这种关系，我想出了一个“factory_item_process”表：

create table factory_item_process (
  factory_id integer not null references factory(id),
  process_id integer not null references process(id),
  item_id integer not null references item(id),
  constraint pk_factory_item_process primary key (
    factory_id, process_id, item_id
  ),
  constraint uq_factory_item_process_process unique (process_id)
);

这解决了[P] n -> 1 [I]和[P] n -> 1 [F]，但没有解决 [I] n -> 1 [F]。

我得出的结论是，没有办法用[I] n -> 1 [F]唯一的键约束来传达，因此我创建了一个简单的函数来检查 [I] 是否已经属于 [F]：

create function in_other_factories(
  factory_id integer, 
  item_id integer
) returns boolean
language sql returns null on null input
  return true in (
    select 
      true 
    from factory_item_process
    where factory_id <> $1
      and item_id = $2
  );
alter table factory_item_process 
  add constraint chk_factory_item check(not in_other_factories(factory_id, item_id));

请记住，我是一个新手，我想问：factory_item_process表达这种关系的正确方式是什么？我感觉自己做错了什么，但又不太能理解。

使用用户定义的函数进行检查看起来像是代码味道，但我想不出其他任何东西。我也不确定表格是否正确标准化。

提前致谢

编辑以回应 Mustaccio 答案：我选择为该关系使用另一个表，因为该关系将具有其自身独有的属性。

database-design

2 个回答

  1. Best Answer

    看看你们的关系：

    • 每个进程都专属于一个工厂，因此 [P] n -> 1 [F]
      这意味着process应该有一factory_id列

    • 每个项目都专属于一个工厂，因此 [I] n -> 1 [F]
      这意味着item应该有一factory_id列

    • 每个项目都可以由多个进程制作，因此 [P] n -> 1 [I]这里有一个关于 的专栏
      是合乎逻辑的。目前尚不清楚进程是否可以在没有创建项目的情况下存在，如果是这样，那么该列应该可以为空。item_createdprocess

    不需要第四个表，因为一侧的流程与另一侧的项目和工厂之间的关系不是多对多，而是多对一。

    剩下的唯一一件事就是强制流程只能从同一个工厂创建项目。我们可以通过使用多列外键来做到这一点。请注意，这意味着item需要一个看似无关的unique约束才能应用它，但无论如何将其用于索引目的可能很有用。

    create table factory (
  id serial primary key,
  detail text not null
);

create table item (
  id serial primary key,
  detail text not null,
  factory_id int not null references factory (id),
  unique (factory_id, id)
);

create table process (
  id serial primary key,
  detail text not null,
  factory_id int not null references factory (id),
  item_created int not null,
  foreign key (factory_id, item_created) references item (factory_id, id)
);

    数据库<>小提琴

    • 1

  2. 每件商品都是一家工厂独有的

    这可以理解为Factory是Item的一个属性。

    相似地，

    每道工序专属于一家工厂

    使工厂成为流程的一个属性。这样，您需要的唯一其他关系是项目和流程之间的关系，这可以说是多对多的关系，可以想象，虽然项目可以由多个流程制作，但流程可以参与制作许多项目。

    如果，正如您在评论中指出的那样，流程只能参与单个项目的创建，则流程本身通过其工厂属性，已经强制执行该项目“属于”同一个工厂的事实，因此没有额外的约束或需要 Item 的属性。A

    • 0

相关问题