Pytanie Jeden lub dwa klucze główne w tabeli "wiele do wielu"?


Mam w mojej bazie danych następujące tabele, które mają relację wiele do wielu, co jest wyrażane przez tabelę łączącą, która ma klucze obce dla kluczy podstawowych każdej z głównych tabel:

  • Widget: WidgetID (PK), tytuł, cena
  • Użytkownik: ID użytkownika (PK), Imię, Nazwisko

Załóżmy, że każda kombinacja Widget użytkownika jest unikalna. Widzę dwie opcje jak zorganizować tabelę łączącą, która definiuje relację danych:

  1. UserWidgets1: UserWidgetID (PK), WidgetID (FK), identyfikator użytkownika (FK)
  2. UserWidgets2: WidgetID (PK, FK), identyfikator użytkownika (PK, FK)

Opcja 1 ma pojedynczą kolumnę klucza podstawowego. Jednak wydaje się to zbędne, ponieważ jedynymi danymi przechowywanymi w tabeli są relacje między dwiema podstawowymi tabelami, a ta sama relacja może tworzyć unikalny klucz. W ten sposób prowadząc do opcji 2, która ma dwukolumnowy klucz podstawowy, ale traci unikalny identyfikator jednej kolumny, który ma opcję 1. Mogłem również opcjonalnie dodać do pierwszej tabeli unikalny indeks dwukolumnowy (WidgetID, UserID).

Czy istnieje jakaś realna różnica między tymi dwoma względami wydajnościowymi lub z jakiegokolwiek powodu, aby preferować jedno podejście do drugiego w celu ukształtowania tabeli User-widget dla wielu-do-wielu?


14
2017-09-02 05:21


pochodzenie


Potrzebne indeksy są określone przez wymagania dotyczące zapytań, a nie przez projekt schematu. - dkretz


Odpowiedzi:


W obu przypadkach masz tylko jeden klucz podstawowy. Drugi to tak zwany klucz złożony. Nie ma żadnego powodu, dla którego warto wprowadzić nową kolumnę. W praktyce będziesz musiał zachować unikalny indeks wszystkich kluczy kandydatów. Dodanie nowej kolumny nie kosztuje nic innego, jak tylko koszty utrzymania.

Idź z opcją 2.


24
2017-09-02 05:28



Podstawowym kluczem może być compund - warunki nie są wyłączne. - paulmurray
@paulmurray: Wierzę, że powyższa odpowiedź mówi, że w obu przypadkach masz klucz podstawowy, w tym przypadek, w którym masz klucz złożony. Czy masz coś do dodania? - Apocalisp


Osobiście ja by mieć klucz syntetyczny / klucz zastępczy w tabelach wiele do wielu z następujących powodów:

  • Jeśli używasz numerycznych kluczy syntetycznych w tabelach encji, to samo ich zachowanie w tabelach relacji zachowuje spójność w konwencji projektowania i nazewnictwa.
  • Może się tak zdarzyć w przyszłości, że sama tablica "wiele do wielu" stanie się jednostką nadrzędną wobec podporządkowanej jednostki, która potrzebuje unikalnego odniesienia do pojedynczego wiersza.
  • To naprawdę nie będzie używać tak dużo dodatkowej przestrzeni dyskowej.

Klucz syntetyczny nie zastępuje klucza naturalnego / złożonego ani nie staje się kluczem PRIMARY KEY dla tego stołu tylko dlatego, że jest to pierwsza kolumna w tabeli, więc częściowo zgadzam się z artykułem Josha Berkusa. Jednak nie zgadzam się, że naturalne klucze są zawsze dobrymi kandydatami PRIMARY KEY's i na pewno nie powinno się ich używać, jeśli mają być używane jako klucze obce w innych tabelach.


5
2017-09-02 10:01



Zdaję sobie sprawę, że odpowiedź została udzielona dawno temu, ale czy klucz złożony nadal nie byłby unikalnym odnośnikiem do pojedynczego wiersza dla tabeli nadrzędnej (twój punkt 2)? - crush
@crush - tak, byłby unikatowy, ale utworzenie ograniczenia na złożonym kluczu jest fugly / inconsistent na platformach. Wolę być jednoznaczny i konsekwentny. Każda tabela ma kolumnę tożsamości. - Guy


Opcja 2 używa prostego klucza Compund, opcja 1 używa a Klucz zastępczy. Opcja 2 jest preferowana w większości scenariuszy i jest zbliżona do modelu lojalnościowego, ponieważ jest to dobry klucz kandydujący.

Są sytuacje, w których możesz chcieć użyć zastępczego klucza (opcja 1)

  1. Nie jest tak, że klucz złożony jest z czasem dobrym kluczem kandydującym. Szczególnie w przypadku danych czasowych (danych zmieniających się w czasie). A co jeśli chcesz dodać kolejny wiersz do tabeli UserWidget z tym samym UserId i WidgetId? Pomyśl o zatrudnieniu (EmployeeId, EmployeeId) - działałoby w większości przypadków, chyba że ktoś wróciłby do pracy dla tego samego pracodawcy w późniejszym terminie
  2. Jeśli tworzysz komunikaty / transakcje biznesowe lub coś podobnego, które wymaga łatwiejszego klucza do integracji. Może replikacja?
  3. Jeśli chcesz utworzyć własne mechanizmy audytu (lub podobne) i nie chcesz, aby klucze były zbyt długie.

