Forum Discussion
Relationship between two tables
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
8 Replies
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.
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"
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?
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.
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?
