Złożone klucze podstawowe a unikalne pole ID obiektu

Pracuję nad aplikacjami bazodanowymi od 15 lat i jeszcze nie spotkałem się z przypadkiem, w którym klucz zastępczy był lepszym Wyborem niż klucz zastępczy.

Nie mówię, że taki przypadek nie istnieje, mówię tylko, że gdy uwzględnisz praktyczne kwestie związane z tworzeniem aplikacji, która uzyskuje dostęp do bazy danych, Zwykle korzyści z klucza zastępczego zaczynają przytłaczać teoretyczną czystość kluczy innych niż zastępcze.

Klucz podstawowy powinien być stały i bezsensowny ; klucze nie zastępcze zwykle nie spełniają jednego lub obu wymagań, ostatecznie

• Jeśli klucz nie jest stały, w przyszłości pojawi się problem z aktualizacją, który może być dość skomplikowany

• Jeśli klucz nie jest bez znaczenia, to jest bardziej prawdopodobne, że się zmieni, tzn. nie będzie stały; patrz powyżej

Weźmy prosty, powszechny przykład: tabelę pozycji inwentarza. Może być kuszące, aby Numer Pozycji (sku numer, kod kreskowy, kod części, czy cokolwiek) klucz główny, ale rok później wszystkie numery pozycji zmieniają się {[2] } i zostajesz z bardzo niechlujnym problemem aktualizacji całej bazy danych...

EDIT: jest dodatkowa kwestia, która jest bardziej praktyczna niż filozoficzna. W wielu przypadkach znajdziesz w jakiś sposób konkretny wiersz, a następnie zaktualizuj go lub znajdź ponownie (lub oba). W przypadku kluczy złożonych jest więcej danych do śledzenia i więcej kontraktów w klauzuli WHERE dla ponownego znalezienia lub uaktualnić (lub usunąć). Możliwe jest również, że jeden z kluczowych segmentów mógł się w międzyczasie zmienić!. W przypadku klucza zastępczego zawsze jest tylko jedna wartość do zachowania (identyfikator zastępczy) i z definicji nie może się zmienić, co znacznie upraszcza sytuację.

To brzmi tak, jakby osoba, która stworzyła bazę danych, znajdowała się po stronie kluczy naturalnych w wielkiej debacie klucze naturalne kontra klucze zastępcze.

Nigdy nie słyszałem o żadnych problemach z btrees na polach ID, ale również nie studiowałem go w żadnej dużej głębi...

Spadam na stronę klucza zastępczego: masz mniej powtórzeń podczas używania klucza zastępczego, ponieważ powtarzasz tylko jedną wartość w innych tabelach. Ponieważ ludzie rzadko dołączają do stolików ręcznie, nie obchodzi nas, czy to numer albo nie. Ponadto, ponieważ w indeksie jest tylko jedna kolumna o stałym rozmiarze, można bezpiecznie założyć, że surogaci mają szybszy czas wyszukiwania według klucza podstawowego.

Używanie pól 'unique (object) ID' upraszcza łączenie, ale powinieneś dążyć do tego, aby drugi (prawdopodobnie złożony) klucz był nadal unikalny - nie zmniejszaj ograniczeń not-null i zachowuj unikalne ograniczenie.

Jeśli DBMS nie może skutecznie obsługiwać unikalnych liczb całkowitych, ma duże problemy. Jednak użycie zarówno "unique (object) ID", jak i drugiego klucza wymaga więcej miejsca (dla indeksów) niż tylko drugiego klucza i ma dwa indeksy do aktualizacji przy każdej operacji wstawiania. Więc to nie jest freebie ... ale tak długo, jak zachowasz oryginalny klucz, to będzie dobrze. Jeśli wyeliminujesz drugi klucz, łamiesz konstrukcję swojego systemu; całe piekło w końcu się uwolni (i możesz, ale nie możesz zauważyć, że piekło się uwolniło).

