Mam 4 podobne tabele (to przykład):

Company:
ID
Name
CNPJ

Department:
ID
Name
Code
ID_Company 

Classification:
ID
Name
Code
ID_Company

Workers:
Id 
Name
Code
ID_Classification
ID_Department

Załóżmy, że mam classificationz id = 20, id_company = 1. I to departmentma id_company = 2(który reprezentuje inną firmę).

Umożliwi to utworzenie pracownika z dwóch firm, ponieważ klasyfikacja i dział są powiązane z firmą osobno. Nie chcę, żeby tak się stało, więc myślę, że mam problem ze swoimi związkami i nie wiem, jak to rozwiązać.

Twój problem wynika z faktu, że w twoim modelu brakuje typu encji. Rozważ następujące ERD:

Zauważ, że dodałem typ obiektu przecięcia między DEPARTMENTi CLASSIFICATION. Ten nowy typ jednostki: POSITIONzapewnia informacje, które są niejawne w twoim modelu, że dany dział ma zestaw zadań o różnych klasyfikacjach.

Dodanie POSITIONdo modelu jako jawnej jednostki ma kilka zalet.

1. Pozwala to uniknąć problemu związanego z WORKERpotencjalnym przypisaniem do działów i klasyfikacji w różnych firmach.
2. Daje ci miejsce na inne predykaty, które mogą mieć zastosowanie do stanowiska, takie jak stopień wynagrodzenia itp.
3. Pozwala to zarejestrować fakt, że pozycja istnieje, nawet jeśli obecnie nie ma WORKERpozycji, co jest całkiem przydatną informacją.

Zauważ, że aby uniknąć problemu definiowania stanowiska dla działu i klasyfikacji, które znajdują się w różnych firmach, rozszerzyłem klucze obu DEPARTMENTi CLASSIFICATION, co jest dobre z powodów, o których możesz przeczytać obszernie w odpowiedzi Todda Everetta.

UWAGA: Powyższy model zakłada uproszczenie. W szczególności zakłada się, że każda pozycja jest rejestrowana tylko raz. To może, ale nie musi być dostosowane do twoich reguł biznesowych. Jeśli potrzebujesz wielu POSITIONrekordów dla tego samego działu i klasyfikacji w firmie, możesz wprowadzić klucz zastępczy POSITION.

Nie sądzę, że masz problem z relacjami. Myślę, że zamiast tego problem polega na tym, że przy użyciu kluczy zastępczych (tj. ID) dla każdej tabeli wynikowa baza danych nie jest w stanie uniemożliwić wstawienia pracowników, których dział jest jednej firmy, podczas gdy klasyfikacja jest innej i odwrotnie. Dobrym sposobem na zrozumienie tego jest wizualizacja schematu za pomocą narzędzia ER Diagramming. Użyję Oracle Data Modeler narzędzie, które jest do pobrania za darmo.

Diagram ER

W tej chwili możesz mieć 2 firmy - powiedzmy IBMi Microsoft. IBMmoże mieć Software Developmentdział, a Microsoft może mieć Desktop Softwaredział. IBM może mieć Software Engineerklasyfikację, a Microsoft może mieć Software Developerklasyfikację. Teraz, ponieważ masz klucz zastępczy Departmenti Classificationfakt, że Software Developmentjest to IBMwydział i Desktop Softwarejest Microsoftwydziałem, zostaje utracony dla przyszłych relacji z dziećmi. Tak jest również w przypadku Classification. Dlatego łatwo jest przypadkowo przypisać Harlan Mills, kto jest IBMpracownikiem w Software Developmentdziale, którego klasyfikacja Software DevelopertoMicrosoftKlasyfikacja! Podobnie pracownik może otrzymać odpowiednią klasyfikację i niewłaściwy dział! Oto schemat przedstawiający pierwszy przykład:

1 ID reprezentuje IBM, a 2 ID reprezentuje Microsoft. Mam wyróżnione czerwonym scenariuszu gdzie Harlan Millsi Bill Gatessą przypisane do niewłaściwych działów, które są wizualizowane przez Id 10 działu związanego z 200 klasyfikacji Id i odwrotnie.

Opcje rozwiązania

Więc jakie są opcje, aby zapobiec jego wystąpieniu? Istnieją dwie natychmiastowe opcje. Pierwszym jest uświadomienie sobie, że przy użyciu klucza zastępczego dla każdej tabeli istnieje ten problem i wprowadzenie dodatkowego programowania w celu sprawdzenia, czy nie występuje. Można to zrobić w aplikacji, ale jeśli wstawki i aktualizacje mogą wystąpić poza aplikacją, nadal mogą wystąpić niepoprawne skojarzenia. Lepszym rozwiązaniem byłoby utworzenie wyzwalacza uruchamianego przy wstawianiu i aktualizacji pracownika, aby mieć pewność, że przypisany dział należy do tej samej firmy co przypisana klasyfikacja, a jeśli nie, wstawienie lub aktualizacja ulegną awarii.

