Wady używania klucza obcego dopuszczającego wartość null zamiast tworzenia tabeli przecięć

Te dwa 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.

Wada obcego dopuszczającego wartość zerową klucz nie jest w stanie modelować relacji wiele-do-wielu, jeśli właśnie to próbujesz osiągnąć.

Na podstawie edycji pytania skutecznie dzielisz tabelę uczniów na dwie tabele z tym samym kluczem. Zwykle widzę to na stołach, które mają o wiele za dużo pól, więc ktoś dzieli je na dwie części, aby łatwiej było zarządzać (nazywam to nakładaniem szminki na świnię).

Dzieląc stół ucznia, druga tabela jest opcjonalna, ponieważ rekord nie musi istnieć w drugiej tabeli. Jest to bardzo podobne do pola, które nie musi być ustawiane, ponieważ może być puste.

Jeśli chcesz mieć relację jeden do wielu, znacznie lepiej będzie, jeśli użyjesz jednej tabeli i zezwolisz na identyfikator szkoły być puste w tabeli uczniów. Nie ma powodu, aby unikać wartości null w polach, nawet w przypadku klucza obcego. Oznacza to, że obcy związek jest opcjonalny: programiści i administratorzy baz danych doskonale to rozumieją, a bazowy silnik bazy danych z pewnością powinien działać dobrze.

Jeśli obawiasz się łączenia, nie martw się. Istnieje dobrze zdefiniowana semantyka opisująca, jak sprzężenia działają z polami o wartości null. Używając jednej tabeli, możesz połączyć dwie tabele zamiast trzech.

Komentarze

• Jeśli więc modeluję relację jeden do wielu (z opcjonalnym udziałem po stronie: 1), powinienem użyć klucza obcego, mimo że może mieć wartości NULL?
• @Tom tak, że dokładnie jak to wymodelować. Chociaż technicznie możliwe jest użycie tabeli łączenia, model danych pozwala na użycie wielu do wielu, więc będziesz potrzebować wyzwalaczy i logiki bazy danych, aby temu zapobiec. Lepiej będzie, jeśli ograniczysz relację w taki sposób, że nie będzie można dodać nieprawidłowych danych.
• Redagowałem moje pytanie.Utworzyłem tylko student_id jako klucz podstawowy w tabeli School_has_Student, co utrzymywało relację jeden do wielu. Jakie wady ma ta metoda w porównaniu z użyciem klucza obcego?
• @Tom Zmieniłem odpowiedź.

W powyższym komentarzu napisałeś:

książka „Fundamentals of Database Systems” […] mówi [.. .], że zaleca się użycie tabeli skrzyżowań, jeśli w kolumnie klucza obcego jest dużo wartości NULL (na przykład: jeśli 98% pracowników nie zarządza działem)

Jeśli w kolumnie klucza obcego jest dużo wartości NULL, programy będą musiały sobie poradzić z tą przeważnie pustą kolumną dla każdego przetwarzanego rekordu. Kolumna prawdopodobnie zajmie trochę miejsca na dysku mimo że w 98% wszystkich przypadków jest puste, zapytanie o relację oznacza wysłanie zapytania do tej kolumny, co daje większy ruch w sieci, a jeśli używasz ORM, który generuje klasy z twoich tabel, twoje programy będą również potrzebować więcej miejsca na kliencie strony niż to konieczne. Korzystanie z inters Tabela ection pozwala tego uniknąć, potrzebne będą tylko rekordy odsyłaczy, w których równoważny klucz obcy nie miałby wartości NULL.

W przeciwieństwie do tego, jeśli nie masz tylko kilku wartości NULL, powiedzmy 50% lub więcej relacje nie są NULL, użycie tabeli przecięć daje odwrotny efekt – więcej miejsca na dysku, większa złożoność skutkująca większym ruchem w sieci itp.