W zasadzie jestem członkiem zespołu kluczy zastępczych i nawet jeśli doceniam i Rozumiem argumenty, takie jak te przedstawione tutaj przez JeremyDWill, nadal szukam przypadku, w którym klucz "naturalny" jest lepszy niż klucz zastępczy ...

Inne posty zajmujące się tym zagadnieniem zwykle odnoszą się do teorii relacyjnych baz danych i wydajności baz danych. Innym interesującym argumentem, zawsze zapomnianym w tym przypadku, jest normalizacja tabeli i produktywność kodu :

Za każdym razem, gdy tworzę tabelę, tracić czas

1. identyfikowanie klucza głównego i jego właściwości fizyczne (rodzaj, Rozmiar)
2. zapamiętanie tych cech za każdym razem chcę się do niego odnieść w mój kod?
3. Wyjaśnienie mojego wyboru PK innym Programiści w zespole?

Moja odpowiedź brzmi nie na wszystkie te pytania:

1. nie mam czasu do stracenia próbując określ "najlepszy klucz podstawowy", gdy / align = "left" / osób.
2. nie chcę pamiętać, że Klucz podstawowy mojej tabeli" computer" jest ciągiem o długości 64 znaków (czy Windows akceptuje, że wiele znaków dla nazwy komputera?).
3. nie chcę tłumaczyć mojego wyboru do innych deweloperów, gdzie jeden z nich w końcu powie "tak stary, ale weź pod uwagę, że musisz zarządzać Komputery nad różnymi domenami? Czy ten ciąg 64 znaków pozwala aby zapisać nazwę domeny + nazwa komputera?".

Więc pracowałem przez ostatnie pięć lat z bardzo podstawową zasadą: każda tabela (nazwijmy ją " myTable") ma swoje pierwsze pole o nazwie " id_MyTable", które jest typu uniqueIdentifier. Nawet jeśli ta tabela obsługuje relację "many-to-many", taką jak Tabela' ComputerUser', gdzie kombinacja ' id_Computer 'i' id_User 'tworzy bardzo akceptowalny klucz podstawowy, wolę utworzyć To pole' id_ComputerUser ' jako uniqueIdentifier, aby trzymać się reguły.

Główną zaletą jest to, że nie musisz przejmować się używaniem Klucz podstawowy i / lub klucz obcy w Twoim kodzie. Gdy już masz nazwę tabeli, znasz nazwę PK i wpisz. Gdy dowiesz się, które łącza są zaimplementowane w twoim modelu danych, poznasz nazwę dostępnych kluczy obcych w tabeli.

Używanie klawiszy naturalnych sprawia, że korzystanie z dowolnego automatycznego ORM jako warstwy trwałości jest koszmarem. Ponadto klucze obce w wielu kolumnach mają tendencję do nakładania się na siebie, co spowoduje dalszy problem podczas nawigowania i aktualizowania relacji w sposób OO.

Nadal można przekształcić klucz naturalny w unikalny ogranicznik i dodać automatycznie wygenerowany identyfikator; to nie usuwa problemu z kluczami obcymi, choć te będą musiały być zmieniane ręcznie; miejmy nadzieję, że wiele kolumn i nakładające się ograniczenia będą mniejszością wszystkich relacji, więc możesz skoncentrować się na refaktoryzacji tam, gdzie ma to największe znaczenie.

Naturalne pk mają swoją motywację i zwyczaje i nie są złe (tm), po prostu mają tendencję do nie dogadywania się dobrze z ORM.

Moje odczucie jest takie, że jak wszystkie inne pojęcia, klucze naturalne i normalizacja tabel powinny być używane, gdy są rozsądne, a nie jako ślepe ograniczenia projektowe

Powiem krótko i słodko: Dodaj zastępcze dowolne klucze, jeśli możesz i utrzymuj bieżące Schematy kluczy za pomocą unikalnych ograniczeń. ORM jest szczęśliwy, ty jesteś szczęśliwy, oryginalny programista nie-taki-szczęśliwy, ale jeśli nie jest twoim szefem, to może sobie z tym poradzić.

