Znowu zaczynamy, stary argument wciąż się pojawia ...

Czy lepiej byłoby mieć klucz biznesowy jako klucz podstawowy, czy też wolelibyśmy mieć identyfikator zastępczy (tj. Tożsamość SQL Server) z unikalnym ograniczeniem w polu klucza biznesowego?

Proszę podać przykłady lub dowody na poparcie swojej teorii.

Obie. Zjedz ciasto i zjedz je.

Pamiętaj, że nie ma nic specjalnego w kluczu podstawowym, poza tym, że jest oznaczony jako taki. To nic innego jak ograniczenie NOT NULL UNIQUE, a tabela może mieć więcej niż jedno.

Jeśli używasz klucza zastępczego, nadal potrzebujesz klucza biznesowego, aby zapewnić unikalność zgodnie z regułami biznesowymi.

Tylko kilka powodów, dla których warto używać kluczy zastępczych:

1. Stabilność : zmiana klucza ze względu na potrzebę biznesową lub naturalną wpłynie negatywnie na powiązane tabele. Klucze zastępcze rzadko, jeśli w ogóle, wymagają zmiany, ponieważ nie ma znaczenia związanego z wartością.

2. Konwencja : pozwala ci mieć znormalizowaną konwencję nazewnictwa kolumn klucza podstawowego, zamiast myśleć o tym, jak łączyć tabele z różnymi nazwami ich PK.

3. Szybkość : w zależności od wartości PK i typu, zastępczy klucz liczby całkowitej może być mniejszy, szybszy do indeksowania i wyszukiwania.

Wygląda na to, że nikt jeszcze nie powiedział nic na poparcie nie-zastępczych (waham się powiedzieć „naturalne”) kluczy. Więc oto idzie ...

Wadą kluczy zastępczych jest to, że są one bez znaczenia (cytowany jako zaletę niektóre, ale ...). To czasami zmusza Cię do dołączenia do zapytania znacznie większej liczby tabel, niż powinno być naprawdę konieczne. Porównać:

select sum(t.hours)
from timesheets t
where t.dept_code = 'HR'
and t.status = 'VALID'
and t.project_code = 'MYPROJECT'
and t.task = 'BUILD';

przeciwko:

select sum(t.hours)
from timesheets t
     join departents d on d.dept_id = t.dept_id
     join timesheet_statuses s on s.status_id = t.status_id
     join projects p on p.project_id = t.project_id
     join tasks k on k.task_id = t.task_id
where d.dept_code = 'HR'
and s.status = 'VALID'
and p.project_code = 'MYPROJECT'
and k.task_code = 'BUILD';

Chyba że ktoś poważnie uważa, że ​​to dobry pomysł ?:

select sum(t.hours)
from timesheets t
where t.dept_id = 34394
and t.status_id = 89    
and t.project_id = 1253
and t.task_id = 77;

„Ale” ktoś powie, „co się stanie, gdy zmieni się kod MYPROJECT, VALID lub HR?” Do którego moja odpowiedź będzie: „dlaczego chcesz trzeba to zmienić?” Nie są to „naturalne” klucze w tym sensie, że jakiś zewnętrzny organ będzie stanowił prawo, zgodnie z którym „WAŻNY” powinien być ponownie zakodowany jako „DOBRY”. Tylko niewielki procent „naturalnych” kluczy naprawdę mieści się w tej kategorii - typowe przykłady to SSN i ​​kod pocztowy. Zdecydowanie użyłbym bezsensownego klucza numerycznego do tabel takich jak Osoba, Adres - ale nie do wszystkiego , co z jakiegoś powodu większość ludzi wydaje się popierać.

Zobacz także: moja odpowiedź na inne pytanie

Klucz zastępczy NIGDY nie będzie miał powodu do zmiany. Nie mogę powiedzieć tego samego o klawiszach naturalnych. Nazwiska, e-maile, numery ISBN - to wszystko może się kiedyś zmienić.

