It looks like you're almost where you want to be. I typically design those linking tables a bit differently, as well, because I don't like compound PKs and that may not be a "best practice" as much as a personal preference:
Primary Tables:
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
)
Linking Tables:
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)
)
And now let's put some data in...
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());
And now let's link these together.
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)
So now you can link a product up to a project and from there down to the owners or vice versa.
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
This gives us :
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
Similar queries can reverse that to show the owners and their products or other combinations. Some XML work can be used to turn this into comma-separated lists instead of more rows...
