How to design a food database?
Posted: Tue Aug 08, 2017 11:15 am
Hi,
im trying to design a small food database. I want to store info such as
product name (UNIQUE)
manufacturer
nutritional info
ingredients
Product name and maufacturer I can save as varchar.
For the nutritional info I thought of separate columns such as carb, fat, protein, energy (INT or float 4,2)
The thing is I do not know how to save ingredients in the best way.
First: I don't know how many ingredients there will be in different foods.. one might have 3 ingredients and another might have 40 ingredients...
Second: I want to be able to quickly run through the database and sort out all products that contain for example EGG.
I thought of saving the ingredients in a text file separated by some special char such as | so I can turn em into an array and compare that array with an array of stuff I want to sort out but I don't know if there is any better way of doing this?
Example ingredient(text):
egg|oil|milk|citric acid|fish ->Load into array1
ex filter(text)
egg|E331|apple ->Load into array2
compare array1 and array2 if they share some common value (in this case egg)
Any ideas how to best store the food in a database (so it will be fast to search even if there are many users)?
im trying to design a small food database. I want to store info such as
product name (UNIQUE)
manufacturer
nutritional info
ingredients
Product name and maufacturer I can save as varchar.
For the nutritional info I thought of separate columns such as carb, fat, protein, energy (INT or float 4,2)
The thing is I do not know how to save ingredients in the best way.
First: I don't know how many ingredients there will be in different foods.. one might have 3 ingredients and another might have 40 ingredients...
Second: I want to be able to quickly run through the database and sort out all products that contain for example EGG.
I thought of saving the ingredients in a text file separated by some special char such as | so I can turn em into an array and compare that array with an array of stuff I want to sort out but I don't know if there is any better way of doing this?
Example ingredient(text):
egg|oil|milk|citric acid|fish ->Load into array1
ex filter(text)
egg|E331|apple ->Load into array2
compare array1 and array2 if they share some common value (in this case egg)
Any ideas how to best store the food in a database (so it will be fast to search even if there are many users)?