This is the scenario, I have a table batches linked to recipes linked to recipe data linked to ingredients:
Batch >-- Recipe --< Recipe Data >--- Ingredients
A batch makes multiple quantities of a recipe, so the qty of ingredient in the Recipe data has to be multiplied for the batch and relates to that batch. Right now that calc is done as Recipe Data::BatchQty = Batch::Multiplier * Recipe Data::IngredientQty.
That is the wrong way to do it because the BatchQty changes with every new batch and reflects back to other batches. Suggestions?
You'll need an additional table linked to Batch, but generated from Recipe Data via a script each time you create a new Batch Record. The records in this table can refer to the quantity in Batch in order to compute the batch based quantitites.