Relationship between two tables

[Medmbchr]

Hi

I would like to know in your opinion how can I build a relationship between the following two tables to avoid a many-to-many relationship:

The idea is that I have 4 distinct products, however a pack is constituted by a main product and its accessories, meaning if my main product goes out by 2, all other accessories need to be multiplied by 2 times the quantity needed in the pack itself. Also, I have a list of how much each client need in terms of number of packs.

Any idea how to do this? Also the quantity needed column need to be calculated dynamically

Here is a simple case to illustrate: M123 is a mobile phone, the accessories are for example: usb cable, crate, earbuds, charger, user manual x 2 (english and french). Hence: Pack 1 is a whole mobile package and each of my 2 clients have 2 distinct needs for the pack

[vanessafvg]

so if customer orders product m123 x 3, then automatically product m123 with it standard accessories are x 3, 

you need a uniquekey for each combination of product and accessory.

for example 

you could take the combination of main product and accessory together that make a pack in a table  and assign  a unique id to each unique combination then under client you could use id that rather than the main product to join on

just depends on how far you want to go to model this data. 

[Medmbchr]

Hi @vanessafvg 

I re-read your first answer, I could use a unique ID for each combination, however I can't use it in my customer table since I don't have two columns "Main product" and "accessory" like my product table, I have only one column gathering them all.

[vanessafvg]

there are several ways to tackle this but  it would be easy to do it with sample data, if you could share some sample data in text form, i could pull something together in a power bi model to show you how you could do it.   preferably if you are able to share your file that would be the most ideal.  

what i am thinking is potentially you could create, as you have a bridge table and in that bridge table you would have a new column which could be an id that groups products together and then the column of the actual product code.  howvever its much easier to assess this when i can actually see the data in your model.

also how do you currently know what products are grouped together?

[Medmbchr]

Hi

The first table is my actual combination, this is just a sample to help out understand the problem without sharing customer data.

The bridge entity that I made is just an idea, but not part of the model itself, it's a list of all distinct products.

One other way to look at that first table is to gather all products in the same column and add a new column identifying either main product or accessory.

[Medmbchr]

Hi

I can't use a combination since each accessory is unique on the customer table, while my main product is also in that column where my accessories are since the main product and accessories are "products" 

[vanessafvg]

the only way you can get around a many to many relationship is to create a unique combination of the products required, otherwise how will you know what other products it is referring to?

[Medmbchr]

I didn't quite get your point, but in the meantime i created a bridge entity between the two table with a list of all unique accessories that link both table

[vanessafvg]

that makes sense to do, as long as you know which products are being used in that particular order.   your question was that you wanted to take the main product and then multiple the same number to the accessories with that product.    so has your solution solved this issue?