Triple many to many relationship

fabiobarreto10

Registered User.
Local time
Today, 18:41
Joined
Dec 28, 2011
Messages
45
Friends, I am having difficulty creating a relationship of tables in a database.
I have a table and another table technology company. For each technology has several companies,
and for each company have different technologies. A many to many.
I created a join table to resolve this issue, with two foreign keys.
I have another table Product, and for each company I have several products, and for each technology I also have
various products.
Any help is welcome.
Thank you.
 
It should end up like a triangle with the Company, Technology and Product tables at the points and the CompanyTechnology, CompanyProduct and TechnologyProduct tables along the edges, each joined to two points.
 
Are the relationships with product independent or dependent? For example do you have products related to companies and products related to technology or are products related only to specific technologies for specific companies. So, you have IBM, Microsoft, and Oracle and you have DB2, IMS, Access, SQL Server, and Oracle and finally you have Lolipops, Caramels, and JellyBeans.
Is the relationship -
IBM - DB2- Lolipops
IBM - DB2 - Caramels
IBM -IMS - Caramels
IBM - IMS - JellyBeans
or is it
IBM - Lolipops
IBM - Caramels
IBM - JellyBeans

and
DB2 - Lolipops
DB2 - Caramels
IMS - Caramels
IMS - JellyBeans
 
VilaRestal and Pat Hartman, thank you for responding and helping.


sorry VilaResta, I do not quite understand what you say quiz. I have to create a second table join? images of
relationships attached.

Thank you.



Now Hartman, I was a little confused by your explanation, but I think that maybe I just need to make a relationship
a table for many technology products for the table.

I think the first option .. IBM - DB2 - lolipops.

I'm confused, because I would do the record companies, technologies and products through cascading combobox.

Thank you.
 

Attachments

  • picture.jpg
    picture.jpg
    82 KB · Views: 203
What I was saying would look like this:
 

Attachments

  • picture2.jpg
    picture2.jpg
    47.7 KB · Views: 271
To make into Pat's first option:

at least remove TblLINKProduct_Company

And maybe remove TblLINKTechnology_Product and put a TechnologyID field in TblProduct and join that to TblTechnology (meaning each product can only be assigned to one technology)


The full loop of relationships is probably not what you want. You can establish a link from company to products through the technology table and its two link tables. Having another route is just more to maintain and could cause confusion.

If a one to many relationship from technology to product is adequate then do it that way as per picture4. Otherwise do it as per picture3
 

Attachments

  • picture3.jpg
    picture3.jpg
    41.9 KB · Views: 210
  • picture4.jpg
    picture4.jpg
    38.4 KB · Views: 176
Last edited:
Having said all that, in picture2 the table TblLINKProduct_Company could be renamed TblModel.

It depends whether TblProduct is a type of product (e.g. Operating System) - use picture2, 3 or 4 - or a particular product as manufactured by a particular company (e.g. Windows 7) - use picture5:
 

Attachments

  • picture5.jpg
    picture5.jpg
    31.2 KB · Views: 186
My first option would make the product relation dependent on the company/technology relation. So the junction table would need an autonumber PK (few do) and a second junction table would be created joining the first junction PK with product.

My second option was the triangle you showed in your first diagram.
 
VilaRestal and Pat Hartman, thanks for all the suggestions. I am studying how best option to solve my problem.

Thank you.
 
Solving the problem requires first understanding the relationships. Once you are clear about those, the problem solves itself:)
 
VilaRestal and Pat Hartman, I managed to solve the problem of relations, in fact, I only need a one to many relationship table
technology products for the table.
Thank you for helping.
 
Friends, I thought it would be the solution to my problem by putting a one to many relationship table technology
product to the table, but I have the following problem.

When I register a new product, I need to indicate in which technology, and also which company.
For example, if the company IBM has Facial Biometrics technology.
Microsoft and the company has also Facial Biometrics technology. I need to register a product in Biometrics Technology
Face, but only in the company IBM. According to my list and table fields product, when I joined
a new product for a particular technology, this product is for all companies that are technology related.
I was a little confused by the options that you sent me, Do you think the image 5 solves my problem?

Thanks for helping.
Fabio.
 
Yes, I think image 5 is probably what you want.
 
VilaRestal, just added the field "CompanyID" in the Product table. And I did a one to many relationship table
company to the table product. I think the logic is the same image that you sent me 5, but with more tables.
Thanks for helping.
 

Attachments

  • newrelation.jpg
    newrelation.jpg
    94.7 KB · Views: 159
OK good if you're happy with that. It seems logical.

I should point out though: Without TblLINKCompany_Technology you can still see all the technologies a company 'has' through TblProduct:

SELECT TblCompany.CompanyID, TblProduct.TechnologyID FROM TblCompany INNER JOIN TblProduct ON TblCompany.CompanyID = TblProduct.CompanyID

So TblLINKCompany_Technology may be duplicating your effort. It has the same fields that need entering that that query automatically has.

Worth thinking about if you haven't already.
 

Users who are viewing this thread

Back
Top Bottom