Discussion between Blake Rivell and CaM

Feeds

16:11

A: Setting up a many-to-many relationship based off of two other many-to-many relationships

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: Create Table Projects ( ProjectID int not null identity(1,1) primary...

Blake Rivell

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.

CaM

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

Blake Rivell

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.

CaM

Does my re-write explain better?

Blake Rivell

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.

CaM

16:11

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.

Blake Rivell

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

CaM

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.

Blake Rivell

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

CaM

The project is what links the owners with the products.

Blake Rivell

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

CaM

16:13

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.

Blake Rivell

That is an assumption that can't be made.

CaM

You don't need that link.

Blake Rivell

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

CaM

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.

Blake Rivell

Correct!!!

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?

CaM

16:17

Ok. So a Project has Owner(s).
A Product has Project(s).

We need a different title/label/something for the people and their role. Do people own projects? Do they own products? Do they manage projects and own products?

That will determine what we call this link.

No, haven't used Entity Framework.

Blake Rivell

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.

CaM

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

Blake Rivell

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

A: Best data modelling approach to handle redundant foreign keys in relational model

As per my understanding of your specifications, your scenario involves a ternary relationship. In this regard, you need to define (a) the relationship between a Person and a Survey, (b) the relationship between a Survey and a Question, and (c) the entity type that produces a connection between th...