Jak utrzymywać relację jeden do wielu z uprzywilejowanym dzieckiem?

Chcę mieć relację jeden do wielu, w której dla każdego rodzica jedno lub zero dzieci jest oznaczone jako „ulubione”. Jednak nie każdy rodzic będzie miał dziecko. (Pomyśl o rodzicach jako pytaniach na tej stronie, dzieciach jako odpowiedziach, a ulubionych jako o zaakceptowanych odpowiedziach.) Na przykład

TableA
    Id            INT PRIMARY KEY

TableB
    Id            INT PRIMARY KEY
    Parent        INT NOT NULL FOREIGN KEY REFERENCES TableA.Id

Z mojego punktu widzenia mogę dodać następującą kolumnę do tabeli A:

    FavoriteChild INT NULL FOREIGN KEY REFERENCES TableB.Id

lub następująca kolumna do tabeli B:

    IsFavorite    BIT NOT NULL

Problem z pierwszym podejściem polega na tym, że wprowadza on zerowy klucz obcy, który, jak rozumiem, nie jest w znormalizowanej formie. Problem z drugim podejściem polega na tym, że należy wykonać więcej pracy, aby zapewnić, że najwyżej jedno dziecko będzie faworytem.

Jakie kryteria powinienem zastosować, aby ustalić, które podejście zastosować? Czy są inne podejścia, których nie rozważam?

Korzystam z programu SQL Server 2012.

Innym sposobem (bez wartości zerowych i bez cykli w FOREIGN KEYrelacjach) jest posiadanie trzeciego stołu do przechowywania „ulubionych dzieci”. W większości DBMS potrzebujesz dodatkowego UNIQUEograniczenia TableB.

@Aaron szybciej zidentyfikował, że powyższa konwencja nazewnictwa jest raczej uciążliwa i może prowadzić do błędów. Zazwyczaj jest to lepsze (i zapewni ci rozsądek), jeśli nie masz Idkolumn we wszystkich tabelach i jeśli kolumny (które są połączone) mają takie same nazwy w wielu pojawiających się tabelach. Oto zmiana nazwy:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    UNIQUE (ParentID, ChildID)

FavoriteChild
    ParentID        INT NOT NULL PRIMARY KEY
    ChildID         INT NOT NULL 
    FOREIGN KEY (ParentID, ChildID) 
        REFERENCES Child (ParentID, ChildID)

W SQL-Server (którego używasz) masz również opcję wspomnianej IsFavoritekolumny bitów. Unikalne ulubione dziecko na rodzica można osiągnąć poprzez filtrowany Unikalny Indeks:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    IsFavorite      BIT NOT NULL

CREATE UNIQUE INDEX is_FavoriteChild
  ON Child (ParentID)
  WHERE IsFavorite = 1 ;

A głównym powodem, dla którego twoja opcja 1 nie jest zalecana, przynajmniej nie w SQL Server, jest to, że wzór ścieżek kołowych w odniesieniach do klucza obcego ma pewne problemy.

Przeczytaj dość stary artykuł: SQL By Design: The Circular Reference

Podczas wstawiania lub usuwania wierszy z dwóch tabel napotkasz problem „kurczaka i jajka”. Którą tabelę powinienem wstawić jako pierwszy - bez naruszania jakichkolwiek ograniczeń?

Aby rozwiązać ten problem, musisz zdefiniować co najmniej jedną kolumnę nullable. (OK, technicznie nie masz, możesz mieć wszystkie kolumny tak NOT NULL, ale tylko w DBMS, takich jak PostgreSQL i Oracle, które zostały wdrożone odroczeniu ograniczenia Zobacz @ odpowiedź Erwin w podobnym pytaniem. Kompleks ograniczenie klucz obcy w SQLAlchemy , w jaki sposób można to zrobić w Postgres). Mimo to ta konfiguracja przypomina jazdę na cienkim lodzie.

Sprawdź także prawie identyczne pytanie w SO (ale dla MySQL). W SQL, czy dwie tabele mogą się odnosić? gdzie moja odpowiedź jest prawie taka sama. MySQL nie ma jednak częściowych indeksów, więc jedynymi realnymi opcjami są zerowalne FK i dodatkowe rozwiązanie tabeli.

To zależy od Twojego priorytetu. Czy chcesz uniknąć pracy, czy chcesz przestrzegać najostrzejszych zasad normalizacji?

Osobiście uważam, że lepiej jest mieć IsFavoriteprzy dziecięcym stole i chętnie włożyłby pracę, aby upewnić się, że najwyżej jedno dziecko na każdego rodzica jest faworytem tego rodzica. Główny powód nie ma nic wspólnego z zerowalnym kluczem obcym: nie podoba mi się pomysł, aby klucze obce wskazywały w obu kierunkach.

Sugestia @ ypercube jest również dobrym kompromisem.

Na marginesie, proszę, proszę, nie zaśmiecaj swojego schematu bezsensownymi nazwami kolumn, takimi jak Id. Wolałbym raczej, aby Idnazwa została nazwana w znaczący sposób w całym schemacie. Czy identyfikuje autora? Ok, nazwij to AuthorID. Czy to reprezentuje produkt? Okej ProductID. Czy to pracownik, aw niektórych przypadkach odwołuje się do kierownika? Ok, EmployeeIDi ManagerIDmiej dla mnie więcej sensu niż IDi Parent. Chociaż może się to wydawać logiczne, aby to pominąć (i zbędne, aby to wstawić), kiedy zaczniesz pisać złożone sprzężenia (lub zamieszczać tutaj zapytania), z pewnością wyczujesz przekleństwo, gdy spróbujesz przerobić kilka złączeń tego punktu do kolumn takich jak a.Parent = b.ID... blecch.

Dane należą do tabeli potomnej. Trzymamy go poprawnie za pomocą wyzwalacza na stole, aby upewnić się, że jeden i jedyny rekord jest oznaczony jako ulubiony (lub w naszym przypadku jako preferowany adres).

Jednak pomysł @ ypercube na osobny stół jest również dobry.