Klucze zastępcze (zwykle liczby całkowite) mają tę wartość dodaną, że sprawiają, że relacje między tabelami są szybsze i bardziej ekonomiczne w przechowywaniu i aktualizowaniu (jeszcze lepiej, klucze obce nie muszą być aktualizowane podczas korzystania z kluczy zastępczych, w przeciwieństwie do pól kluczy biznesowych, to się zmienia od czasu do czasu).

Klucz podstawowy tabeli powinien być używany do jednoznacznej identyfikacji wiersza, głównie do celów łączenia. Pomyśl o tabeli Osoby: imiona mogą się zmieniać i nie gwarantuje się ich niepowtarzalności.

Think Companies: jesteś szczęśliwą firmą Merkin, prowadzącą interesy z innymi firmami w Merkia. Jesteś wystarczająco sprytny, aby nie używać nazwy firmy jako klucza głównego, więc używasz unikalnego identyfikatora firmy Merkia, składającego się w całości z 10 znaków alfanumerycznych. Następnie Merkia zmienia identyfikatory firm, ponieważ uznała, że ​​to dobry pomysł. W porządku, używasz funkcji kaskadowych aktualizacji silnika db, aby zmienić, który nie powinien Cię w ogóle angażować. Później Twoja firma się rozwija, a teraz pracujesz w firmie we Freedonii. Freedoński identyfikator firmy może mieć maksymalnie 16 znaków. Musisz powiększyć klucz podstawowy identyfikatora firmy (także pola kluczy obcych w zamówieniach, problemach, przelewach itp.), Dodając pole Kraj w kluczu podstawowym (również w kluczach obcych). Auć! Wojna domowa we Freedonii, to są podzielone na trzy kraje. Nazwę kraju Twojego współpracownika należy zmienić na nową; kaskadowe aktualizacje na ratunek. BTW, jaki jest twój klucz podstawowy? (Kraj, CompanyID) czy (CompanyID, Country)? Ta ostatnia pomaga w dołączaniu, ta pierwsza pozwala uniknąć innego indeksu (a może wielu, jeśli chcesz, aby Twoje zamówienia były również pogrupowane według krajów).

Wszystko to nie jest dowodem, ale wskazaniem, że klucz zastępczy do jednoznacznej identyfikacji wiersza dla wszystkich zastosowań, w tym operacji łączenia, jest lepszy niż klucz biznesowy.

Generalnie nienawidzę kluczy zastępczych. Powinny być używane tylko wtedy, gdy nie ma dostępnego naturalnego klucza wysokiej jakości. Myśląc o tym, wydaje się raczej absurdalne, że dodanie bezsensownych danych do tabeli może polepszyć sytuację.

Oto moje powody:

1. Podczas korzystania z kluczy naturalnych tabele są grupowane w taki sposób, w jaki są najczęściej przeszukiwane, co przyspiesza zapytania.

2. Korzystając z kluczy zastępczych, należy dodać unikalne indeksy do kolumn kluczy logicznych. Nadal musisz zapobiegać logicznemu duplikowaniu danych. Na przykład nie możesz zezwolić dwóm organizacjom o tej samej nazwie w tabeli Organizacja, mimo że pk jest kolumną zastępczego identyfikatora.

3. Gdy klucze zastępcze są używane jako klucz podstawowy, znacznie mniej jasne jest, jakie są naturalne klucze podstawowe. Podczas programowania chcesz wiedzieć, jaki zestaw kolumn sprawia, że ​​tabela jest wyjątkowa.

4. W jednym do wielu łańcuchów relacji logiczne łańcuchy kluczy. Na przykład organizacje mają wiele kont, a konta mają wiele faktur. Zatem klucz logiczny organizacji to nazwa organizacji. Klucz logiczny kont to nazwa organizacji, identyfikator konta. Klucz logiczny faktury to nazwa organizacji, identyfikator konta, numer faktury.

   Kiedy używane są klucze zastępcze, łańcuchy kluczy są obcinane tylko przez posiadanie klucza obcego tylko dla bezpośredniego nadrzędnego. Na przykład tabela faktur nie ma kolumny OrgName. Ma tylko kolumnę dla AccountID. Jeśli chcesz wyszukiwać faktury dla danej organizacji, będziesz musiał dołączyć do tabel Organizacja, Konto i Faktury. Jeśli używasz kluczy logicznych, możesz bezpośrednio wysłać zapytanie do tabeli Organizacja.

