How to go through 5 related tables.

[SilverReed]

Dear All,

I have a very interesting issue with the following database structure:

1. Production Plan, key: planID
   
2. Plan details, keys: planID, productID
   
3. Product, key: productID, recipeID
   
4. Recipe, key: recipeID
   
5. Recipe details, key: recipeID, materialID
   
6. Material, productID
   

Material is a TO of Product

The following relation is set:

Production Plan (planID) >---< (planID) Plan details (materialID) >---< (materialID) Product (recipeID) >---< (recipeID) Recipe (recipeID) >---< (recipeID) Recipe details (productID) >---< (productID) Material

The fileds I use are as follow:

Production Plan: month

Plan details: month, product, qty

Product: name, price, netto weight

Recipe: name

Recipe: material, qtyPer1000kg

Material: name, price

OK lets define, I have:

Products:

1. Orange drink
   
2. Forest drink
   

Recipes:

1. Orange drink:
   
   • Water 950
     
   • Sugar 30
     
   • Aroma orange 20
     

   [*]forest drink:

   • Water 920
     
   • Sugar 50
     
   • Aroma forest 30
     

Plan:

Month: 10

Plan details:

• Orange drink 10kg
  
• Forest drink 20 kg
  

And finally I would like to get an report of raw materials use:

Month: 10

No Material name Qty

1 Water 27,9 (calc: 950/1000*10 + 920/1000*20)

2 Sugar 1,3 (calc: 30/1000*10 + 50/1000*20)

3 Aroma forest 30 (...)

4 Aroma orange 30 (...)

And the first question is:

1. Is it possible
   
2. May I use the structure I have, or I need to make a new tables?
   

Any help I would like to thank in advance

With Regards

Luca

[comment]

Sounds very similar to:

http://fmforums.com/forum/topic/61550-perpetual-inventory-extracting-totals-of-goodsingredients-used/page__view__findpost__p__291835

[SilverReed]

I tried to make it as in example but I have very strange effects.

The problem is I can not match Plan line ID with materialID

So I should have (the source for the report is by ingredient):

planID-----Banana Drink ------ Water 50

planID-----Banana Drink ------ banana aroma 30

planID-----Banana Drink ------ sugar 20

but I get

planID-----Banana Drink ------ Water 50

planID-----Banana Drink ------ banana aroma 30

planID-----Banana Drink ------ sugar 20

? ---------?????-------flavour 30

----------?????-------milk 1200

I just want to say it is taking all lines from recipe details, not only the lines connected with plan :(

[comment]

The problem is I can not match Plan line ID with materialID

AFAICT, your case is simpler than the one in the other thread: in addition to the core TOG:

Sales -< LineItems >- Products -< Quantities >- Materials

you need only a global gSaleID field in the Quantities table, and the following relationship:

Quantities::gSaleID = LineItems 2::SaleID

AND

Quantities::ProductID = LineItems 2::ProductID

---

P.S. I don't see why you have separate tables for products and recipes.

Edited November 9, 2011 by comment