Eu não acho que você tem um problema com os relacionamentos. Acho que o problema é que, ao usar chaves substitutas (ou seja, Ids) para cada tabela, o banco de dados resultante não consegue impedir que sejam inseridos Trabalhadores cujo Departamento é de uma Empresa enquanto a Classificação é de outra e vice-versa. Uma boa maneira de entender isso é visualizar o esquema usando uma ferramenta de Diagramação ER. Usarei a ferramenta Oracle Data Modeler que é um download gratuito.

Diagrama ER

Do jeito que está, você poderia ter 2 empresas - digamos IBMe Microsoft. IBMpode ter um Software Developmentdepartamento e a Microsoft pode ter um Desktop Softwaredepartamento. A IBM pode ter uma Software Engineerclassificação e a Microsoft pode ter uma Software Developerclassificação. Agora, porque você tem uma chave substituta para Departmente Classification, o fato de que Software Developmenté um IBMdepartamento e Desktop Softwareé um Microsoftdepartamento é perdido para futuros relacionamentos de filhos. Este também é o caso de Classification. Portanto, é fácil atribuir acidentalmente Harlan Mills, que é IBMfuncionário do Software Developmentdepartamento, Software Developercuja classificação é umMicrosoftclassificação! Da mesma forma, o trabalhador pode receber a classificação certa e o departamento errado! Aqui está um diagrama mostrando o primeiro exemplo:

Os 1 Ids representam IBM, e os 2 Ids representam Microsoft. Destaquei em vermelho o cenário onde Harlan Millse Bill Gatessão atribuídos aos departamentos errados, que é visualizado pelo Id de 10 departamentos associado ao Id de classificação 200 e vice-versa.

Opções para resolver

Então, quais são as opções para evitar que isso aconteça? Existem duas opções imediatas. A primeira é perceber que, ao usar uma chave substituta para cada tabela, esse problema existe e introduzir programação adicional para verificar se isso não ocorre. Isso pode ser feito no aplicativo, mas se as inserções e atualizações puderem ocorrer fora do aplicativo, ainda poderão ocorrer associações incorretas. Uma abordagem melhor seria criar um gatilho que dispare na inserção e atualização de um funcionário para garantir que o departamento atribuído seja da mesma empresa que a classificação atribuída e, se não, falhar na inserção ou atualização.

A segunda opção é não usar chaves substitutas para cada tabela. Em vez disso, use as chaves substitutas apenas para a Companytabela, que é fundamental e não tem pais e, em seguida, crie relacionamentos de identificaçãoDepartment para as tabelas e Classificationfilho. As tabelas Departmente agora têm um PK de mais um Número de Sequência ou Nome para distingui-los. Então, as relações de e para também se tornam e, portanto, o PK de se torna o , mais o (estou usando um número de sequência neste exemplo), mais o . O resultado é que existe apenas na tabela. Agora é impossível atribuir umClassificationCompany IdDepartmentClassificationWorkeridentifyingWorkerCompany IdDepartment NumberClassification Numberone Company IdWorkerWorkerpara um Departmentem um Companye para um Classificationem outro Company.

Por que isso é impossível? É impossível porque o esquema implementa integridade referencial entre Workere Departmente Classification. Se for feita uma tentativa de inserir um Workerpara a Departmentem um Companye um Classificationde outro, a combinação que não existe na tabela pai correspondente acionará uma violação de integridade referencial e a inserção não funcionará.

Aqui está um diagrama atualizado de uma implementação da segunda opção:

Opção preferida

Das duas opções, prefiro absolutamente a segunda - usando os relacionamentos de identificação e as chaves em cascata - por dois motivos. Primeiro, esta opção atinge a regra desejada sem programação adicional. Desenvolver um gatilho não é trivial. Deve ser codificado, testado e mantido. Garantir que a lógica do gatilho seja ideal para não afetar o desempenho também não é trivial. O livro Applied Mathematics for Database Professionals fornece muitos detalhes sobre a complexidade de tal solução. Em segundo lugar, as regras implicam que um Departamento e uma Classificação não podem existir fora do contexto do Company, e assim o esquema agora reflete com mais precisão o mundo real.

