With this approach where are we specifying which Owners manage which Projects? It looks like it just handles the two initial many-to-many relationships with join tables.

The ProjectOwner table shows who is connected to which projects. I don't see what's missing? If you need to mark one of the owners as the manager, then add a 3rd column to ProjectOwner {IsManager bit default 0}.

It wouldn't be marking as manager. It is more about associating the ProjectOwner with a ProjectProduct. Many ProjectOwners can manage many ProjectProducts, Many ProjectProducts can have many ProjectOwners. So it is a third many-to-many that needs to be handled but with all 3 keys based on the example in the comment MDCCL left above. Please see the part under "This is the part that is confusing me" in my post.

Does my re-write explain better?

I read through your entire post. I still don't see where the association between the Owners and Products is being stored within the Project. Where are you saying this Owner is associated with this Product? Here is the result I would expect: Project 1 > Product 1 > Owner1, Owner2. Project 2 > Product 1 > Owner1, Owner2, Owner3.

I don't see where there needs to be an explicit declaration linking the product to the owner, because they are linked via the projects that both are associated with.

Hey sorry, if you have a few minutes. I would like to discuss in here.

First off I really appreciate your in depth post.

Owners get added to a Project

Products get added to a Project

Sure. I showed a query that says "for each product, give me all of the owners..." that same query can be reversed to say "for an owner, give me all the products" So I guess I'm not understanding what's missing?

Right, with you so far.

Within that Project (Project1), ProjectOwners get associated with ProjectProducts.

The project is what links the owners with the products.

Let me look at it again, I just don't see how its possible for some reason because no where in your table structure do you have a table that has ProjectId, OwnerId, ProductId

You have to traverse several tables to get there (projects, products, and owners, plus the two linking tables between projects and the other two tables) but that forms your links.

That is an assumption that can't be made.

You don't need that link.

Let me give you a few scenarios

Project 1 (has three owners and 2 products).

Within project 1, product 1, has only a single ProjectOwner - Owner1.

With project 1, product 2, has three ProjectOwners - Owner1, Owner2, Owner3

I just don't see how with your table setup you would be able to get me

Project 1 > Product 1 > Owner 1

Project 1 > Product 2 > Owner 1 Owner 2 Owner 3

I feel like you are making the assumption that once an owner is added to a project they are part of all products

AH, okay. THAT's the part I didn't grasp from your original post... That WITHIN a project, the products may not be linked to all of the owners associated with that project.

I see now what you're getting at.

So I believe there just needs to be a third table called ProjectOwnerProducts with 3 keys

This would be able to give me full flexibility to retrieve any information I need...

I believe with the way you like to do it the third table would be something like this (Id, ProjectOwnerId, ProjectProductId)

Since you give your join tables ids

Have you ever used Entity Framework?

No, haven't used Entity Framework.

Alright because I am getting the following error and its killing me but maybe it is because I am not doing this 3rd join table correctly.

Introducing FOREIGN KEY constraint 'FK_ProjectOwnerProducts_ProjectProducts_ProjectId_ProductId' on table 'ProjectOwnerProducts' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

give me a minute, I'm trying to rework my example code...

If you don't mind check out the ERD here:

See the diagram under Logical data model

In this case they are giving the 3rd join table the label of "Response"

As you can see 3 top level entities, goes to 2 join tables, goes to a third join table at the bottom.

I believe my scenario is very similar.

Okay, I just rewrote my post. See if that's where you want to get to?

Alright will check it out

Perfect, that is inline with exactly how I am doing it.