Praktycznym podejściem do tworzenia nowej architektury jest takie, które wykorzystuje klucze zastępcze dla tabel, które będą zawierać tysiące wielokolumnowych rekordów i klucze złożone dla krótkich tabel opisowych. Zwykle stwierdzam, że uczelnie dyktują użycie kluczy zastępczych, podczas gdy programiści z prawdziwego świata wolą klucze złożone. Naprawdę musisz zastosować odpowiedni typ klucza podstawowego do tabeli - nie tylko w ten czy inny sposób.

Klucze złożone mogą być dobre - mogą wpływać na wydajność - ale nie są jedyną odpowiedzią, tak samo jak unikalny (zastępczy) klucz nie jest jedyną odpowiedzią.

Martwi mnie niejasność w rozumowaniu przy wyborze kluczy złożonych. Najczęściej niejasność w cokolwiek technicznego wskazuje na brak zrozumienia-może przestrzeganie cudzych wytycznych, w książce lub artykule....

Nie ma nic złego w pojedynczym unikalnym ID-fakcie, jeśli masz aplikację podłączoną do serwera bazy danych i możesz wybrać, której bazy danych używasz to wszystko będzie dobre, i możesz prawie zrobić wszystko z kluczami i naprawdę nie cierpieć zbyt źle.

Było i będzie dużo o tym napisano, bo nie ma jednej odpowiedzi. Istnieją metody i podejścia, które należy stosować ostrożnie w sposób wykwalifikowany.

Miałem wiele problemów z tym, że ID jest dostarczane automatycznie przez bazę danych - I unikaj ich tam, gdzie to możliwe, ale nadal używaj ich od czasu do czasu.

... jak baza danych radzi sobie z polami ID w nieefektywny sposób i kiedy buduje indeksy, sortowanie drzew jest wadliwe ...

Było to prawie na pewno nonsensem, ale mogło mieć związek z kwestią kwestionowania bloków indeksu podczas przypisywania liczb rosnących do PK z dużą szybkością z różnych sesji. Jeśli tak, to indeks odwróconego klucza jest tam, aby pomóc, aczkolwiek kosztem większego rozmiaru indeksu ze względu na zmianę algorytmu podziału bloków. http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref998

Go syntetyczne, szczególnie jeśli pomaga szybszy rozwój z zestawem narzędzi.

Nie jestem doświadczonym, ale nadal jestem za używaniem klucza podstawowego, ponieważ id tutaj jest wyjaśnienie na przykładzie..

Format danych zewnętrznych może się zmieniać w czasie. Na przykład, można by pomyśleć, że ISBN książki będzie dobry klucz podstawowy w tabeli książek. W końcu ISBN są wyjątkowe. Ale ponieważ ta konkretna książka jest pisana, przemysł wydawniczy w Stanach Zjednoczonych przygotowuje się do poważnej zmiany, ponieważ dodatkowe cyfry są dodawane do wszystkich ISBN. Gdybyśmy używając ISBN jako klucza podstawowego w tabeli książek, musielibyśmy aktualizować każdy wiersz, aby odzwierciedlić tę zmianę. Ale wtedy mielibyśmy inny problem. W bazie danych będą znajdować się inne tabele, które odwołują się do wierszy w tabeli książki za pomocą klucza głównego. Nie możemy zmienić klucza w tabeli książek, chyba że najpierw przejrzymy i zaktualizujemy wszystkie te odniesienia. A to będzie wymagało zniesienia ograniczeń klucza obcego, aktualizacji tabel, aktualizacji tabeli książek i wreszcie przywrócenia ograniczeń. Wszystkie w sumie to jest coś w rodzaju bólu. Problemy znikną, jeśli użyjemy własnej wartości wewnętrznej jako klucza podstawowego. Żadna osoba trzecia nie może przyjść i arbitralnie powiedzieć nam, aby zmienić nasz schemat-kontrolujemy własną przestrzeń kluczy. A jeśli coś takiego jak ISBN musi się zmienić, może się zmienić bez wpływu na jakiekolwiek istniejące relacje w bazie danych. W efekcie oddzieliliśmy łączenie wierszy od zewnętrznej reprezentacji danych w tych wierszach.

