Klucz obcy do wielu tabel


127

W mojej bazie danych mam 3 odpowiednie tabele.

CREATE TABLE dbo.Group
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)  

CREATE TABLE dbo.User
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)

CREATE TABLE dbo.Ticket
(
    ID int NOT NULL,
    Owner int NOT NULL,
    Subject varchar(50) NULL
)

Użytkownicy należą do wielu grup. Odbywa się to poprzez relację wiele do wielu, ale w tym przypadku nie ma to znaczenia. Bilet może być własnością grupy lub użytkownika za pośrednictwem pola dbo.Ticket.Owner.

Jaki byłby NAJPOPRAWNIEJSZY sposób opisania relacji między biletem a opcjonalnie użytkownikiem lub grupą?

Myślę, że powinienem dodać flagę w tabeli biletów, która mówi, jaki typ go posiada.


Moim zdaniem każdy bilet jest własnością grupy. Po prostu użytkownik to jedna grupa. Który wybór 4 z modeli @ nathan-skerl. Jeśli używasz GUID jako klucze całość działa dość dobrze
GraemeMiller

Odpowiedzi:


149

Masz kilka opcji, z których wszystkie różnią się „poprawnością” i łatwością użycia. Jak zawsze, właściwy projekt zależy od Twoich potrzeb.

  • Możesz po prostu utworzyć dwie kolumny w Ticket, OwnedByUserId i OwnedByGroupId i przypisać do każdej tabeli klucze obce dopuszczające wartość null.

  • Można utworzyć tabele referencyjne M: M, które umożliwią zarówno relację bilet: użytkownik, jak i bilet: grupa. Być może w przyszłości zechcesz zezwolić, aby jeden bilet był własnością wielu użytkowników lub grup? Ten projekt nie narzuca, że ​​bilet musi być własnością tylko jednej jednostki.

  • Możesz utworzyć domyślną grupę dla każdego użytkownika i mieć bilety po prostu należące do prawdziwej grupy lub domyślnej grupy użytkownika.

  • Lub (mój wybór) modeluj jednostkę, która działa jako baza zarówno dla użytkowników, jak i grup, i ma bilety należące do tej jednostki.

Oto przybliżony przykład użycia opublikowanego schematu:

create table dbo.PartyType
(   
    PartyTypeId tinyint primary key,
    PartyTypeName varchar(10)
)

insert into dbo.PartyType
    values(1, 'User'), (2, 'Group');


create table dbo.Party
(
    PartyId int identity(1,1) primary key,
    PartyTypeId tinyint references dbo.PartyType(PartyTypeId),
    unique (PartyId, PartyTypeId)
)

CREATE TABLE dbo.[Group]
(
    ID int primary key,
    Name varchar(50) NOT NULL,
    PartyTypeId as cast(2 as tinyint) persisted,
    foreign key (ID, PartyTypeId) references Party(PartyId, PartyTypeID)
)  

CREATE TABLE dbo.[User]
(
    ID int primary key,
    Name varchar(50) NOT NULL,
    PartyTypeId as cast(1 as tinyint) persisted,
    foreign key (ID, PartyTypeId) references Party(PartyID, PartyTypeID)
)

CREATE TABLE dbo.Ticket
(
    ID int primary key,
    [Owner] int NOT NULL references dbo.Party(PartyId),
    [Subject] varchar(50) NULL
)

7
Jak wyglądałoby zapytanie o bilety użytkownika / grupy? Dzięki.
paulkon

4
Jaka jest korzyść z utrwalonych kolumn obliczonych w tabelach grup i użytkowników? Klucz podstawowy w tabeli Party zapewnia już, że nie będzie nakładania się identyfikatorów grup i identyfikatorów użytkowników, więc klucz obcy musi znajdować się tylko na samym identyfikatorze PartyId. Wszelkie napisane zapytania i tak musiałyby znać tabele z PartyTypeName.
Arin Taylor

1
@ArinTaylor utrwalona kolumna uniemożliwia nam utworzenie Strony typu User i powiązanie jej z rekordem w dbo.Group.
Nathan Skerl