Tak więc używanie tabeli przecięć jest tylko formą optymalizacji, sensowną tylko dla szczególny przypadek, zwłaszcza w dzisiejszych czasach, gdy miejsce na dysku i pamięć stały się tańsze, znacznie rzadziej potrzebne. Zwróć uwagę, że „Podstawy systemów baz danych” zostały pierwotnie napisane ponad 20 lat temu (znalazłem odniesienie do drugiego wydania z 1994 roku) i wydaje mi się, że zalecenie to było już w tamtym czasie. Przed 1994 rokiem optymalizacja przestrzeni była prawdopodobnie znacznie ważniejsza niż obecnie, ponieważ pamięć masowa była nadal droższa, a komputery i sieci były znacznie wolniejsze niż obecnie.

Na marginesie wybrednego komentarza: Powyższe stwierdzenie jest tylko próbą przewidzenia tego, co autor książki „Podstawy systemów baz danych” miał na myśli w swojej rekomendacji, myślę, że robił przybliżone, ogólne stwierdzenie, obowiązujące dla większości systemów. W niektórych bazach danych istnieją inne możliwe optymalizacje, takie jak „rzadkie kolumny”, które sprawiają, że tabela przecięć jest jeszcze bardziej przestarzała.

Więc nie zrozum źle tego zalecenia. Książka nie mówi wolisz tabele przecięć dla {0,1}:n relacji w ogóle lub – jak napisałeś – że jest to „właściwy sposób”. Użyj optymalizacji tego typu, które skomplikują programy tylko wtedy, gdy naprawdę ich potrzebujesz.

Komentarze

• ' ponownie zakładasz wiele na temat implementacji baza danych, zwłaszcza biorąc pod uwagę, że OP nie ' nie wspomniał o konkretnej. Jest ' bardziej niż prawdopodobne, że baza danych jest wystarczająco inteligentna, aby używać tylko niewielka ilość miejsca na rzadkie kolumny.
• @gardenhead: co sprawia, że sądzisz, że jest to ” bardziej niż prawdopodobne „?
• Fakt, że bazy danych mają istnieją od dziesięcioleci i są wysoce zoptymalizowane, ponieważ są krytycznym elementem większości infrastruktur.
• @gardenhead: wydaje mi się, że przyjmujesz znacznie cięższe nieuzasadnione założenia niż ja. Niemniej jednak, zobacz moją edycję.

Model koncepcyjny będzie wyglądał następująco, czyli bardzo nietypowe mówiąc mniej:

Model fizyczny będzie wyglądał następująco, co jest mylące powiedzieć mniej (ludzie pomyślą, że to„ M: M, chyba że zobaczą z bliska):

Moja sugestia:

Jeśli masz podobne, wiele kolumn (FK lub innych), 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, dzieje się tak, ponieważ kolumny nie mają zastosowania do większości wierszy.

W przeciwnym razie , dopuszczające wartość null FK są normalną częścią bazy danych, a tabele złączeń są zwykle przeznaczone dla M: M rels.

Typowe zastosowania rels 1: 1 to dla tabel ról zawierających kolumny, które mają zastosowanie tylko wtedy, gdy jednostka jest określonego typu, oraz wyodrębnianie kolumn BLOB ze względu na wydajność lub pamięć. Avoding wartości null w SK nie jest do tego powszechnym zastosowaniem.

Oprócz innych odpowiedzi chciałbym zwrócić uwagę, że wartość zerowa dla klucza obcego jest niejednoznaczna. Czy to oznacza:

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

2) Jest wiadomo, czy uczeń ma szkołę, a nie ma żadnej.

Jeśli używasz standardowego znaczenia wartości null, w jaki sposób przedstawisz wyrażenie „uczeń nie ma szkoły” w modelu klucza obcego. W takim przypadku prawdopodobnie musiałbyś utworzyć wpis „brak szkoły” z własnym identyfikatorem w szkolnej tabeli. (Nieidealne)

Komentarze