5. Przechowywanie zastępczych wartości kluczy tabel przeglądowych powoduje, że tabele są wypełniane bezsensownymi liczbami całkowitymi. Aby wyświetlić dane, należy utworzyć złożone widoki, które łączą się ze wszystkimi tabelami przeglądowymi. Tabela przeglądowa jest przeznaczona do przechowywania zestawu dopuszczalnych wartości dla kolumny. Nie należy go kodyfikować przez przechowywanie zamiast tego klucza zastępczego w postaci liczby całkowitej. W zasadach normalizacji nie ma nic, co sugerowałoby, że zamiast samej wartości należy przechowywać zastępczą liczbę całkowitą.

6. Mam trzy różne książki z bazą danych. Żaden z nich nie używa kluczy zastępczych.

Chcę podzielić się z wami moim doświadczeniem w tej niekończącej się wojnie: D dotyczącej dylematu naturalnego i zastępczego. Myślę, że zarówno klucze zastępcze (sztuczne generowane automatycznie), jak i klucze naturalne (złożone z kolumn o znaczeniu domenowym) mają wady i zalety . Dlatego w zależności od sytuacji bardziej odpowiedni może być wybór jednej lub drugiej metody.

Ponieważ wydaje się, że wiele osób przedstawia klucze zastępcze jako rozwiązanie niemal doskonałe, a klucze naturalne jako plagę, skupię się na argumentach z innego punktu widzenia:

Wady kluczy zastępczych

Klucze zastępcze to:

1. Źródło problemów z wydajnością:
   • Zazwyczaj są one implementowane przy użyciu automatycznie zwiększanych kolumn, co oznacza:
     • Podróż w obie strony do bazy danych za każdym razem, gdy chcesz uzyskać nowy identyfikator (wiem, że można to poprawić za pomocą algorytmów buforowania lub algorytmów [seq] hilo, ale wciąż te metody mają swoje wady).
     • Jeśli pewnego dnia będziesz musiał przenieść swoje dane z jednego schematu do drugiego (zdarza się to dość często w mojej firmie), możesz napotkać problemy z kolizją identyfikatorów. I tak, wiem, że możesz używać UUID, ale te ostatnie wymagają 32 cyfr szesnastkowych! (Jeśli zależy Ci na rozmiarze bazy danych, może to być problem).
     • Jeśli używasz jednej sekwencji dla wszystkich kluczy zastępczych, to na pewno zakończysz rywalizację o bazę danych.
2. Podatne na błędy. Sekwencja ma limit max_value, więc jako programista musisz zwrócić uwagę na następujące punkty:
   • Musisz cyklicznie wykonywać swoją sekwencję (po osiągnięciu maksymalnej wartości wraca ona do 1,2, ...).
   • Jeśli używasz sekwencji jako uporządkowania (w czasie) swoich danych, musisz zająć się przypadkiem cyklicznym (kolumna z Id 1 może być nowsza niż wiersz z Id max-value - 1).
   • Upewnij się, że Twój kod (a nawet interfejsy klienta, które nie powinny się zdarzyć, ponieważ powinien to być wewnętrzny identyfikator) obsługuje liczby całkowite 32b / 64b, których użyłeś do przechowywania wartości sekwencji.
3. Nie gwarantują, że dane nie zostaną zduplikowane. Zawsze możesz mieć 2 wiersze z tymi samymi wartościami kolumn, ale z inną wygenerowaną wartością. Dla mnie jest to problem zastępczych kluczy z punktu widzenia projektowania baz danych.
4. Więcej w Wikipedii ...

Mity na temat kluczy naturalnych

1. Klucze złożone są mniej wydajne niż klucze zastępcze. Nie! To zależy od używanego silnika bazy danych:
   • Wyrocznia
   • MySQL
