Jakie są najlepsze praktyki dotyczące tabel odnośników w relacyjnych bazach danych?

Tabele odnośników (lub tabele kodów , jak nazywają je niektóre osoby) są zwykle zbiorem możliwych wartości, które można podać dla określonej kolumny.

Załóżmy na przykład, że mamy tablicę przeglądową o nazwie party(przeznaczoną do przechowywania informacji o partiach politycznych), która ma dwie kolumny:

party_code_idn, który przechowuje generowane przez system wartości liczbowe i (pozbawiony znaczenia domeny biznesowej ) działa jako surogat dla prawdziwego klucza.
party_code, jest prawdziwym lub „naturalnym” kluczem tabeli, ponieważ utrzymuje wartości mające konotacje z domeną biznesową .

Powiedzmy, że taka tabela zachowuje następujące dane:

 +----------------+------------+
 | party_code_idn | party_code |
 +----------------+------------+
 |              1 | Republican |
 |              2 | Democratic |
 +----------------+------------+

party_codeKolumna, która utrzymuje „republikański” i „demokratyczny”, będąc prawdziwym kluczem stołu, jest skonfigurowana z ograniczeniem wyjątkowy, ale opcjonalnie dodany wartości party_code_idni określił go jako PK tabeli (choć logicznie rzecz biorąc , party_codemoże działać jako KLUCZ PODSTAWOWY [PK]).

Pytanie

Jakie są najlepsze praktyki wskazywania wartości wyszukiwania z tabel transakcji ? Czy powinienem ustanowić odniesienia do klucza OBCEGO (FK) albo (a) bezpośrednio do wartości naturalnej i znaczącej, albo (b) w celu zastąpienia wartości?

Opcja (a) , na przykład

 +---------------+------------+---------+
 | candidate_idn | party_code |  city   |
 +---------------+------------+---------+
 |             1 | Democratic | Alaska  |
 |             2 | Republican | Memphis |
 +---------------+------------+---------+

ma następujące właściwości ¹ :

Czytelny dla użytkownika końcowego (+)
Łatwy do importu-eksportu między systemami (+)
Trudno zmienić wartość, ponieważ wymaga modyfikacji we wszystkich tabelach odsyłających (-)
Dodanie nowej wartości nie jest kosztowne (=)

Myślę, że jest prawie jak „ przekazywanie przez wartość ”, aby wyciągnąć analogię z wywołania funkcji w żargonie programowania aplikacji.

Opcja (b) , na przykład

 +---------------+----------------+---------+
 | candidate_idn | party_code_idn |  city   |
 +---------------+----------------+---------+
 |             1 |              1 | Alaska  |
 |             2 |              2 | Memphis |
 +---------------+----------------+---------+

ma właściwości poniżej:

Nieczytelne dla użytkownika końcowego (-)
Trudne do importu-eksportu ponieważ musimy go usunąć z odniesienia (-)
Łatwa do zmiany wartości, ponieważ przechowujemy tylko referencje w tabelach transakcji (+)
Dodanie nowej wartości nie jest kosztowne (=)

Jest bardzo podobny do „ pass by reference ”, jeśli porównać do wywołania funkcji w języku programowania aplikacji.

Import-eksport można również wykonać w inny sposób, np. Po prostu ponownie wypełniając tabelę przeglądową, a następnie ponownie wypełniając kolumnę zastępczą. Mam nadzieję, że dobrze to rozumiem. Właśnie to usłyszałem jako możliwość.

_{1. Należy pamiętać, że +, -i =wskazują na korzyści z tych właściwości.}

Pytanie

Co ważne: czy istnieje różnica między wyszukiwaniem (lub kodem) tabelą ) a referencją FK, jeśli zamierzamy zastosować to drugie podejście? Myślę, że działają tak samo.

Powiązane zasoby

— Nishant
źródło

Odpowiedzi:

Przez IDNbiorę to znaczy takie IDENTITY, SEQUENCEczy AUTO_INCREMENTpole? Powinieneś zajrzeć tu i tutaj .

Uwaga, sekcja 5 (Niewłaściwe wykorzystanie wartości danych jako elementów danych) pierwszego odniesienia, pod rysunkiem 10

Oczywiście możesz mieć osobną tabelę dla sprzedawców, a następnie odwoływać się do niej za pomocą klucza obcego, najlepiej z prostym kluczem zastępczym, takim jak Sales_person_id, pokazanym powyżej.

Zatem ten ekspert uważa, że powinieneś „uszanować” klucze zastępcze. To naprawdę podstawowa technika SQL i nie powinna powodować problemów w codziennym SQL. Wygląda na to, że na rysunku 10 wystąpił błąd - osoba_sprzedaży w SalesData powinna być kluczem zastępczym (tj. Liczbą), a nie tekstem. Wnioskuję to z powyższego cytatu.