• W książce ” Podstawy systemów baz danych ” wspomniano, że istnieją 3 interpretacje wyrażenia , może oznaczać: 1) nieznaną wartość. 2) niedostępną lub ukrytą wartość. 3) nie dotyczy atrybutu (myślę, że ta interpretacja oznacza, że można określić NULL dla klucza obcego).
• To ' jest użyteczną listą, ale semantyka wartości null (lub jakiejkolwiek innej wartości) jest definiowana przez użytkownika.może oznaczać cokolwiek projektant mówi, że to znaczy, nie ograniczając się do tej listy. Problem polega na tym, jak rozróżnić różne znaczenia, gdy więcej niż jeden może być wymagany (lub nawet zapisany nieumyślnie)
• Czy sugerujesz więc, że powinienem utworzyć tabelę skrzyżowań zamiast używać klucza obcego dopuszczającego wartość null?
• @Tom Tak, uważam, że w tym przypadku jest lepiej
• @BradThomas – aby uniknąć tej samej dwuznaczności podczas korzystania z tabeli skrzyżowań, czy przedstawiłbyś przypadek 2 (wiadomo, że uczeń brak szkoły) przez rekord w tabeli skrzyżowań z NULL School_ID?

Tabele bazy danych mają to fajna rzecz zwana ograniczeniami. Tak więc bardzo łatwo jest stworzyć tabelę przecięć, która pozwala tylko 1 uczniowi na pojawienie się w tabeli, ale wiele szkół w tej tabeli. Skutecznie daje ci

Teoria jest fajna, ale w końcu jesteś zamierzasz modelować bazę danych na podstawie pytań, które zadajesz.

Jeśli chcesz często zadawać pytania: „którzy uczniowie są w mojej szkole”, czy naprawdę chcesz sprawdzić całą tabelę uczniów, czy też masz łatwa tabela przecięć.

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

Tam to 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 tabeli students i w pewnym momencie zdecydujesz się wymusić unikalność rekordów za pomocą indeksu złożonego w kilku kolumnach. Jest bardzo prawdopodobne, że „Muszę również uwzględnić kolumnę school_id, a tutaj sprawy zaczną się komplikować. Ze względu na sposób zaprojektowania SQL wstawiono kilka identycznych rekordów, gdzie school_id jest NULL będzie możliwe. Ma to sens z technicznego punktu widzenia, ale jest sprzeczne z intuicją i może prowadzić do nieoczekiwanych rezultatów. Z drugiej strony wymuszanie wyjątkowości na tabela przecięć jest łatwa.

Niedawno musiałem modelować taką „opcjonalną” relację, w której wymóg ograniczenia unikalności wynikał z kolumny z datownikiem. Pozostawienie w tabeli klucza obcego dopuszczającego wartość zerową nagle prowadzi do możliwość wstawiania rekordów z tym samym znacznikiem czasu (załóżmy, że jest to domyślny, ustawiony na rekordach, które nie były audytowane / zatwierdzone ukochane) – a jedynym wyjściem było usunięcie kolumny dopuszczającej wartość null.

Więc jak widać, jest to dość szczególny przypadek i jak zauważyli inni, w większości przypadków wszystko byłoby w porządku wartości NULL. To naprawdę zależy od konkretnych wymagań twojego modelu.

Oprócz wielu dobrych sugestii już przesłanych, osobiście ja „Nie jestem fanem kluczy obcych, chyba że są one naprawdę potrzebne. Po pierwsze, istnieje relacja M: M, do której się odwołujesz. Ponadto wywołanie klucza obcego, a tym samym wciągnięcie danych tabeli do zapytań, wprowadza większą złożoność i zależy od rozmiar tabeli, wolniejsza wydajność. Jak powiedzieli inni, pola FK dopuszczające wartość null mogą być nieobsługiwane i mogą powodować problemy z integralnością danych.

Jeśli definiujesz stan, w którym szkoła ucznia jest nieznana lub pusta, wartość NULL nie będzie różnicować tych warunków. (znowu wracamy do integralności danych). Sugestia tabeli ról autorstwa Tulains jest elegancka i pozwala na czyste wartości zerowe.