Czy rozsądne jest oznaczenie wszystkich kolumn oprócz jednej jako klucza podstawowego?

Mam stolik przedstawiający filmy. Pola są:
id (PK), title, genre, runtime, released_in, tags, origin, downloads.

Moja baza danych nie może zostać zanieczyszczona przez zduplikowane wiersze, dlatego chcę wymusić unikalność. Problem polega na tym, że różne filmy mogą mieć ten sam tytuł lub nawet te same pola, z wyjątkiem tagsi downloads. Jak egzekwować wyjątkowość?

Myślałem o dwóch sposobach:

• utwórz wszystkie pola oprócz downloadsklucza podstawowego. Trzymam się z downloadsdaleka, ponieważ jest to JSON i prawdopodobnie wpłynie to na wydajność.
• zachowaj tylko idjako klucz podstawowy, ale dodaj unikalne ograniczenie do wszystkich pozostałych kolumn (oprócz, znowu, downloads).

Przeczytałem to pytanie, które jest bardzo podobne, ale nie bardzo rozumiałem, co powinienem zrobić. Obecnie ta tabela nie jest powiązana z żadnymi innymi tabelami, ale w przyszłości może być.

W tej chwili mam nieco mniej niż 20 000 rekordów, ale spodziewam się, że liczba ta wzrośnie. Nie wiem, czy to ma jakiś związek z tym problemem.

EDYCJA: Zmodyfikowałem schemat i oto jak utworzę tabelę:

CREATE TABLE movies (
    id          serial PRIMARY KEY,
    title       text NOT NULL,
    runtime     smallint NOT NULL CHECK (runtime >= 0),
    released_in smallint NOT NULL CHECK (released_in > 0),
    genres      text[] NOT NULL default ARRAY[]::text[],
    tags        text[] NOT NULL default ARRAY[]::text[],
    origin      text[] NOT NULL default ARRAY[]::text[],
    downloads   json NOT NULL,
    inserted_at timestamp NOT NULL default current_timestamp,
    CONSTRAINT must_be_unique UNIQUE(title,runtime,released_in,genres,tags,origin)
);

Dodałem również timestampkolumnę, ale nie stanowi to problemu, ponieważ jej nie dotknę. Zawsze będzie więc automatyczny i niepowtarzalny.

Twoja definicja tabeli wygląda teraz rozsądnie. Ze wszystkich kolumn ograniczeniem będzie działać zgodnie z oczekiwaniami - z wyjątkiem literówki i drobne różnice w pisowni, co może być dość powszechne obawiam. Zastanów się nad komentarzem @ a_horse .NOT NULLUNIQUE

Alternatywa z funkcjonalnym unikalnym indeksem

Inną opcją byłby funkcjonalny, unikalny indeks (podobny do tego, co komentował @Dave ). Ale uuidużyłbym typu danych, aby zoptymalizować rozmiar indeksu i wydajność.

Rzut z tablicy na tekst nie jest IMMUTABLE(ze względu na jego ogólną implementację):

• Indeksowanie tablicy do wyszukiwania pełnotekstowego

Dlatego potrzebujesz małej funkcji pomocnika, aby zadeklarować, że jest niezmienna:

CREATE OR REPLACE FUNCTION f_movie_uuid(_title text
                                      , _runtime int2
                                      , _released_in int2
                                      , _genres text[]
                                      , _tags text[]
                                      , _origin text[])
  RETURNS uuid LANGUAGE sql IMMUTABLE AS  -- faking IMMUTABLE
'SELECT md5(_title || _runtime::text || _released_in::text
         || _genres::text || _tags::text || _origin::text)::uuid';

Użyj go do definicji indeksu:

CREATE UNIQUE INDEX movies_uni_idx
ON movies (f_movie_uuid(title,runtime,released_in,genres,tags,origin));

SQL Fiddle.

Więcej szczegółów:

• Gdyby wszystkie wartości miały 36 znaków, wyszukiwanie indeksu byłoby zauważalnie szybsze przy użyciu char vs varchar
• Konwertuj szesnastkową reprezentację tekstu na liczbę dziesiętną

Możesz użyć wygenerowanego UUID jako PK, ale nadal użyłbym serialkolumny z 4 bajtami, która jest prosta i tania dla referencji FK i innych celów. UUID byłby świetną opcją dla systemów rozproszonych, które muszą generować wartości PK niezależnie. Lub przy bardzo dużych stołach, ale w naszym Układzie Słonecznym nie ma wystarczającej ilości filmów.

Plusy i minusy

UNIQUE jest realizowany z unikalnym indeksie na zaangażowanych kolumn. Najpierw umieść odpowiednie kolumny w definicji ograniczenia, a otrzymasz przydatny indeks do innych celów jako korzyść z zabezpieczenia.

Istnieją inne szczególne korzyści, oto lista:

• W jaki sposób PostgreSQL wymusza ograniczenie UNIQUE / jakiego typu indeks używa?

Funkcjonalny unikalny wskaźnik jest (potencjalnie wiele) mniejszej wielkości, co może go znacznie szybciej. Jeśli twoje kolumny nie są zbyt duże, różnica nie będzie duża. Istnieje również niewielki koszt ogólny obliczeń.

