Manufactuing relationship to sales

[ModelFear]

Hi community, 

I follow the star schema where sales is a fact table.
1. Now I have datasets of a manufacturing enterprises. How do I connect raw material, recipe, and production datasets into the star schema sales model?

For example, if I have a star schema of restaurant sales data, what is the best practice to build relationship to restaurant sales data ? Thank you a million for any help. I know i need to stay away from many to many relationship. Tks Lin

I tried so hard to include the table and spent time creating table using the icon above, but i keep getting invalid html message. So here is a image: 

Sales table (assume dish is sold by kg) 

[v-junyant-msft]

Hi @ModelFear ,

Have you solved your problem?
If not, I think if you want to avoid many-to-many relationships wherever possible, you can add a new table like this:

Or merge the entries in this table that have the same Dish ID and Dish Name into one row:

Otherwise you can barely avoid having a many-to-many relationship.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ModelFear2]

Hi v-junyant-msft
I created a bridge table after watching youtube video (seriously a very poor bi beginner here). Now i am trying to calculate margin (selling price - subtotal/ selling price). But I got an error message when i tried to calculate margin using either the Sales table or Recipe table below. Thank you for any advice. 

[PijushRoy]

Hi @ModelFear 

Please try below model
Receipe Table - Production Table - use DishID in relation - Many to Many relation - Receipe Table filter Production Table
Receipe Table - Raw Material Table - use Ingredient ID in relation - Many to one relation - Receipe Table filter Raw Material Table
Receipe Table - Sales Table - use Dish ID in relation - Many to Many relation - Receipe Table filter Sales Table

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[ModelFear]

Thank you PijushRoy, 

1. "Receipe Table - Raw Material Table - use Ingredient ID in relation - Many to one relation - Receipe Table filter Raw Material Table" - I think you meant Raw material filters Recipe. 

Kindly see below image

2. My next question is - I kept reading or seeing in videos that many to many relationship is not advisable. The model engages this. What is the danger of it? 😮  . Thank you in advance!