How should I handle units of measure in an ingredient database?

Question

I'm just getting started learning SQL with MySQL/MariaDB, and I figured a good project would be to set up a database for nutrition and price information for my (actual) local pizza store. My overall concept:

1. One table for information on ingredient nutrition information.

2. One table for ingredient price information.

3. One table for recipes, indicating the amount of each ingredient in each pizza.

I have a couple problems:

1. Different ingredients and aspects of nutrition information typically use different units: milligrams of sodium per cup of salt, milligrams of sodium per fluid ounce of sauce, grams of protein per pound of flour, calories per pound of flour, etc. How can I handle this? The cleanest approach, in theory, would be to measure all nutrients except calories in milligrams, and all ingredients in kilograms, but then I'd need a table of densities (which are not always readily available) and some way to insulate the user from that mess. And calories would remain oddballs off to the side.

2. Two key aspects of pizza, dough and sauce, serve as "sub-recipes" for (pretty much) all kinds of pizza. What's the right way to handle this?

Answer 1

It's hard to give you and "answer" on this as the question is asking for opinion, however my opinion would be:

1. On your ingredient table hold a field for 'unit' type, you might then need another table for conversion ratios from one unit to another should you need to aggregate things for recipes.

----------------------------------------------
| id | item             | quantity  |  unit  |
-----------------------------------------------
  1    flour                 2          kg     
  2    eggs                  4          count
  3    paste                 3          g


============================
| unit1   | unit2  | convt  |
-----------------------------
    g        kg      0.01

I haven't done it here but you'd have a table of units and use their IDs in the conversion table. Some unit types, like count, will be impossible to convert and so conversion is not allowed on any unit not found in the conversion table. The same approach could be used for nutrition elements.

2. For recipes in recipes you need a set up that means a recipe can also be an ingredient so, assuming you have a table of recipes that points at a set of ingredients, it also needs to be able to refer to a recipe key (maybe unit could be used for this too). Maybe something like this:

  Recipes 
  -------------------------
  | id  |  name           |
  -------------------------
  | 1   |  dough          |
  | 2   |  sauce          |
  | 3   |  pizza          |


  Recipe Ingredients
  ---------------------------------------------------------
  | recipe | ingredient   | quantity  | unit   | key ref  |
  ---------------------------------------------------------
  | 1      |  flour       |    400    |  g     |  NULL    |
  | 1      |  egg         |     3     |  count |  NULL    |
  | 1      |  water       |     5     |  foz   |  NULL    |
  | 2      |  tomatoes    |     1     |  kg    |  NULL    |
  | 2      |  onions      |     3     |  count |  NULL    |
  | 3      |  dough       |     1     |  recipe|  1       |
  | 3      |  sauce       |     1     |  recipe|  2       |

Now your application code would need to catch the recipe entries and loop back round to grab their 'real' ingredients and keep doing that until there are no more recipe entries. You'd have to be careful to make sure your application guards against the possibility of an infinite loop, like a recipe referring to itself.

Some people would argue that I'm using the unit column here to hold data that is not a 'unit' (remember this would really just be IDs pointing to your units table) but I think that is a moot point and could probably argue that recipe is a valid 'unit', but hey.

I'm not suggesting this is the best design, just some ideas to get you started on the two points you raised.