Drugą opcją jest nieużywanie kluczy zastępczych dla każdego stołu. Zamiast tego używaj kluczy zastępczych tylko dla Companytabeli, która jest podstawowa i nie ma rodziców, a następnie utwórz identyfikujące relacje z tabelami Departmenti Classificationpotomnymi. DepartmentI Classificationstoły mają teraz pK Company Idoraz numer sekwencji lub Nazwa je rozróżnić. Następnie, relacje z Departmenti Classificationdo Workertakże stać identifying, a tym samym PK Workerstaje się Company Idplus Department Number(używam numer sekwencji w tym przykładzie), plus Classification Number. Rezultat jest tylko one Company Idw Workertabeli. To jest teraz niemożliwe do przypisaniaWorkerDo Departmentjednego Companyoraz do Classificationw innym Company.

Dlaczego to jest niemożliwe? Jest to niemożliwe, ponieważ schemat realizuje więzy integralności między Workeri Departmenta Classification. Jeśli zostanie podjęta próba wstawienia a Workerdla Departmentjednego Companyi Classificationdrugiego, kombinacja, która nie istnieje w odpowiedniej tabeli nadrzędnej, spowoduje naruszenie integralności referencyjnej i wstawka nie będzie działać.

Oto zaktualizowany schemat realizacji drugiej opcji:

Preferowana opcja

Z tych dwóch opcji absolutnie wolę drugą - używając relacji identyfikujących i kluczy kaskadowych - z dwóch powodów. Po pierwsze, ta opcja pozwala osiągnąć pożądaną regułę bez dodatkowego programowania. Opracowanie wyzwalacza nie jest trywialne. Musi być kodowany, testowany i konserwowany. Zapewnienie optymalnej logiki wyzwalania, aby nie wpływać na wydajność, nie jest również trywialne. Książka Applied Mathematics for Database Professionals zawiera wiele szczegółów na temat złożoności takiego rozwiązania. Po drugie, reguły sugerują, że Departament i Klasyfikacja nie mogą istnieć poza kontekstem Company, a więc schemat teraz dokładniej odzwierciedla rzeczywisty świat.

To świetne pytanie, ponieważ pokazuje dokładnie, dlaczego po prostu założenie, że każda tabela wymaga klucza zastępczego, jest złym pomysłem. Fabian Pascal ma świetny post na blogu na ten temat, pokazujący, że klucz zastępczy może być nie tylko złym pomysłem z punktu widzenia integralności danych, ale może również spowolnić niektóre wyszukiwaniana poziomie fizycznym właśnie dlatego, że wymagane są sprzężenia, które, gdyby klucze były odpowiednio kaskadowane, byłyby niepotrzebne. Innym interesującym tematem tego pytania jest to, że baza danych nie może zapewnić, że wszystkie wprowadzone do niej dane są dokładne w stosunku do realnego świata. Zamiast tego może jedynie zapewnić, że wprowadzone do niego dane są zgodne z zadeklarowanymi mu regułami. W tym przypadku możemy zrobić najlepszy możliwy za pomocą klucza kaskadowe podejście do zapewnienia DBMS może zachować dane zgodne w odniesieniu do reguły, że Workerdanej Companypotrzeby zostać przypisany Classificationi Departmenttego samego Company. Ale jeśli w prawdziwym świecie Microsoftistnieje dział o nazwie, Desktop Softwareale użytkownik bazy danych twierdzi, że jest to działSoftware Development DBMS nie może nic zrobić, tylko założyć, że otrzymał prawdziwy fakt.

W moim rozumieniu pytanie brzmi: pole ID_Classification tabeli „Pracownicy” powinno pozwalać tylko na klasyfikacje zdefiniowane dla firmy danego pracownika. W związku z tym sprawdzenie poprawności (przez dołączenie ZASADY lub przez PRAWNIKI) informacji wstawionych / zaktualizowanych w polu Workers.ID_Classification jest wystarczające, aby spełnić ten wymóg.

Z moich odczytów wciąż nie rozumiem, co to jest ta klasyfikacja i dlaczego musi mieć ID_Company . Jeśli to jak pozycja, o której tu ktoś wspomniał, pomyślałbym, że statyczny stół zawierający wszystkie pozycje byłby lepszy.

Jeśli robisz to, aby łatwo znaleźć klasyfikację / stanowisko w firmie, dodaj proste zapytanie / widok, aby połączyć działy klasyfikacyjne-pracownicy-działy i pobrać identyfikator firmy z klasyfikacji.

obecnie istnieją inteligentniejsze widoki lub technologie, takie jak widoki zmaterializowane i indeksy złączeń, więc jeśli Twoim problemem jest wykonanie zapytania, skorzystaj z nich.