Nutritional Information Data Model

database-design

I'm trying to create a model for food and it's nutrition facts.

My criteria is:

  • A food can have multiple servings.
  • Multiple servings (of a food) can point to the same nutrition facts. (For example: if there are 3 serving sizes for a food, then 2 of them point to the same nutrition facts row, but the third serving size points to a different nutrition facts row.)

Additional information

My thinking why multiple servings can be be linked to the same nutrition facts: On a nutritional label, the serving size can be written like: 2 cookies (50g). I wanted to be able to separate the two sizes (cookies and grams), but have them connected to the same nutritional profile, should they need to be changed.

The second point is where I'm kind of struggling.

Working DB Model Solution
This is what I have so far, however, the serving_sizes table seems weird and is perhaps there is a better way. These are my concerns:

  1. I think I want the servings table to have an auto-incrementing ID column because I want to record in another table that I, for example, ate cookies with a serving size of 50 grams. I also realize that size serving_sizes ID would be unique since it is a PK, I could just record the serving size ID, as that uniquely identifies the relationship. (Perhaps that means something is incorrect?) Also, given that this is a many-to-many relationship, is an auto-incrementing ID correct here?
  2. How do I prevent the nutrition_facts_id in serving_sizes from being used by another food_id?

Note: I haven’t discussed this scenario with a domain expert since this is a personal project/exercise. However, I have spent a significant amount of time thinking and doing research about this domain.

Best Answer

If your intention is that you want to have multiple equivalent units of measure, all of which share the identical nutrition information, then you should flip things around and make nutrition_facts either a child of food directly. The serving_sizes would then be a child of your nutrition facts, not of food (except as a grand child through nutrition facts. Something like this:

food -+-----< nutrition_facts -+-----< serving_sizes

This would let you have multiple sets of nutrition facts, say for "box of cookies" and "two cookies" each stored only once, with these units of measure stored as either cookie units or equivalent grams.

If you only want to have one set of nutrition facts... such as would appear on a nutrition label, then you have a 1:1 between food and nutrition facts, which gives you the option of moving the columns from nutrition_facts into the food table as an alternative design. This may or may not appeal to you, depending on your design inklings.

Related Question