2. Naturalne klucze nie istnieją w prawdziwym życiu. Przepraszamy, ale one istnieją! Na przykład w przemyśle lotniczym następująca krotka będzie zawsze niepowtarzalna w odniesieniu do danego zaplanowanego lotu (linia lotnicza, data odlotu, numer lotu, sufiks operacyjny). Mówiąc bardziej ogólnie, gdy zbiór danych biznesowych gwarantuje, że jest on niepowtarzalny przez dany standard, wówczas ten zestaw danych jest [dobrym] naturalnym kandydatem na klucz.
3. Klucze naturalne „zanieczyszczają schemat” tabel potomnych. Dla mnie to bardziej uczucie niż prawdziwy problem. Posiadanie 4-kolumnowego klucza podstawowego po 2 bajty każda może być bardziej wydajne niż pojedyncza kolumna o długości 11 bajtów. Poza tym 4 kolumny mogą być używane do bezpośredniego wykonywania zapytań w tabeli podrzędnej (przy użyciu 4 kolumn w klauzuli where) bez łączenia się z tabelą nadrzędną.

Wniosek

Używaj kluczy naturalnych, gdy jest to konieczne, i kluczy zastępczych, gdy lepiej ich używać.

Mam nadzieję, że to komuś pomogło!

Zawsze używaj klucza, który nie ma znaczenia biznesowego. To po prostu dobra praktyka.

EDYCJA: Próbowałem znaleźć link do tego w Internecie, ale nie mogłem. Jednak w „Patterns of Enterprise Archtecture” [Fowler] zawiera dobre wyjaśnienie, dlaczego nie należy używać niczego innego niż klucza, który nie ma żadnego innego znaczenia niż bycie kluczem. Sprowadza się to do tego, że powinna mieć jedną pracę i tylko jedną pracę.

Klucze zastępcze są bardzo przydatne, jeśli planujesz używać narzędzia ORM do obsługi / generowania klas danych. Chociaż możesz używać kluczy złożonych z niektórymi bardziej zaawansowanymi programami mapującymi (czytaj: hibernuj), dodaje to nieco złożoności do twojego kodu.

(Oczywiście puryści baz danych będą argumentować, że nawet pojęcie zastępczego klucza jest obrzydliwością).

Jestem fanem używania uidów do kluczy zastępczych, gdy jest to odpowiednie. Główną wygraną z nimi jest to, że znasz klucz z wyprzedzeniem, np. Możesz utworzyć instancję klasy z już ustawionym identyfikatorem i gwarantowaną unikalnością, podczas gdy z, powiedzmy, kluczem całkowitoliczbowym musisz domyślnie ustawić 0 lub - 1 i zaktualizuj do odpowiedniej wartości podczas zapisywania / aktualizacji.

Identyfikatory UID mają jednak określone kary w zakresie szybkości wyszukiwania i łączenia, więc zależy to od danej aplikacji, czy są one pożądane.

Użycie klucza zastępczego jest moim zdaniem lepsze, ponieważ nie ma szans na jego zmianę. Prawie wszystko, co przychodzi mi do głowy, czego mógłbyś użyć jako naturalnego klucza, może się zmienić (zastrzeżenie: nie zawsze prawdziwe, ale często).

Przykładem może być DB samochodów - na pierwszy rzut oka można by pomyśleć, że jako klucza można by użyć tablicy rejestracyjnej. Ale można to zmienić, więc byłby to zły pomysł. Naprawdę nie chciałbyś się tego dowiedzieć po wydaniu aplikacji, gdy ktoś przychodzi do ciebie, chcąc wiedzieć, dlaczego nie może zmienić swojej tablicy rejestracyjnej na nową, błyszczącą, spersonalizowaną.

Zawsze używaj pojedynczej kolumny i klucza zastępczego, jeśli to możliwe. Dzięki temu łączenia, a także wstawianie / aktualizowanie / usuwanie są znacznie bardziej przejrzyste, ponieważ jesteś odpowiedzialny tylko za śledzenie pojedynczej informacji w celu utrzymania rekordu.

