Sql-server – Setting up a many-to-many relationship based off of two other many-to-many relationships

database-designerdsql serversql-server-2012sql-server-2016

I have 3 main top level entities:

Project (PK: Id)
Owner (PK: Id)
Product (PK: Id)

Here are the rules:

A Project can have many Owners. An Owner can have many Projects.
A Project can have many Products. A Product can have many Projects.

This is the part that is confusing me:

A ProjectOwner can have many ProjectProducts and a ProjectProduct can have many ProjectOwners.

Here is how I currently have it setup (w/ two additional tables besides the ones above):

ProjectOwner (PK: ProjectId, PK: OwnerId)
ProjectProductOwner (PK: ProjectId, PK: ProductId, PK: OwnerId)

I am trying to figure out if I am following best practice with this because something seems strange. For some reason I feel like I should have this broken out into 3 tables (ProjectOwner, ProjectProduct, ProjectOwnerProduct).

Just to give you an idea of how things will be saved in the application it will be as follows:

User creates a Project. User creates Owners and during this process they add them to the project. User adds multiple Products to the Project and associates them with Project Owners from the previous step.

Best Answer

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...

Related Question