od zera lub jednego do zera lub jednego

Jak w najbardziej naturalny sposób modelować relację zero-jedynka do zera jeden lub jeden w Sql Server?

Istnieje tabela „Zagrożenia”, która zawiera listę zagrożeń w witrynie. Istnieje tabela „Zadań” do pracy, którą należy wykonać na stronie. Niektóre zadania mają naprawić zagrożenie, żadne zadanie nie może poradzić sobie z wieloma zagrożeniami. Niektóre zagrożenia mają za zadanie je naprawić. Żadnemu zagrożeniu nie można przypisać dwóch zadań.

Poniżej jest najlepsze, o czym mogłem myśleć:

CREATE TABLE [dbo].[Hazard](
  [HazardId] [int] IDENTITY(1,1) NOT NULL,
  [TaskId] [int] NULL,
  [Details] [varchar](max) NULL,
 CONSTRAINT [PK_Hazard] PRIMARY KEY CLUSTERED 
(
  [HazardId] ASC
))

GO

ALTER TABLE [dbo].[Hazard]  WITH CHECK ADD  CONSTRAINT [FK_Hazard_Task] FOREIGN KEY([TaskId])
REFERENCES [dbo].[Task] ([TaskId])
GO


CREATE TABLE [dbo].[Task](
  [TaskId] [int] IDENTITY(1,1) NOT NULL,
  [HazardId] [int] NULL,
  [Details] [varchar](max) NULL,
 CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED 
(
  [TaskId] ASC
))

GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Hazard] FOREIGN KEY([HazardId])
REFERENCES [dbo].[Hazard] ([HazardId])
GO

Czy zrobiłbyś to inaczej? Powodem, dla którego nie jestem zadowolony z tej konfiguracji jest to, że musi istnieć logika aplikacji, aby upewnić się, że zadania i zagrożenia wskazują na siebie nawzajem, a nie na inne zadania i zagrożenia, i że żadne zadanie / zagrożenie nie wskazuje na to samo zagrożenie / zadanie inne zadanie / zagrożenie wskazuje na.

Czy jest lepszy sposób?

Możesz pójść z własnym pomysłem na schemat asymetryczny, usuwając jeden z kluczy obcych z bieżącej konfiguracji, lub, aby zachować symetryczność, możesz usunąć oba klucze obce i wprowadzić tabelę połączeń z unikalnym ograniczeniem dla każdego odwołania .

Tak by wyglądało to tak:

CREATE TABLE dbo.Hazard
(
  HazardId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Hazard PRIMARY KEY CLUSTERED,
  Details varchar(max) NULL
);

CREATE TABLE dbo.Task
(
  TaskId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Task PRIMARY KEY CLUSTERED,
  Details varchar(max) NULL,
);

CREATE TABLE dbo.HazardTask
(
  HazardId int NOT NULL
    CONSTRAINT FK_HazardTask_Hazard FOREIGN KEY REFERENCES dbo.Hazard (HazardId)
    CONSTRAINT UQ_HazardTask_Hazard UNIQUE,
  TaskId int NOT NULL
    CONSTRAINT FK_HazardTask_Task FOREIGN KEY REFERENCES dbo.Task (TaskId)
    CONSTRAINT UQ_HazardTask_Task UNIQUE
);

Możesz dodatkowo zadeklarować, (HazardId, TaskId)że jesteś kluczem podstawowym, jeśli chcesz odwoływać się do tych kombinacji z innej tabeli. Jednak aby zachować unikalność par, klucz podstawowy jest niepotrzebny, wystarczy, aby każdy identyfikator był unikalny.

Podsumowując:

• Zagrożenia mają jedno lub zero zadań
• Zadania mają jedno lub zero zagrożeń

Jeśli tabele zadań i zagrożeń są używane do czegoś innego (tj. Zadania i / lub zagrożenia mają inne powiązane dane, a model, który nam pokazałeś, jest uproszczony, aby pokazać tylko odpowiednie pola), powiedziałbym, że twoje rozwiązanie jest poprawne.

W przeciwnym razie, jeśli Zadania i Zagrożenia istnieją tylko w celu ich połączenia, nie potrzebujesz dwóch tabel; możesz utworzyć jedną tabelę dla ich relacji, z następującymi polami:

ID            int, PK
TaskID        int, (filtered) unique index  
TaskDetails   varchar
HazardID      int, (filtered) unique index
HazardDetails varchar

Innym podejściem, które wydaje się jeszcze nie wspomniane, jest używanie zagrożeń i zadań w tej samej przestrzeni ID. Jeśli zagrożenie ma zadanie, będzie miało ten sam identyfikator. Jeśli zadanie dotyczy zagrożenia, będzie miało ten sam identyfikator.

Aby wypełnić te identyfikatory, należy użyć sekwencji zamiast kolumn tożsamości.

Kwerendy dotyczące tego typu modelu danych wykorzystywałyby (pełne) sprzężenia zewnętrzne do pobierania wyników.

To podejście jest bardzo podobne do odpowiedzi @ AndriyM, tyle że jego odpowiedź pozwala na różne identyfikatory i tabelę do przechowywania tej relacji.

Nie jestem pewien, czy chcesz zastosować to podejście do scenariusza z dwiema tabelami, ale działa ono dobrze, gdy wzrasta liczba zaangażowanych tabel.