Za wszelką cenę należy unikać pokusy (bardzo powszechnej dla początkujących programistów baz danych), aby popełnić błąd opisany w sekcji (1) Typowe tabele przeglądowe. Jest to powszechnie nazywane podejściem MUCK ( Massively Unified Code Key ) (nieprzypadkowo :-), w szczególności Joe Celko , również sarkastycznie znany jako OTLT - One True Lookup Table ) i prowadzi do różnego rodzaju trudności. Wydaje się, że nowi programiści uważają, że pojedynczy kod / tablica / cokolwiek jest „czystszy” i będzie bardziej wydajny, gdy nic nie będzie dalej od prawdy.

Z drugiego odniesienia powyżej:

Normalizacja eliminuje zbędne dane, dzięki czemu zadanie egzekwowania integralności danych jest znacznie prostsze, ale proces tworzenia MUCK jest czymś zupełnie innym. MUCK nie eliminuje zbędnych danych, a raczej eliminuje to, co postrzegane jest jako zbędne tabele, ale jak wykażę, mniej tabel nie oznacza prostoty.

Możesz także rzucić okiem na powiązany paradygmat EAV ( Entity Attribute Value ), z którym mam tutaj do czynienia .

— Vérace
źródło

Przez IDN miałem na myśli automatycznie wygenerowany klucz obcy. Nie używam wspólnych tabel przeglądowych, nie jestem pewien, jak myślałeś, że tego użyłem? Używamy właściwie setek tabel kodów. Wydaje się naprawdę dziwne, że ktoś zrobiłby to w zunifikowanym stole. Ale dobrze wiedzieć, że taki wzór istnieje i należy go unikać. EAV wydaje się interesujące. Więc zgoda jest taka, że powinienem wyłapać IDN, tzn. Klucz zastępczy?

— Nishant

Strategia „dereferencji” z pewnością wydaje się być podejściem większościowym. Dlaczego nie trochę poeksperymentować i zobaczyć, jak sobie radzisz? Wybierz naturalne klucze i zobacz, jak działa Twój SQL - następnie określ surogat i przez chwilę z tym popsuć. Celko i Pascal byliby szanowani w świecie SQL / Relational, ale widziałem ludzi, którzy kłócą się z nimi, twierdząc, że ich podejście jest zbyt doktrynalne i purystyczne - i że systemy „rzeczywiste” muszą używać kluczy zastępczych. Jeśli twój naturalny klucz to trzy pola, a to kolejne FOREIGN KEYw innej tabeli, może być dość niechlujny, ale YMMV.

— Vérace

Tak, myślałem o purystycznym myśleniu i pomyślałem, dlaczego ppl używa kluczy zastępczych! A potem niektóre przypadki użycia wydawały się naprawdę trudne w purystycznym świecie. Czułem, że podejście zastępcze jest łatwiejsze, chociaż masz pewne wady importowania i eksportowania. Rzeczywiście scenariusz kombinacji może być trudniejszy. Tabele kodów Btw niewiele różnią się od klucza obcego w scenariuszu zastępczym, prawda? Rozumiem, że logiczne rozróżnienie istnieje, ale jest to tylko klucz obcy.

— Nishant

Możesz wymusić swoje naturalne klucze za pomocą UNIQUE CONSTRAINTs i NOT NULLs - cóż, wpisy w tabeli kodów znajdują się FOREIGN KEYw tabelach, które ich używają / odnoszą się do nich - więc pojęcia są powiązane, ale nie takie same. Klucz zastępczy tabeli kodów to pole pojawiające się w tabeli „potomnej” - z pewnością mniej czytelne, ale INTniezbyt duże - nie wymaga dużo miejsca, co jest zaletą kluczy zastępczych.

— Vérace,

Istnieje trzecie podejście, które ma niektóre zalety dwóch opcji - umieść rzeczywisty kod w tabeli kodów. Rozumiem przez to krótką sekwencję znaków, która oddaje esencję pełnej wartości i jest wyjątkowa. W twoim przykładzie może to być

Idn: 1
Name: Democrats
Code: D      (or DEM)

Kod jest przenoszony do tabel transakcyjnych jako klucz obcy. Jest krótki, zrozumiały i nieco niezależny od „prawdziwych” danych. Przyrostowe zmiany nazwy nie sugerują zmiany kodu. Should Republikanie DeCamp en masse , jednak zmiana kodu może być konieczne, z towarzyszącymi jej problemami, że id zastępczym nie poniosą.

Ten styl został nazwany skrótem kodującym. Mogę polecić pismo Celko na ten temat. Książki Google zawierają kilka przykładów. Wyszukaj „Kodowanie Celko”.

Inne przykłady: kodowanie 2- lub 3-literowe dla krajów, kodowanie 3-literowe (GBP, USD, EUR) dla kodów walut. Krótkie, samo wyjaśniające się i niezmienne (a dla nich istnieje ISO).

— Michael Green
źródło