Następnie, w razie potrzeby, ułóż klucze biznesowe jako unikalne ograniczenia lub indeksy. Zapewni to integralność danych.

Logika biznesowa / klucze naturalne mogą się zmieniać, ale klucz fizyczny tabeli NIGDY nie powinien się zmieniać.

Uważam, że w scenariuszu z magazynem danych lepiej jest podążać ścieżką klucza zastępczego. Dwa powody:

• Jesteś niezależny od systemu źródłowego i zmiany w nim - takie jak zmiana typu danych - nie mają na Ciebie wpływu.
• Twój DW będzie potrzebował mniej miejsca fizycznego, ponieważ będziesz używać tylko typów danych całkowitych dla kluczy zastępczych. Również twoje indeksy będą działać lepiej.

Klucze zastępcze mogą być przydatne, gdy informacje biznesowe mogą ulec zmianie lub być identyczne. W końcu nazwy firm nie muszą być unikalne w całym kraju. Załóżmy, że masz do czynienia z dwiema firmami o nazwie Smith Electronics, jedną w Kansas i jedną w Michigan. Możesz je rozróżnić po adresie, ale to się zmieni. Nawet stan może się zmienić; co, jeśli Smith Electronics z Kansas City w Kansas przeniosła się przez rzekę do Kansas City w stanie Missouri? Nie ma oczywistego sposobu na odróżnienie tych biznesów za pomocą naturalnych informacji kluczowych, więc klucz zastępczy jest bardzo przydatny.

Pomyśl o zastępczym kluczu jak o numerze ISBN. Zazwyczaj identyfikuje się książkę według tytułu i autora. Jednak mam dwie książki zatytułowane „Pearl Harbor” autorstwa HP Willmott i są to zdecydowanie różne książki, a nie tylko różne wydania. W takim przypadku mógłbym odnieść się do wyglądu książek lub wcześniejszego kontra późniejszego, ale dobrze, że mam numer ISBN, na którym mogę się oprzeć.

Przypominamy, że nie jest dobrą praktyką umieszczanie indeksów klastrowych na losowych kluczach zastępczych, tj. Identyfikatorach GUID, które odczytują XY8D7-DFD8S, ponieważ SQL Server nie ma możliwości fizycznego sortowania tych danych. Zamiast tego należy umieścić unikalne indeksy w tych danych, chociaż może być również korzystne uruchomienie profilera SQL dla operacji na głównej tabeli, a następnie umieszczenie tych danych w Doradcy dostrajania aparatu bazy danych.

Zobacz wątek @ http://social.msdn.microsoft.com/Forums/en-us/sqlgetstarted/thread/27bd9c77-ec31-44f1-ab7f-bd2cb13129be

Przypadek 1: Twoja tabela jest tabelą przeglądową z mniej niż 50 typami (wstawkami)

Użyj kluczy biznesowych / naturalnych . Na przykład:

Table: JOB with 50 inserts
CODE (primary key)       NAME               DESCRIPTION
PRG                      PROGRAMMER         A programmer is writing code
MNG                      MANAGER            A manager is doing whatever
CLN                      CLEANER            A cleaner cleans
...............
joined with
Table: PEOPLE with 100000 inserts

foreign key JOBCODE in table PEOPLE
looks at
primary key CODE in table JOB

Przypadek 2: Twój stół to stół z tysiącami wkładek

Użyj kluczy zastępczych / automatycznych . Na przykład:

Table: ASSIGNMENT with 1000000 inserts
joined with
Table: PEOPLE with 100000 inserts

foreign key PEOPLEID in table ASSIGNMENT
looks at
primary key ID in table PEOPLE (autoincrement)

W pierwszym przypadku:

• Możesz wybrać wszystkich programistów w tabeli LUDZIE bez użycia łączenia z tabelą JOB, ale tylko za pomocą: "SELECT * FROM PEOPLE WHERE JOBCODE = 'PRG'"

W drugim przypadku:

