I'm making a recipe-type app and while I am proficient in basic SQL (using Delphi/Firemonkey as my main IDE) I am a little stuck on the best way to proceed in setting up the structure of my database.
The best way to describe this is as a recipe app (it's actually something different but a recipe app would work exactly the same way). This is for the development of the recipes so it contains iterations as I test each formula and make tweaks as I go. Ultimately I then compound the formula into batches and iterations. If we look at it from the ground up we have this:
Product: Chocolate Cake
recipe version 1: formula containing the materials and quantities
recipe attempt 2: second formula
. . . (there is no limit to how many formulas I may create to find the final perfect version)
There is a small complication - recipes can also use other recipes as ingredients - so there might be some recursive linking. For example a formula for battenburg cake might include 100g flour, 3 eggs, 250ml milk, and 500g Chocolate Cake Formula.
Once I have the final version I like, I then need to produce batches of mixture (physically) and record that data also:
Chocolate Cake Mixture recipe attempt 2 (recipe 2 was the best one)
Batch 0001: made on december 1
Batch 0002: made on december 5
Each batch will have more info stored, like the name of the baker making the mixture, notes on the batch, etc. Each batch needs to store the same list of materials as the original recipe but will be the ACTUAL weight of the ingredients - so the product will have the recipe - say 100g flour, but the batch will the real weight that ended up in the mix - let's say 100.3g flour for batch 0001, and 100.04 for batch 0002. At this stage the batch data is just storing the weights used and any notes relating to each line item in the formula as I go. In my software I will be adding the weights here as I go (either manually or through an interface with my scales).
To clarify, the original recipe and the batch formulas are storing the same type of information but the recipe is the "formula" and the batch is the physical product.
Now - the job is not quite done yet. Once these batches are made I need to produce lots for sale - of baked cakes! I don't want to sell the uncooked mix I want to sell cakes. So now I need lots from each batch:
LOT 1: made on december 12
LOT 2: made on december 14
Lot 3: made on december 15
Again I need to keep notes and other linked data like the name of the cook once again (this will be used in each step as different cooks may perform different parts of the task). And, like the batches, the lots may include other materials - so it might include the batch, plus a cake tin size, plus a batch of icing (from a previous formula), plus some cherries, etc. Each line needs to keep other data like the batch number or notes for each added material.
I already have two databases in existence - one is Materials which keeps the name of the materials I use in the recipe.
This is being done in MySQL and the database is access via my FireMonkey created app on MacOS.
Each section needs extra data - the basic formula will need the records for the actual formula, but also extra data like the cook, the name, comments or notes, etc. and the batches and lots will need not just an unknown quantity of records for each ingredient and added weight, but will also need to store global info like the type of bowl the cake was mixed in, the weight of the bowl, the weight of the mix after mixing, the cook, etc.
So - to end, we have a cake with all the trimmings to sell - and a long series of formulas from the manifestation of the idea (chocolate cake) through the making of the raw batches and the ultimate baking and adding of trimmings to the batches (lots). What you buy in the shop will be labelled:
Chocolate Cake [Recipe 2, Batch 0003, Lot 0005]
Chocolate Recipe Iteration 2 -> Raw mixture 3 -> baked and packaged cake
I know this is very complex and I hope my question is not outside the scope of this site! I am generally quite an able developer but I don't typically dabble with databases in any complex way. And for the record, this app is for my own use to keep track of my baking - not for commercial sale.