Esta é uma ótima pergunta porque mostra exatamente por que simplesmente assumir que cada tabela requer uma chave substituta é uma má ideia. Fabian Pascal tem uma excelente postagem no blog apenas sobre este tópico, mostrando que não apenas uma chave substituta pode ser uma má ideia do ponto de vista da integridade dos dados, mas também pode resultar em algumas recuperações mais lentasno nível físico, precisamente porque são necessárias junções que, se as chaves tivessem sido devidamente colocadas em cascata, seriam desnecessárias. Outro tópico interessante que esta pergunta revela é que um banco de dados não pode garantir que todos os dados inseridos nele sejam precisos em relação ao mundo real. Em vez disso, ele pode apenas garantir que os dados nele inseridos sejam consistentes com as regras declaradas a ele. Nesse caso, podemos fazer o melhor possível usando a abordagem de chave em cascata para garantir que o DBMS possa manter os dados consistentes com relação à regra de que um Workerde um determinado Companyprecisa ser atribuído a Classificatione um Departmentdesse mesmo Company. Mas, se no mundo real Microsofthouver um departamento chamado, Desktop Softwaremas o usuário do banco de dados afirmar que o departamento éSoftware Developmento DBMS não pode fazer nada além de assumir que recebeu um fato verdadeiro.

Seu problema decorre do fato de que há um tipo de entidade ausente em seu modelo. Considere o seguinte DER:

Observe que adicionei um tipo de entidade de interseção entre DEPARTMENTe CLASSIFICATION. Este novo tipo de entidade: POSITIONfornece as informações que estão implícitas em seu modelo, que um determinado departamento possui um determinado conjunto de trabalhos de várias classificações.

Adicionar POSITIONao seu modelo como uma entidade explícita tem algumas vantagens.

1. Evita o problema que o preocupa com a WORKERpossibilidade de ser atribuído a departamentos e classificações em diferentes empresas.
2. Ele fornece um locus para outros predicados que podem ser aplicáveis ​​a uma posição, como nível salarial, etc.
3. Ele permite que você registre o fato de que uma posição existe, mesmo que não haja WORKERs atualmente na posição, o que é uma informação possivelmente útil.

Observe que, para evitar o problema de uma posição sendo definida para um departamento e uma classificação que está em empresas diferentes, expandi as chaves de ambos DEPARTMENTe CLASSIFICATION, o que é bom pelos motivos pelos quais você pode ler detalhadamente na resposta de Todd Everett.

CUIDADO O modelo acima pressupõe uma simplificação. Especificamente, assume que cada posição é registrada apenas uma vez. Isso pode ou não ser adequado às suas regras de negócios. Se você precisar de vários POSITIONregistros para o mesmo departamento e classificação dentro de uma empresa, poderá introduzir uma chave substituta em POSITION.

A forma como entendi a questão é que o campo ID_Classification da tabela 'Workers' deve permitir apenas as classificações definidas para a respectiva empresa do trabalhador. Assim, validar (anexando uma REGRA ou por meio de TRIGGERS) as informações inseridas/atualizadas no campo Workers.ID_Classification é adequado para atender a esse requisito.

Pelas minhas leituras, ainda não entendi o que é essa Classificação e por que ela precisa ter o ID_Company . Se for como uma posição como alguém mencionou aqui, acho que uma tabela estática para conter todas as posições seria melhor.

Se você estiver fazendo isso para encontrar facilmente uma classificação/posição em uma empresa, adicione uma consulta/exibição simples para conectar os departamentos de classificação dos trabalhadores e recuperar o ID da empresa da classificação.

hoje em dia, existem visualizações ou tecnologias mais inteligentes, como visualizações materializadas e índices de junção; portanto, se o seu problema for o desempenho da consulta, use-os.