Chociaż Wyjaśnienie jest dość książkowe, ale myślę, że wyjaśnia rzeczy w prostszy sposób.

@JeremyDWill

Dziękuję za zapewnienie bardzo potrzebnej równowagi w debacie. W szczególności, dzięki za informacje na DOMAIN s.

Właściwie używam kluczy zastępczych w całym systemie dla zachowania spójności, ale są kompromisy. Najczęstszą przyczyną dla mnie przeklinać za pomocą kluczy zastępczych jest, gdy mam tabelę wyszukiwania z krótką listą wartości kanonicznych-użyłbym mniej miejsca i wszystkie moje zapytania byłyby krótsze / łatwiejsze / szybsze, gdybym tylko miał wartości PK zamiast dołączyć do stołu.

Możesz zrobić jedno i drugie - ponieważ każda duża baza danych firmy może być używana przez kilka aplikacji, w tym ludzkie bazy danych uruchamiające jednorazowe zapytania i import danych, projektowanie jej wyłącznie na rzecz systemów ORM nie zawsze jest praktyczne lub pożądane.

To, co obecnie robię, to dodawanie właściwości "RowID" do każdej tabeli - to pole jest identyfikatorem GUID i tak unikalnym dla każdego wiersza. Nie jest to klucz podstawowy - jest to klucz naturalny (jeśli to możliwe). Jednak wszelkie warstwy ORM działające na ta baza danych może używać RowID do identyfikacji ich pochodnych obiektów.

Więc możesz mieć:

CREATE TABLE dbo.Invoice (
  CustomerId varchar(10),
  CustomerOrderNo varchar(10),
  InvoiceAmount money not null,
  Comments nvarchar(4000),
  RowId uniqueidentifier not null default(newid()),

  primary key(CustomerId, CustomerOrderNo)
)

Więc twój DBA jest szczęśliwy, twój architekt ORM jest szczęśliwy, a integralność bazy danych jest zachowana!

Chciałem tylko dodać coś, czego nigdy nie widzę, omawiając automatycznie generowane pola tożsamości integer z relacyjnymi bazami danych (ponieważ widzę je często), a to znaczy, że jest to typ bazowy, który może w pewnym momencie przepełnić się.

Teraz nie próbuję powiedzieć, że to automatycznie sprawia, że composite ID jest drogą do zrobienia, ale faktem jest, że nawet jeśli więcej danych można logicznie dodać do tabeli( która jest nadal unikalna), pojedyncza automatycznie wygenerowana tożsamość całkowita może temu zapobiec.

Tak, zdaję sobie sprawę, że w większości sytuacji jest to mało prawdopodobne, a użycie 64-bitowej liczby całkowitej daje dużo miejsca na głowie, a realistycznie baza danych prawdopodobnie powinna być zaprojektowana inaczej, jeśli takie przepełnienie kiedykolwiek miało miejsce.

Ale to nikogo nie powstrzymuje... tabela wykorzystująca pojedynczą automatycznie wygenerowaną 32-bitową liczbę całkowitą jako tożsamość, która ma przechowywać wszystkie transakcje na poziomie globalnym dla danego fast-foodu firma, nie powiedzie się, gdy tylko spróbuje wstawić to 2,147,483,648 TH transakcja (i to jest całkowicie wykonalny scenariusz).

To jest po prostu coś do zauważenia, że ludzie mają tendencję do tuszowania lub po prostu ignorować całkowicie. Jeśli jakakolwiek tabela ma być wstawiana z regularnością, należy rozważyć, jak często i ile danych będzie gromadzić się w czasie, i czy identyfikator oparty na liczbie całkowitej powinien być nawet używany.