Wady używania zerowalnego klucza obcego zamiast tworzenia tabeli skrzyżowań

Powiedz, że mam następujący schemat ER:

Teraz gdybym reprezentował związek użyciu klucza zagraniczną Schoolw Student, mogę mieć NULLwartości (ponieważ Student nie jest wymagane, aby należeć do School), na przykład:

Tak więc poprawnym sposobem (w oparciu o to, co przeczytałem) jest utworzenie tabeli skrzyżowań w celu przedstawienia relacji, na przykład:

W ten sposób NULLw tabeli nie mogą występować żadne wartości School_has_Student.

Ale jakie są wady używania zerowalnego klucza obcego zamiast tworzenia tabeli skrzyżowań?

Edytować:

Przez pomyłkę wybrałem ( school_id, student_id) jako klucz podstawowy dla School_has_Studenttabeli, co spowodowało, że relacja była wiele do wielu. Prawidłowy klucz podstawowy powinien być student_id:

Oba modele reprezentują różne relacje.

Korzystając z tabeli łączenia, modelujesz relację wiele do wielu.

Używając prostego klucza obcego, modelujesz relację jeden do wielu.

Wadą zerowalnego klucza obcego jest niemożność modelowania relacji jako wiele do wielu, jeśli to właśnie próbujesz osiągnąć.

W oparciu o edycję pytania skutecznie dzielisz tabelę uczniów na dwie tabele z tym samym kluczem. Zazwyczaj widzę to na stołach, które mają zbyt wiele pól, więc ktoś dzieli je na dwa, aby były łatwiejsze w zarządzaniu (nazywam to nakładaniem szminki na świnię).

Dzieląc tabelę ucznia, czynisz drugą tabelę opcjonalną, ponieważ rekord nie musi istnieć w drugiej tabeli. Jest to bardzo podobne do pola, które nie musi być ustawiane, ponieważ może mieć wartość NULL.

Jeśli chcesz relacji jeden do wielu, znacznie lepiej jest użyć jednego stołu i pozwolić, aby identyfikator szkoły był pusty w tabeli ucznia. Nie ma powodu, aby unikać zer w polach, nawet w przypadku klucza obcego. Oznacza to, że relacja zagraniczna jest opcjonalna: programiści i DBA doskonale to rozumieją, a silnik bazy danych z pewnością powinien działać dobrze.

Jeśli martwisz się dołączeniami, nie martw się. Istnieją dobrze zdefiniowane semantyki dotyczące tego, jak sprzężenia działają z polami zerowymi. Korzystając z jednej tabeli, możesz połączyć dwie tabele zamiast trzech.

Napisałeś w komentarzu powyżej:

książka „Podstawy systemów baz danych” [...] mówi [...], że zaleca się stosowanie tabeli przecięć, jeśli w kolumnie klucza obcego jest dużo wartości NULL (na przykład: jeśli 98% pracowników nie zarządzaj działem)

Gdy w kolumnie klucza obcego znajduje się wiele wartości NULL, twoje programy będą musiały poradzić sobie z tą w większości pustą kolumną dla każdego przetwarzanego rekordu. Kolumna prawdopodobnie zajmie trochę miejsca na dysku, mimo że w 98% wszystkich przypadków jest pusta, zapytanie o relację oznacza zapytanie do kolumny, która zapewnia większy ruch w sieci, a jeśli używasz ORM, który generuje klasy z twoich tabel, twoje programy będzie również potrzebował więcej miejsca po stronie klienta niż to konieczne. Użycie tabeli skrzyżowań pozwala uniknąć tego, będą potrzebne tylko rekordy odsyłaczy, jeśli w przeciwnym razie równoważny klucz obcy nie byłby NULL.

W przeciwieństwie do tego, jeśli nie masz tylko kilku wartości NULL, powiedzmy, że 50% lub więcej relacji nie ma wartości NULL, użycie tabeli przecięć daje odwrotny efekt - więcej miejsca na dysku, większą złożoność powodującą większy ruch sieciowy itp.

Tak więc użycie tabeli skrzyżowań jest tylko formą optymalizacji, sensowną tylko w konkretnym przypadku, a zwłaszcza w dzisiejszych czasach, gdy miejsce na dysku i pamięć stały się tańsze, znacznie rzadziej potrzebne. Zauważ, że „Podstawy systemów baz danych” zostały pierwotnie napisane ponad 20 lat temu (odnalazłem odniesienie do drugiego wydania z 1994 r.) I myślę, że to zalecenie już tam było. Przed 1994 r. Optymalizacja przestrzeni była prawdopodobnie znacznie ważniejsza niż obecnie, ponieważ pamięć masowa była wciąż droższa, a komputery i sieci działały znacznie wolniej niż obecnie.

Na marginesie do wybrednego komentarza: powyższe stwierdzenie stara się tylko przewidzieć, co autor „Podstawy systemów baz danych” miał na myśli w swojej rekomendacji, wydaje mi się, że przedstawił szorstkie, ogólne stwierdzenie, ważne dla większości systemów. W niektórych bazach danych istnieją inne możliwe optymalizacje, takie jak „rzadkie kolumny”, które sprawiają, że korzystanie z tabeli skrzyżowań jest jeszcze bardziej przestarzałe.

