Início / dba / Perguntas / 332939
Accepted
Cidos
Asked: 2023-11-09 23:49:49 +0800 CST

Maneira correta (como normalizada) de expressar um relacionamento entre 3 tabelas com restrições complexas (?)?

  • 772

Esta é a minha primeira pergunta a este site, que diz que sinto muito se for uma duplicata, só não tenho educação suficiente para saber os termos corretos a serem usados ​​para pesquisar esse conceito. Se tal conceito for um padrão conhecido, eu estaria grato se a resposta tivesse um nome para isso.

Agora, vamos à pergunta. Usarei postgres para os exemplos, mas fique à vontade para mostrar exemplos em outros bancos de dados, se necessário.

O esquema simplificado:

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

Por questões de brevidade, referir-me-ei à Fábrica como [F], ao Processo como [P] e ao Item como [I].

O esquema tem os seguintes relacionamentos conceituais:

  • Cada Processo é exclusivo de uma Fábrica, então [P] n -> 1 [F]
  • Cada Item é exclusivo de uma Fábrica, então [I] n -> 1 [F]
  • Cada item pode ser feito por vários processos, então [P] n -> 1 [I]

para expressar essas relações criei uma tabela "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)
);

Isso cuida de [P] n -> 1 [I]and [P] n -> 1 [F], mas não resolve [I] n -> 1 [F].

Cheguei à conclusão de que não há como transmitir [I] n -> 1 [F]restrições de chave únicas, então criei uma função simples que verifica se [I] já pertence a um [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));

Tendo em mente que sou um novato, gostaria de perguntar: é factory_item_processa maneira correta de expressar essas relações? Sinto que estou fazendo algo errado, mas não consigo entender.

Fazer uma verificação com uma função definida pelo usuário parece um cheiro de código, mas não consegui pensar em mais nada. Também não tenho certeza se as tabelas estão normalizadas corretamente.

desde já, obrigado

Editar em resposta à resposta do mustaccio: optei por usar outra tabela para esse relacionamento porque esse relacionamento terá atributos exclusivos dele mesmo.

database-design

2 respostas

  1. Best Answer

    Olhando para seus relacionamentos:

    • Cada Processo é exclusivo de uma Fábrica, então [P] n -> 1 [F]
      Isso significaria que processdeveria ter uma factory_idcoluna

    • Cada Item é exclusivo de uma Fábrica, então [I] n -> 1 [F]
      Isso significa que itemdeve ter uma factory_idcoluna

    • Cada Item pode ser feito por vários Processos, então [P] n -> 1 [I]
      Seria lógico aqui ter uma item_createdcoluna em process. Não está claro se um Processo pode existir sem a criação de um Item; nesse caso, esta coluna deve ser anulável.

    Não há necessidade de uma quarta tabela, pois o relacionamento entre um Processo, por um lado, e um Item e uma Fábrica, por outro, não é muitos para muitos, é muitos para um.

    A única coisa que resta é garantir que um Processo só pode criar um Item da mesma Fábrica. Podemos fazer isso usando uma chave estrangeira com várias colunas. Observe que isso significa que itemé necessária uma uniquerestrição aparentemente estranha para que isso se aplique, mas de qualquer maneira pode ser útil ter isso para fins de indexação.

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

    banco de dados<> violino

    • 1

  2. Cada item é exclusivo de uma fábrica

    Isso pode ser entendido como a Fábrica sendo um atributo de um Item.

    De forma similar,

    Cada Processo é exclusivo de uma Fábrica

    torna a Fábrica um atributo de um Processo. Com isso, o único outro relacionamento que você precisa é aquele entre Itens e Processos, que é indiscutivelmente muitos para muitos, já que se pode imaginar que, enquanto um Item pode ser feito por vários processos, um Processo pode estar envolvido na produção. muitos itens.

    Se, como você indicou em um comentário, um Processo só pode participar da criação de um único Item, o próprio Processo, por meio de seu atributo Fábrica, já impõe o fato de que o Item "pertence" à mesma Fábrica, portanto, nenhuma restrição extra ou o atributo do Item é necessário. A

    • 0

relate perguntas