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.)
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.
- 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?
- 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.