Avatar of Jamie Frater
Jamie Frater
 asked on

What is the best DB structure for my recipe-type app?

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:


Batch 0001 

   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]

AKA

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.

DelphiDatabases* data structuresMySQL Server

Avatar of undefined
Last Comment
Jamie Frater

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Kent Olsen

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jamie Frater

ASKER
That is perfect! Thank you so much Kent. Despite my years of programming I have never really dealt with complexities like this at the starting level - normalisation appears to be the main key here. I realise now that the most optimal option is (I think) to follow your method above and include ALL recipes in one big recipe table which can be queried via a view to extract the records relating to the particular recipe. A very clean approach. I think I was presuming that I shouldn't be mixing formulas together in one database but there is no reason not to that I can see.

Thank you!
Kent Olsen

You're very welcome.

The key is the normalization. It allows each recipe to have anywhere from 1 to 1,000 (or more) ingredients .  Another feature is that an ingredient is only spelled once in the database so there no risk of a typo "hiding" an ingredient some place.  It's trivial to find something like "all desserts that don't have sugar".



David Favor

Simple solution...

1) Setup a WordPress site.

2) Use a mobile response theme.

3) Look through the 100s of recipe plugins to find one matching your requirements.

4) If you must write your own code, then refer to item #3, then each time you install a plugin take a look at how they've setup their tables.

Likely you'll find design... approaches... which solve problems you haven't thought about before...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jamie Frater

ASKER
Thanks for the comment David - as it happens the "recipe" was a metaphor to describe something else as it is the closest type of thing I can think of to what I'm doing.
Kent Olsen

I'm kinda curious about your progress.  How's it going?
Jamie Frater

ASKER
it is going really well thank you Kent! The database structure as you suggested is working perfectly and it is nice and easy to work with.  Now I just have to battle the interface seeing as the last time I was programming I used the VCL and now need to learn to understand FMX well.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.