I’m building a tool that will allow you to find foods with certain characteristics. What is the best way to design the database?
I want to be able to display a page with a list of foods high in one of the values below (e.g. protein) AND individual food pages with their corresponding macro/micronutrient values, etc.
For example, for each food, I will include the following (values/info is made up):
FOOD: Brown Rice
Calories & Macros
Calories (Cal)
Carbs (g)
Fat (g)
Protein (g)
Allergens
Soy
Lipids (Fats)
Trans Fat (g)
Sat Fat (g)
Monounsaturated Fat (g)
Polyunsaturated Fat (g)
Cholesterol (mg)
You can create a table Food and another one for macro/micronutrient ( and include a field “type” for each type of macro/micronutrient).
Or you can create a specific table for each macro/micronutrient.
Thanks @Bubbleboy. I wanted to just create a MVP first with Foods uploaded or manually added and then go down the API route. Either way, I need to get the database structure and relationships correct…which I’m struggling with as a bit of a newbie. Any help on that is greatly appreciated.
My humble opinion is you actually might be over thinking this… why create a thing “food” and then create a thing "nutrient”. Then assign the nutrient thing to the food thing (over complicated. Just create a thing which is food and then have all the fields relating to its Nutrient. The categories such as Calories & MacrosLipids (Fats)CarbohydratesMinerals don’t need to be declared in the DB just how you wish to display ion the front end. All foods are made of the same nutrients just in different amaounts. If you were create a quiz and each quiz had different questions then you would create a Quiz think and then a question thing…assigning the list of questions to a quiz…hope that makes sense…
OK @Bubbleboy, for some reason I felt (perhaps still feel) like I needed to have two types. Here’s where I’m trying to wrap my head around…
For example, if I have a “food” and it’s corresponding nutrient values (6g protein, 100 calories, 40mg vitamin c, etc). I’m running into a problem where I want to display on SPECIFIC food’s page (e.g. Brown Rice) in a repeating group it’s top 3 nutrients.
That would change depending on the food and I’d need some way to identify out of the various different fields WHICH are nutrients and which values are highest. How would you suggest I do that?
Thank you so much again for your help!
–
@ualdir - that could work, although I need to maitain a relationship between those field lists something like a “Recommended Daily Value”…e.g. Brown Rice has a field or protein and the value is 6 (g) and the “Recommended Daily Value” is actually 10g. There needs to be a relationship there and I’m not sure a list field type allows that?
Hey @nfish - thanks for commenting. I guess what I was trying to say above, is I don’t have a clue how to acheive the list of top 3 nutrients of a given food with a RG. Any pointers? Thanks!
OK I see, but first you need to define your conditions. so how will the top 3 be determined by weight or will you store percentage (which could be worked out automatically?
OK then you need to change your structure then if Nutrients need to have their own fields.
I would create a ‘Nutrients’ Option set, this is a list of ‘nutrient’ options along with their relevant attributes, be it RDA and any other info you need to store against them (perhaps allergens etc)
Then your Nutrients table will become a many to many table which will store the ‘FOODS’ ‘NUTRIENTS’ along with the relevant stored values. So your FOOD will need a list of NUTRIENTS field added.
For data input, simply display a repeating group of all of the Nutrient options along with a text input that will be used when you create you ‘FOOD’
@nfish - OK, firstly, thanks again for your help and patience.
Can you quickly explain why given my last question that means I should switch from a flat (one table/data type) to a “many to many” approach? Just trying to learn.
Also, do I have the correct fields here? in the screenshots?
And I’ve also provided a link and access to my app below. You should be able to see more clearly what I’m trying to do with the “3 Top Nutrients” section on the “food” page. I just can’t figure out how to display the RG with the top nutrients for the current page’s food.
P.S. When someone clicks on a food from the “nutrients” page, it passes it on to the “food” page.
The main reason is that you need the data to be relational, keeping it flat is fine if you only ever want a field per nutrient, but you want to store fields on nutrients too, so they need their own table too. Many ‘Foods’ will have many ‘Nutrients’. It may seem a little more complex, but trust me, you will regret not doing it and only end up having to redo it further down the line. Your example of adding an RDA, illustrates this, you add an RDA field to the ‘Nutrients’ table once, not 20 (or how ever many there are) ‘Foods Nutrients’ fields and then have to populate them. That’s how a spreadsheet works, not a relational database, and it opens the door to do so much more.
Anyway, it looks like you have created a ‘Nutrients’ table which is great, the only reason I recommended option sets is that they are great for fixed lists of options (with attributes) that don’t need to be edited at the application level, such as category’s or types, but a table might be the best way to go now I’ve seen the fields you want to store. The app look great by the way, but I can see the data structure is a bit out. What you want to do is put all of your ‘Nutrients’ fields together (Nutrient Name, RDA, URL, Image, Description, category, etc) and then create the records by building a Nutrients data entry page. Then the nutrients will need to be added against the Foods.
I hope that is enough info for now, let me know if you get stuck.
OK I have set up a new ‘Nutrients’ table, and got the top 3 nutrients search working for you. I would suggest that you move forwards in this way, as it is the traditional approach, and any categorys should use option sets as they don’t really need to change day to day, and can be administered from the back end.
I am happy to help out new bubblers who are trying to get the best out of their app, however I do usually charge for doing it for them as I am building a few apps at the moment, however it was probably easier to just get it working on this occasion.
