Czy kolumna Unikalny identyfikator jest potrzebna w tabeli wiele do wielu (skrzyżowanie)?

Pierwsze kilka projektów zaczęło się od EF, ale miałem kilka pytań na temat łączenia tabel i kluczy itp. Powiedzmy, że mam tabelę aplikacji i tabelę uprawnień. Aplikacje mają wiele uprawnień, a każde uprawnienie może należeć do wielu aplikacji (wiele do wielu).

Teraz tabele aplikacji i uprawnień są proste:

Applications
--------------
PK  ApplicationID
    Name

Permissions
--------------
PK  PermissionID
    Name

Ale jaki jest NAJLEPSZY sposób na zrobienie stolika? Mam te dwie opcje:

ApplicationPermissions
-----------------------
PK  ApplicationPermissionID
CU  ApplicationID
CU  PermissionID

LUB

ApplicationPermissions
-----------------------
CPK ApplicationID
CPK PermissionID

PK = Primary Key
CPK = Composite Primary Key
CU = Composite Unique Index

Czy kiedykolwiek zostałeś spalony, robiąc to w jedną stronę? czy jest to ściśle preferencja? Przyszło mi do głowy, że wiele „różnic” zostanie wyabstrahowanych przez mój wzorzec repozytorium (na przykład prawie nigdy nie stworzyłbym całego obiektu uprawnień i nie dodałbym go do aplikacji, ale robię to według identyfikatora lub unikalnej nazwy lub coś), ale chyba szukam horrorów, tak czy inaczej.

Uważam, że masz na myśli tabelę „skrzyżowanie”, a nie tabelę „dołącz”.

Tabela połączeń nie musi mieć własnego pola identyfikatora. Nigdy nie będziesz musiał dołączać do takiego identyfikatora ani filtrować go. Dołączysz lub przefiltrujesz tylko identyfikatory mapowanych map. Identyfikator w tabeli połączeń to marnowanie miejsca na dysku.

Zatem najlepszą opcją jest unikanie identyfikatora. Zazwyczaj tabela połączeń będzie miała 2 indeksy obejmujące. Każdy indeks pokrywający używa jednego z mapowanych identyfikatorów jako podstawowego pola sortowania.

Ale „najlepsze” nie jest dalekie. Nadmiarowe pole identyfikatora to bardzo drobny problem. Nie będziesz mieć żadnych horrorów na niewielkiej ilości zmarnowanego dysku. Identyfikator nie „wykradnie” indeksu klastrowego, ponieważ i tak nie chcesz grupować w zmapowanej kombinacji.

Jeśli Twoja platforma chce, aby wszystkie tabele miały identyfikator, wybierz go. Jeśli standardy bazy danych Twojego zespołu dyktują, że wszystkie tabele muszą mieć identyfikator, wybierz go. Jeśli nie, to unikaj go.

Z biegiem lat nabrałem zwyczaju nadawania każdej tabeli „TableName” automatycznie wygenerowanego klucza podstawowego „TableNameID”, bez żadnych wyjątków, nawet dla tabel połączeń. Mogę powiedzieć, że nigdy tego nie żałowałem, ponieważ ułatwia to wiele rzeczy podczas tworzenia ogólnego kodu, który robi coś dla „wszystkich tabel” lub „niektórych tabel”, lub „wielu wierszy kilku różnych tabel”.

Na przykład, jeśli ktoś poprosi cię o przechowanie niektórych wierszy różnych tabel (lub odniesień do nich) w pliku lub w pamięci, na przykład do celów logowania, bardzo przydatne jest, gdy wiesz wcześniej, że musisz tylko zapisać dokładnie jedną nazwa tabeli i dokładnie jeden identyfikator liczby całkowitej, i nie musisz zajmować się żadnymi „specjalnymi przypadkami”.

Inną rzeczą jest, że kiedy zaczynasz od połączonych PK, prawdopodobnie będziesz później musiał potrzebować połączonych kluczy obcych (ponieważ możesz dojść do punktu, w którym chcesz dodać numer referencyjny FK do swojego ApplicationPermissionsstołu). Następnie następnym wymogiem może być uniknięcie tego FK w połączeniu z innymi atrybutami lub kluczami obcymi - co spowoduje ogólną zwiększoną złożoność. Oczywiście nic nie jest w stanie poradzić sobie z większością nowoczesnych systemów DB, ale jednolite rozwiązanie znacznie ułatwia życie programistom.

I na koniec, zdanie podobne SELECT ... FROM TABLE WHERE TableNameID IN (id1,id2,...)działa dobrze z jedną kolumną jako kluczem podstawowym, ale do tej pory nigdy nie widziałem dialektu SQL, który pozwala to zrobić z kombinowanymi kluczami. Jeśli wiesz wcześniej, że nigdy nie będziesz potrzebować takiego zapytania, dobrze, ale nie zdziw się, jeśli jutro otrzymasz wymaganie, które zostanie rozwiązane najłatwiej za pomocą tego rodzaju SQL.

Oczywiście, jeśli oczekujesz, że twój ApplicationPermissionsstół pomieści kilkaset milionów wierszy, powinieneś rozważyć uniknięcie czegoś takiego ApplicationPermissionsID.

Chociaż odpowiedź Mike'a jest dobra, oto powody, dla których dodałbym osobne pole identyfikatora lub nie.

1. Rozważ użycie oddzielnego pola identyfikatora dla tabeli połączeń / łączenia, jeśli zawiera on pola inne niż identyfikator . Zazwyczaj zauważa się, że jest to jednostka pierwszej klasy.

2. Rozważ użycie oddzielnego pola identyfikatora, jeśli interfejsy API lub istniejąca logika mają tendencję do używania pojedynczych pól do pobierania / edycji jednostek. Pomoże to innym osobom śledzić Twój kod w kontekście większego projektu.

3. Nie używaj go, jeśli nie ma określonych korzyści (KISS). EF wie, jak radzić sobie z tego typu tabelą, a czasem można pominąć złożone unikalne ograniczenie, gdy inni próbują zrozumieć ten rodzaj relacji. Ponadto, podczas normalizacji staram się używać najmniejszego możliwego klucza, który jednoznacznie definiuje krotkę . W drugim przykładzie efektywnie masz 2 oddzielne klucze podstawowe kandydatów.

table Person
   Id int identity(1,1) not null primary key
   ...other fields go here...
table Address
   Id int identity(1,1) not null primary key
   ...other fields go here...
table PersonAddress
   Id int identity(1,1) not null primary key
   PersonId int not null
   AddressId int not null

Pamiętaj, aby utworzyć indeks i klucz obcy zarówno na, jak PersonIdi na AddressId.

Bez względu na to, co inni uważają za „lepsze” lub „powinieneś”, jest to najprostszy i najłatwiejszy sposób na prawidłowe działanie bazy danych.