3
@paulkon Wiem, że to stare pytanie, ale zapytanie byłoby podobne SELECT t.Subject AS ticketSubject, CASE WHEN u.Name IS NOT NULL THEN u.Name ELSE g.Name END AS ticketOwnerName FROM Ticket t INNER JOIN Party p ON t.Owner=p.PartyId LEFT OUTER JOIN User u ON u.ID=p.PartyId LEFT OUTER JOIN Group g on g.ID=p.PartyID;do tego, że w rezultacie miałbyś każdy temat biletu i nazwę właściciela.
Corey McMahon

2
Odnośnie opcji 4, czy ktoś może potwierdzić, czy jest to wzorzec przeciw, czy rozwiązanie dla wzorca anty?
inckka

31

Pierwsza opcja na liście @Nathana Skerla jest tym, co zostało zaimplementowane w projekcie, z którym kiedyś pracowałem, w którym podobna relacja została ustalona między trzema tabelami. (Jeden z nich odnosił się do dwóch innych, pojedynczo).

Tak więc tabela odwołująca się miała dwie kolumny klucza obcego, a także miała ograniczenie gwarantujące, że dokładnie jedna tabela (nie obie, nie żadna) była przywoływana przez pojedynczy wiersz.

Oto, jak może wyglądać po zastosowaniu do tabel:

CREATE TABLE dbo.[Group]
(
    ID int NOT NULL CONSTRAINT PK_Group PRIMARY KEY,
    Name varchar(50) NOT NULL
);

CREATE TABLE dbo.[User]
(
    ID int NOT NULL CONSTRAINT PK_User PRIMARY KEY,
    Name varchar(50) NOT NULL
);

CREATE TABLE dbo.Ticket
(
    ID int NOT NULL CONSTRAINT PK_Ticket PRIMARY KEY,
    OwnerGroup int NULL
      CONSTRAINT FK_Ticket_Group FOREIGN KEY REFERENCES dbo.[Group] (ID),
    OwnerUser int NULL
      CONSTRAINT FK_Ticket_User  FOREIGN KEY REFERENCES dbo.[User]  (ID),
    Subject varchar(50) NULL,
    CONSTRAINT CK_Ticket_GroupUser CHECK (
      CASE WHEN OwnerGroup IS NULL THEN 0 ELSE 1 END +
      CASE WHEN OwnerUser  IS NULL THEN 0 ELSE 1 END = 1
    )
);

Jak widać, Tickettabela ma dwie kolumny OwnerGroupi OwnerUserobie są kluczami obcymi dopuszczającymi wartość null. (Odpowiednie kolumny w pozostałych dwóch tabelach są odpowiednio tworzone jako klucze podstawowe.) Ograniczenie CK_Ticket_GroupUsersprawdzające zapewnia, że ​​tylko jedna z dwóch kolumn klucza obcego zawiera odwołanie (druga ma wartość NULL, dlatego obie muszą mieć wartość null).

(Włączony klucz główny Ticket.IDnie jest konieczny dla tej konkretnej implementacji, ale zdecydowanie nie zaszkodzi mieć go w takiej tabeli).


1
To jest również to, co mamy w naszym oprogramowaniu i unikałbym, jeśli próbujesz stworzyć ogólne ramy dostępu do danych. Ten projekt zwiększy złożoność warstwy aplikacji.
Frank

4
Jestem naprawdę nowy w SQL, więc popraw mnie, jeśli to jest złe, ale ten projekt wydaje się być podejściem do użycia, gdy jesteś bardzo pewien, że będziesz potrzebować tylko dwóch typów biletów właściciela. W dalszej części drogi, jeśli wprowadzono trzeci typ właściciela biletu, należałoby dodać trzecią kolumnę klucza obcego dopuszczającą wartość null do tabeli.
Shadoninja

@Shadoninja: Nie mylisz się. W rzeczywistości uważam, że jest to całkowicie uczciwy sposób ujęcia tego. Generalnie nie przeszkadza mi tego rodzaju rozwiązanie, które jest uzasadnione, ale z pewnością nie byłoby to pierwsze w mojej głowie, rozważając opcje - właśnie z powodu, który przedstawiłeś.
Andriy M