Z reguły podczas modelowania danych można zauważyć, że większość jednostek asocjacyjnych (od wielu do wielu) jest wynikiem zdarzenia. Osoba podejmuje pracę, przedmiot jest dodawany do koszyka itp. Większość wydarzeń ma czasową zależność od wydarzenia, w którym data i godzina są istotne - w takim przypadku klucz zastępczy może być najlepszą alternatywą.

Tak więc weź opcję 2, ale upewnij się, że masz pełny model.


5
2017-09-02 11:49





Zgadzam się z poprzednimi odpowiedziami, ale mam jedną uwagę do dodania. Jeśli chcesz dodać więcej informacji do relacji i zezwolić na więcej relacji między tymi samymi dwoma obiektami, potrzebujesz opcji pierwszej.

Na przykład, jeśli chcesz śledzić wszystkie czasy, w których użytkownik 1 używał widżetu 664 w tabeli userwidget, identyfikator użytkownika i widgetid nie jest już unikalny.


3
2017-09-02 08:17





Jaka jest korzyść z klucza podstawowego w tym scenariuszu? Rozważ opcję braku klucza podstawowego:   UserWidgets3: WidgetID (FK), identyfikator użytkownika (FK)

Jeśli chcesz unikatowości, użyj klucza złożonego (UserWidgets2) lub ograniczenia unikalności.

Zwykle zaletą posiadania klucza podstawowego jest to, że często przesyła on zapytanie do tabeli za pomocą klucza podstawowego, który jest szybki. W przypadku tabel wielu do wielu zwykle nie następuje zapytanie o klucz podstawowy, więc nie ma korzyści z wydajności. Tabele wielu do wielu są sprawdzane przez ich klucze obce, więc powinieneś rozważyć dodanie indeksów na WidgetID i UserID.


2
2017-09-02 05:33





Opcja 2 jest poprawną odpowiedzią, chyba że masz naprawdę dobry powód, aby dodać zastępczy klucz numeryczny (który zrobiłeś w opcji 1).

Surogatyczne kolumny kluczy numerycznych nie są "kluczami podstawowymi". Klucze podstawowe są technicznie jedną z kombinacji kolumn, które jednoznacznie identyfikują rekord w tabeli.

Każdy, kto tworzy bazę danych, powinien przeczytać ten artykuł http://it.toolbox.com/blogs/database-soup/primary-keyvil-part-i- 7327 autorstwa Josha Berkusa, aby zrozumieć różnicę między odpowiednimi kolumnami liczbowymi a kluczami podstawowymi.

Z mojego doświadczenia wynika, że ​​jedynym prawdziwym powodem dodania zastępczego klucza numerycznego do tabeli jest to, że klucz podstawowy jest kluczem złożonym i musi być użyty jako odniesienie do klucza obcego w innej tabeli. Tylko wtedy powinieneś pomyśleć o dodaniu dodatkowej kolumny do stołu.

Ilekroć widzę strukturę bazy danych, w której każda tabela ma kolumnę "id", istnieje duże prawdopodobieństwo, że została zaprojektowana przez kogoś, kto nie docenia modelu relacyjnego i niezmiennie wyświetla jeden lub więcej problemów zidentyfikowanych w artykule Josh'a.


2
2017-09-02 06:12





Poszedłbym z oboma.

Wysłuchaj mnie:

Klucz złożony jest oczywiście ładnym i poprawnym sposobem, o ile odzwierciedla to znaczenie twoich danych. Bez pytania.

Jednakże: miałem problemy z hibernowaniem, które działają poprawnie, chyba że użyjesz jednego wygenerowanego klucza podstawowego - klucz zastępczy.

Więc użyłbym danych logicznych i fizycznych Model. Logiczna ma klucz złożony. Model fizyczny - który implementuje model logiczny - ma klucz zastępczy i klucze obce.


1
2018-03-22 02:40





Ponieważ każda kombinacja Widżet użytkownika jest unikalna, powinieneś ją reprezentować w tabeli, czyniąc kombinację unikalną. Innymi słowy, przejdź do opcji 2. W przeciwnym razie możesz mieć dwa wpisy o tym samym widżecie i identyfikatorach użytkowników, ale inne identyfikatory widżetów użytkowników.


0
2017-09-02 05:25





Identyfikator użytkownika w pierwszej tabeli nie jest potrzebny, tak jak powiedziałeś, wyjątkowość pochodzi z kombinacji widgetid i userid.

Chciałbym użyć drugiej tabeli, zachować klucze foriegn i dodać unikalny indeks na widgetid i userid.

Więc:

userwidgets (widgetid (fk), userid (fk),
             unique_index (widgetid, userid)
)

Występuje pewien wzrost wydajności bez posiadania dodatkowego klucza podstawowego, ponieważ baza danych nie musiałaby obliczać indeksu dla klucza. W powyższym modelu chociaż ten indeks (za pomocą unique_index) jest nadal obliczany, ale uważam, że jest to łatwiejsze do zrozumienia.


0
2017-09-02 05:29