Link to home
Start Free TrialLog in
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]


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.

Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jamie Frater
Jamie Frater


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!
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".

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...
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.
I'm kinda curious about your progress.  How's it going?
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.