• Twoje zapytania do bazy danych są szybsze, ponieważ klucz podstawowy jest liczbą całkowitą
• Nie musisz zawracać sobie głowy znalezieniem kolejnego unikatowego klucza, ponieważ sama baza danych daje ci następny automatyczny przyrost.

Jest to jeden z tych przypadków, w których klucz zastępczy prawie zawsze ma sens. Są przypadki, w których wybierasz to, co jest najlepsze dla bazy danych lub to, co jest najlepsze dla modelu obiektów, ale w obu przypadkach lepszym pomysłem jest użycie bezsensownego klucza lub identyfikatora GUID. To sprawia, że ​​indeksowanie jest łatwiejsze i szybsze, a tożsamość obiektu nie ulega zmianie.

Koń na kursy. Aby wyrazić moją stronniczość; Najpierw jestem programistą, więc głównie zależy mi na tym, aby dać użytkownikom działającą aplikację.

Pracowałem nad systemami z naturalnymi kluczami i musiałem spędzać dużo czasu, upewniając się, że zmiany wartości będą widoczne.

Pracowałem na systemach z tylko kluczami zastępczymi, a jedyną wadą był brak zdenormalizowanych danych do partycjonowania.

Większość tradycyjnych programistów PL / SQL, z którymi pracowałem, nie lubiła kluczy zastępczych ze względu na liczbę tabel na złączenie, ale nasze testowe i produkcyjne bazy danych nigdy nie wzbudziły wysiłku; dodatkowe łączenia nie wpłynęły na wydajność aplikacji. W przypadku dialektów bazy danych, które nie obsługują klauzul, takich jak „X wewnętrzne sprzężenie Y na Xa = Yb”, lub deweloperów, którzy nie używają tej składni, dodatkowe łączenia dla kluczy zastępczych sprawiają, że zapytania są trudniejsze do odczytania i dłuższe wpisywanie i sprawdź: zobacz post @Tony Andrews. Ale jeśli używasz ORM lub jakiejkolwiek innej struktury generującej SQL, nie zauważysz tego. Pisanie bezwzrokowe również łagodzi.

Może nie do końca dotyczy tego tematu, ale ból głowy mam do czynienia z kluczami zastępczymi. Wstępnie dostarczone narzędzia analityczne Oracle tworzą automatycznie generowane SK na wszystkich swoich tabelach wymiarów w magazynie, a także przechowują je jako fakty. Tak więc za każdym razem, gdy trzeba je (wymiary) ponownie załadować, ponieważ nowe kolumny są dodawane lub muszą być wypełnione dla wszystkich elementów w wymiarze, SK przypisane podczas aktualizacji powodują, że SK nie są zsynchronizowane z oryginalnymi wartościami zapisanymi w rzeczywistości, wymuszając całkowite przeładowanie wszystkich dołączonych do niego tabel faktów. Wolałbym, żeby nawet jeśli SK była liczbą bez znaczenia, byłby jakiś sposób, aby nie mogła się zmienić dla oryginalnych / starych płyt. Jak wielu wie, gotowe rozwiązania rzadko spełniają potrzeby organizacji i musimy stale je dostosowywać. Mamy teraz dane z 3 lat w naszej hurtowni, a pełne doładowania z systemów Oracle Financial są bardzo duże. Więc w moim przypadku nie są one generowane na podstawie wprowadzania danych, ale dodawane w hurtowni, aby pomóc w raportowaniu wydajności. Rozumiem, ale nasze się zmieniają i to jest koszmar.

W przypadku bazy danych punktu w czasie najlepiej jest mieć kombinację kluczy zastępczych i naturalnych. np. musisz śledzić informacje o członkach klubu. Niektóre cechy członka nigdy się nie zmieniają. np. data urodzenia, ale nazwisko może ulec zmianie. Stwórz więc tabelę Member z zastępczym kluczem member_id i miej kolumnę dla DOB. Utwórz kolejną tabelę o nazwie imię i nazwisko osoby i miej kolumny dla id_członka, nazwa_członka, nazwa_członka, data_updated. W tej tabeli naturalnym kluczem byłby identyfikator_członka + data_updated.