2
@ Frank.Germain W tym przypadku możesz użyć unikalnego klucza obcego opartego na dwóch kolumnach RefID, RefTypegdzie RefTypejest ustalonym identyfikatorem tabeli docelowej. Jeśli potrzebujesz integralności, możesz sprawdzić w wyzwalaczu lub warstwie aplikacji. W takim przypadku możliwe jest pobieranie ogólne. SQL powinien zezwalać na taką definicję FK, ułatwiając nam życie.
djmj

2

Jeszcze inną opcją jest umieszczenie w Ticketjednej kolumnie określającej typ jednostki będącej właścicielem ( Userlub Group), drugiej kolumny z odniesieniem UserlubGroup identyfikatorem identyfikatorem i NIE używać kluczy obcych, ale zamiast tego polegać na wyzwalaczu, aby wymusić integralność referencyjną.

Dwie zalety, które widzę tutaj w porównaniu z doskonałym modelem Nathana (powyżej):

  • Większa jasność i prostota.
  • Prostsze zapytania do napisania.

1
Ale to nie pozwoliłoby na klucz obcy, prawda? Nadal próbuję znaleźć właściwy projekt dla mojego obecnego projektu, w którym jedna tabela może odnosić się do co najmniej 3, może więcej w przyszłości
Can Rau

2

Innym podejściem jest utworzenie tabeli asocjacji zawierającej kolumny dla każdego potencjalnego typu zasobu. W twoim przykładzie każdy z dwóch istniejących typów właścicieli ma własną tabelę (co oznacza, że ​​masz coś do odniesienia). Jeśli tak będzie zawsze, możesz mieć coś takiego:

CREATE TABLE dbo.Group
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)  

CREATE TABLE dbo.User
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)

CREATE TABLE dbo.Ticket
(
    ID int NOT NULL,
    Owner_ID int NOT NULL,
    Subject varchar(50) NULL
)

CREATE TABLE dbo.Owner
(
    ID int NOT NULL,
    User_ID int NULL,
    Group_ID int NULL,
    {{AdditionalEntity_ID}} int NOT NULL
)

Dzięki temu rozwiązaniu można kontynuować dodawanie nowych kolumn podczas dodawania nowych jednostek do bazy danych, a także usuwać i odtwarzać wzorzec ograniczenia klucza obcego pokazany przez @Nathan Skerl. To rozwiązanie jest bardzo podobne do @Nathana Skerla, ale wygląda inaczej (w zależności od preferencji).

Jeśli nie zamierzasz mieć nowej tabeli dla każdego nowego typu właściciela, być może byłoby dobrze dołączyć kolumnę owner_type zamiast kolumny klucza obcego dla każdego potencjalnego właściciela:

CREATE TABLE dbo.Group
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)  

CREATE TABLE dbo.User
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)

CREATE TABLE dbo.Ticket
(
    ID int NOT NULL,
    Owner_ID int NOT NULL,
    Owner_Type string NOT NULL, -- In our example, this would be "User" or "Group"
    Subject varchar(50) NULL
)

Dzięki powyższej metodzie możesz dodać dowolną liczbę typów właścicieli. Owner_ID nie miałby ograniczenia klucza obcego, ale byłby używany jako odniesienie do innych tabel. Wadą jest to, że musiałbyś spojrzeć na tabelę, aby zobaczyć, jakie są typy właścicieli, ponieważ nie jest to od razu oczywiste na podstawie schematu. Sugerowałbym to tylko wtedy, gdy nie znasz wcześniej typów właścicieli i nie będą one łączyły się z innymi tabelami. Jeśli znasz wcześniej typy właściciela, wybrałbym rozwiązanie takie jak @Nathan Skerl.

Przepraszam, jeśli źle napisałem SQL, po prostu wrzuciłem to razem.


-4
CREATE TABLE dbo.OwnerType
(
    ID int NOT NULL,
    Name varchar(50) NULL
)

insert into OwnerType (Name) values ('User');
insert into OwnerType (Name) values ('Group');

Myślę, że byłby to najbardziej ogólny sposób przedstawienia tego, czego chcesz, zamiast używania flagi.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.