Łączenie wszystkich kolumn może wprowadzać fałszywe alarmy ( 'foo ' || 'bar' = 'foob ' || 'ar'ale wydaje się to bardzo mało prawdopodobne w tym przypadku. Literówki są o wiele bardziej prawdopodobne, że możesz bezpiecznie zignorować to tutaj.

Wyjątkowość i tablice

Tablice musiałyby być konsekwentnie sortowane, aby miały sens w każdym unikalnym układzie polegającym na =operatorze, ponieważ '{1,2}' <> '{2,1}'. Proponuję Wysz tabel do genre, tagi originz serialPK i unikalnych wpisów, które umożliwiają wyszukiwanie rozmyte dla elementów tablicy. Następnie:

• albo wdrożyć w pełni znormalizowane relacje n: m, które również zapewniają integralność referencyjną. Unikalność każdego zestawu odniesień jest trudniejsza do ustalenia, można użyć MATERIALIZE VIEW(MV) z zagregowanymi tablicami jako odskocznią.

  • Jak wdrożyć relację wiele do wielu w PostgreSQL?

• lub działają z posortowanymi tablicami referencji FK (które nie mogą być jeszcze obsługiwane przez ograniczenia FK). Przydatne mogą być narzędzia z dodatkowej tablicy modułów :

  • Porównaj tablice dla równości, ignorując kolejność elementów

Tak czy inaczej, pracując bezpośrednio z tablicami lub ze znormalizowanym schematem i zmaterializowanym widokiem, wyszukiwanie może być bardzo wydajne z odpowiednim indeksem i operatorami:

• Czy PostgreSQL może indeksować kolumny tablicowe?

Jeśli korzystasz z Postgres 9.4 lub nowszego, rozważ jsonbzamiastjson .

Wyobraź sobie, że jesteś z grupą przyjaciół, a rozmowa zamienia się w filmy. Ktoś pyta: „Co sądzisz o„ Trzech muszkieterach ”? Odpowiadasz: „Który?”

Jakich dodatkowych informacji potrzebujesz, aby mieć absolutną pewność, że oboje myślicie o tym samym filmie? Nazwisko reżysera? Studio produkcyjne? Rok wydania? Jedna z nazw gwiazdy? Jakaś kombinacja dwóch lub więcej?

Odpowiedź na moje pytanie i twoje są takie same.

Jednak nie sądzę, że ten gatunek byłby dobrym kandydatem. Jednym z powodów jest to, że gatunek jest zbyt subiektywnym kryterium. Czy akcja „Trzej muszkieterowie”? dramat? przygoda? komedia? akcja Przygoda? komedia romantyczna? Często widzę ten sam film na liście różnych gatunków. Nawet jeśli zezwalasz na wiele gatunków, użytkownik może wybrać zupełnie inny, niewymieniony na liście szukanego filmu.

Nawet środowiska wykonawcze mogą się różnić, szczególnie między wersjami kinowymi i magnetowidami / DVD / b-ray.

Potrzebujesz więc twardych, obiektywnych atrybutów, które nie zmienią się z jednej wersji medialnej na drugą. Niestety może to wykluczać nazwę filmu, ponieważ wiadomo, że filmy zostały przemianowane, szczególnie po wydaniu sequela.

Co z datą wydania? Wydanie teatralne z 1993 roku? Wydanie VCR z 1999 roku? Wydanie DVD z 2004 roku? Masz pomysł.

Zastanów się, a co z tymi filmami w reżyserii Alana Smithee? Czy prawdziwy reżyser kiedykolwiek w końcu wystąpił z propozycją podania swojego nazwiska po tym fakcie? Nie wiem

Hmm, lepiej przestanę, dopóki pozostaną jeszcze jakieś kryteria.

Niektóre dodatkowe punkty:

• Tak, zachowaj klucz zastępczy i utwórz unikalny indeks na naturalnych polach klucza (jeśli możesz w końcu je poprawić). Klucz zastępczy jest najlepszy dla odniesień do klucza obcego. Nie chcesz powielać wszystkich pól naturalnych kluczy w każdej tabeli zawierającej odniesienie do filmu.
• Upuść pola tablicy (gatunki, tagi, pochodzenie). Śmiało i odpowiednio znormalizuj te atrybuty. Nigdy nie widziałem pola tablicy, które nie stanowiło większego problemu niż było warte, szczególnie jeśli chcesz, aby można je było przeszukiwać („... gdzie gatunek =„ horror ”…”). Pamiętaj, że nie wyeliminuje to automatycznie problemów z różnicami liter i pisowni („Science Fiction” vs „SciFi”) - chyba że odpowiednio utrzymasz tabele odnośników . Ale o wiele łatwiej jest sprawdzić takie różnice w jednym polu małego stołu niż w każdej komórce tablicy każdego rzędu dużego stołu.

Kolumna ID nie ma żadnej przewagi, jeśli chodzi o wyjątkowość, którą chcesz / musisz egzekwować. Unikalność jakiejkolwiek kombinacji atrybutów nigdy nie będzie egzekwowana przez dodanie bez znaczenia identyfikatora. Jego „przewaga” pokazuje się tylko wtedy, gdy dojdziesz do momentu, w którym potrzebujesz nowej tabeli, która potrzebuje do tego klucza obcego. W takim przypadku i JEŚLI podałeś identyfikator, możesz użyć go jako FK w nowej tabeli. (Ale nie sądzę, że będzie to darmowy lunch. Minusem takiego podejścia jest to, że prawdopodobnie będziesz pisał więcej złączeń tylko po to, aby uzyskać informacje, które mogłyby idealnie być częścią nowego stołu, który zrobiłeś. )