Więc nie zrozum źle tej rekomendacji. Książka nie mówi ci, abyś preferował tabele przecięć dla {0,1}:nrelacji w ogóle, lub - jak pisałeś - że jest to „właściwy sposób”. Korzystaj z takich optymalizacji, które komplikują twoje programy tylko wtedy, gdy naprawdę ich potrzebujesz.

Model koncepcyjny będzie wyglądał tak, co jest bardzo niekonwencjonalne, mówiąc mniej:

Model fizyczny będzie wyglądał następująco, co jest mylące, mówiąc mniej (ludzie pomyślą, że to M: M, chyba że zobaczą dokładnie):

Moja sugestia:

Jeśli chcesz, wiele kolumn (FK lub inne), które nie dotyczą większości uczniów, podziel tabele na tabele ról z relacjami 1: 1. Ale to nie dlatego, że są FK, to dlatego, że kolumny nie dotyczą większości wierszy.

W przeciwnym razie , pustych FK są normalną częścią bazy danych i dołącz tabele są zwykle dla M: RELS m.

Typowe zastosowania rel 1: 1 dotyczą tabel ról mających kolumny, które mają zastosowanie tylko wtedy, gdy encja jest określonego typu, i wyodrębniają kolumny BLOB ze względu na wydajność lub pamięć. Awodowanie wartości zerowych w FK nie jest jednym z powszechnych zastosowań.

Oprócz innych odpowiedzi chciałbym zauważyć, że wartość null klucza obcego jest niejednoznaczna. Czy to znaczy:

1) Szkoła ucznia (jeśli istnieje) jest nieznana (jest to standardowe znaczenie „null” - wartość jest nieznana)

2) Wiadomo, czy uczeń ma szkołę, czy nie

Jeśli użyjesz standardowego znaczenia null, w jaki sposób reprezentowałbyś „uczeń nie ma szkoły” w swoim modelu klucza obcego. W takim przypadku prawdopodobnie będziesz musiał utworzyć wpis „brak szkoły” z własnym identyfikatorem w szkolnym stole. (Nieidealny)

Tabele bazy danych mają tę fajną rzecz zwaną ograniczeniami. Bardzo łatwo jest zrobić w tabeli skrzyżowań, która pozwala na pojawienie się tylko 1 każdego ucznia w tabeli, ale wiele szkół w tej tabeli. Skutecznie daje ci

Teoria jest dobra, ale w końcu zamierzasz modelować bazę danych na podstawie zadawanych pytań.

Jeśli chcesz często pytać pytaniem: „którzy uczniowie są w mojej szkole”, czy naprawdę chcesz zapytać o całą tabelę uczniów lub mieć łatwą tabelę skrzyżowań.

W bazach danych: optymalizuj pod kątem zadawanych pytań.

Istnieje przypadek użycia, w którym użycie trzeciej tabeli może mieć sens. Przykład może wydawać się czysto hipotetyczny, ale mam nadzieję, że dobrze ilustruje mój punkt widzenia. Załóżmy, że dodajesz więcej kolumn do studentstabeli, a w pewnym momencie decydujesz się na wymuszenie wyjątkowości rekordów poprzez indeks złożony w kilku kolumnach. Jest bardzo prawdopodobne, że będziesz musiał również dołączyć school_idkolumnę, a tutaj sprawy zaczynają się robić nieporządne. Ze względu na sposób SQL został zaprojektowany, wkładając kilka identycznych rekordów gdzie school_idto NULLbędzie możliwe. Z technicznego punktu widzenia ma to sens, ale jest sprzeczne z intuicją i może prowadzić do nieoczekiwanych rezultatów. Z drugiej strony, wymuszanie wyjątkowości na skrzyżowaniu jest łatwe.

Ostatnio musiałem modelować taką „opcjonalną” relację, w której wymóg dotyczący wyjątkowości wynikał z kolumny znacznika czasu. Pozostawienie zerowego klucza obcego w tabeli nagle prowadzi do możliwości wstawienia rekordów z tym samym znacznikiem czasu (załóżmy, że jest to domyślny, ustawiony na rekordach, które nie były jeszcze kontrolowane / zatwierdzone) - jedynym wyjściem było usunięcie kolumna zerowalna.

Jak widać, jest to dość specyficzny przypadek i, jak zauważyli inni, w większości przypadków byłbyś całkowicie w porządku ze wszystkimi NULLwartościami. To zależy od konkretnych wymagań twojego modelu.

Oprócz wielu dobrych sugestii, które już zostały przesłane, osobiście nie jestem fanem kluczy obcych, chyba że są one naprawdę konieczne. Najpierw jest relacja M: M, do której się odwołujesz. Ponadto wywoływanie klucza obcego, a tym samym pobieranie danych z tabeli do zapytań, wprowadza większą złożoność i, w zależności od wielkości tabeli, niższą wydajność. Jak powiedzieli inni, zerowalne pola FK mogą nie być obsługiwane i mogą powodować problemy z integralnością danych.

Jeśli definiujesz stan, w którym szkoła studencka jest nieznana lub pusta, NULL nie rozróżnia tych warunków. (ponownie wróciliśmy do integralności danych). Sugestia Tulains w tabeli ról jest elegancka i pozwala na czyste zerowanie wartości.