基於其他兩個多對多關係建立多對多關係

我有 3 個主要的頂級實體：

項目 (PK: Id)

所有者 (PK: Id)

產品 (PK: Id)

以下是規則：

一個項目可以有多個所有者。一個所有者可以擁有多個項目。

一個項目可以有許多產品。一個產品可以有多個項目。

這是讓我困惑的部分：

一個 ProjectOwner 可以有多個 ProjectProducts，一個 ProjectProduct 可以有多個 ProjectOwner。

這是我目前的設置方式（除了上面的表之外還有兩個額外的表）：

ProjectOwner（PK：ProjectId，PK：OwnerId）

ProjectProductOwner（PK：ProjectId，PK：ProductId，PK：OwnerId）

我試圖弄清楚我是否遵循最佳實踐，因為有些東西看起來很奇怪。出於某種原因，我覺得我應該把它分成 3 個表（ProjectOwner、ProjectProduct、ProjectOwnerProduct）。

只是為了讓您了解如何在應用程序中保存內容，如下所示：

使用者創建一個項目。使用者創建所有者，並在此過程中將其添加到項目中。使用者將多個產品添加到項目中，並將它們與上一步中的項目所有者相關聯。

看起來你幾乎是你想去的地方。我通常也會以不同的方式設計這些連結表，因為我不喜歡複合 PK，這可能不像個人偏好那樣是“最佳實踐”：

主表：

Create Table Projects (
   ProjectID int not null identity(1,1) primary key,
   ProjectName nvarchar(2000) not null,
   StartDate datetime2,
   FinishDate datetime2
   -- Other fields here
   )

Create Table People (
   PersonID  int not null identity(1,1) primary key,
   PersonName nvarchar(2000) not null,
   CreateDate Datetime2
   --other fields here
   )

Create Table Products (
   ProductID  int not null identity(1,1) primary key,
   ProductName nvarchar(2000) not null,
   CreateDate datetime2
   --other fields here
   )

連結表：

Create Table ProjectManagers (
   ProjectManagerID   int not null identity(1,1) primary key,
   ProjectID int not null,
   PersonID int not null, --this is the manager
   -- other fields here
   Constraint FK_ProjectManager_Project Foreign Key (ProjectID) References Projects (ProjectID),
   Constraint FK_ProjectManager_Owner Foreign Key (PersonID) References People (PersonID)
)

Create Table ProjectProducts (
   ProjectProductID int not null identity(1,1) primary key,
   ProjectID int not null,
   ProductID int not null,
   -- other fields here
   Constraint FK_PRojectProduct_Project Foreign Key (ProjectID) References Projects (ProjectID),
   Constraint FK_PRojectProduct_Product Foreign Key (ProductID) References Products (ProductID)
)

Create Table ProductOwners (
   ProductOwnerID int not null identity(1,1) primary key,
   ProductID int not null,
   PersonID int not null,  --the owner
   -- other fields...
   Constraint FK_ProductOwner_Owner Foreign Key (PersonID) References People (PersonID)
   )

現在讓我們將一些數據放入…

Insert into projects (ProjectName, StartDate) values ('Project 1', SYSDATETIME());
Insert into projects (ProjectName, StartDate) values ('Project 2', SYSDATETIME());
Insert into projects (ProjectName, StartDate) values ('Project 3', SYSDATETIME());

insert into People (PersonName, CreateDate) values ('Alan Alda', SYSDATETIME());
insert into People (PersonName, CreateDate) values ('Ben Bova', SYSDATETIME());
insert into People (PersonName, CreateDate) values ('Claire Collins', SYSDATETIME());

Insert into Products (ProductName, CreateDate) values ('1 product', SYSDATETIME());
Insert into Products (ProductName, CreateDate) values ('2 product', SYSDATETIME());
Insert into Products (ProductName, CreateDate) values ('3 product', SYSDATETIME());
Insert into Products (ProductName, CreateDate) values ('4 product', SYSDATETIME());

現在讓我們將這些連結在一起。

Insert Into ProjectManagers (ProjectID, PersonID) values (1, 1);
Insert Into ProjectManagers (ProjectID, PersonID) values (1, 2);
Insert Into ProjectManagers (ProjectID, PersonID) values (2, 1);
Insert Into ProjectManagers (ProjectID, PersonID) values (3, 3);

Insert into projectproducts (projectid, productid) values (1, 1)
Insert into projectproducts (projectid, productid) values (2, 1)
Insert into projectproducts (projectid, productid) values (3, 1)
Insert into projectproducts (projectid, productid) values (2, 2)
Insert into projectproducts (projectid, productid) values (3, 3)
Insert into projectproducts (projectid, productid) values (1, 4)
Insert into projectproducts (projectid, productid) values (2, 4)
Insert into projectproducts (projectid, productid) values (3, 4)

Insert Into ProductOwners (ProductID, PersonID) values (1, 1)
Insert Into ProductOwners (ProductID, PersonID) values (1, 3)
Insert Into ProductOwners (ProductID, PersonID) values (3, 1)
Insert Into ProductOwners (ProductID, PersonID) values (1, 2)

因此，現在您可以將產品連結到項目，然後再連結到所有者，反之亦然。

select proj.ProjectName, peMgr.PersonName [Proj Manager], prod.ProductName, peOwn.PersonName [Product Owner]
from projects proj
left join ProjectManagers pm on pm.ProjectID = proj.ProjectID
left join ProjectProducts pp on pp.ProjectID = proj.ProjectID
left join people peMgr on peMgr.personID = pm.personID
left join Products prod on  prod.ProductID = pp.ProductID
left join ProductOwners po on po.ProductID = prod.ProductID
left join people peOwn on peOwn.PersonID = po.PersonID
order by 1, 2, 3

這給了我們：

ProjectName |  Proj Manager |  Product      |   Product Owner
Project 1   |   Alan Alda   |   1 product   |   Alan Alda
Project 1   |   Alan Alda   |   1 product   |   Alan Alda
Project 1   |   Alan Alda   |   1 product   |   Ben Bova
Project 1   |   Alan Alda   |   1 product   |   Ben Bova
Project 1   |   Alan Alda   |   1 product   |   Claire Collins
Project 1   |   Alan Alda   |   1 product   |   Claire Collins
Project 1   |   Ben Bova   |   1 product   |   Alan Alda
Project 1   |   Ben Bova   |   1 product   |   Alan Alda
Project 1   |   Ben Bova   |   1 product   |   Ben Bova
Project 1   |   Ben Bova   |   1 product   |   Ben Bova
Project 1   |   Ben Bova   |   1 product   |   Claire Collins
Project 1   |   Ben Bova   |   1 product   |   Claire Collins
Project 2   |   Alan Alda   |   2 product   |   NULL
Project 2   |   Alan Alda   |   2 product   |   NULL
Project 2   |   Alan Alda   |   2 product   |   NULL
Project 3   |   Claire Collins   |   3 product   |   Alan Alda
Project 3   |   Claire Collins   |   3 product   |   Alan Alda
Project 3   |   Claire Collins   |   3 product   |   Alan Alda

類似的查詢可以反轉它以顯示所有者及其產品或其他組合。一些 XML 工作可用於將其轉換為逗號分隔的列表，而不是更多的行……

引用自：https://dba.stackexchange.com/questions/229759