creating new child records without a parent record
I have finally got my winemaking database singing sweetly except for one issue that I need to resolve.
I have attached my workflow below - a vineyard has many patches/varieties of grapes, and this makes many components (multiple harvests per year, multiple years). There is an indexing table to link many components into many blends and many blends to many components. The components are blended into a wine blend through a portal which has a drop down value list of available components determined by calculation. If I then bottle that blend the database performs well.
Sometimes, however, a blend may itself be re-blended (either in part or in whole) i.e. looped back to become a component. I have written a script to take blend information and write that into a component record for re-selecting as a component through the drop down value list. This type of component record has no parent patch/vineyard, and does not appear in the value list for selection despite the calculation working correctly. I have managed to get the value list to work correctly if I assign this re-blended component to a "dummy vineyard and patch" to provide it with a parent. However this then leaves me with clunky issues of having a vineyard that I don't want. To complicate matters, for legal reasons I have to carry through all the original blend details into the re-blended component.
(Example: 2012 shiraz from vineyard a is blended with 2012 shiraz vineyard b (AB). Half of this blend is then removed and blended with 2012 cabernet from vineyard c & d, and the other half of the shiraz blend goes to bottle. Eventually Shiraz AB Cabernet CD is itself blended with Merlot from Vineyard A to make wine SCM ABCD. I have to legally report % variety, % vineyard and % vintage of AB that is bottled and SCM ABCD that is bottled.)
Any advice on how I should address this would be very much appreciated.