PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Thu Aug 17, 2017 10:35 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
PostPosted: Tue Aug 08, 2017 11:15 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 107
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)?


Top
 Profile  
 
PostPosted: Tue Aug 08, 2017 4:44 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6483
Location: WA, USA
What database system?

If you're putting stuff into a database then don't put stuff into a file too. That defeats the whole purpose of using a database.

Rule of thumb: Once set up, a database structure shouldn't need to be altered in the normal course of using it. Obviously, it's okay to modify if you need to add or remove behavior.
Rule of thumb: Don't use multiple columns for data when you think you might need to add or remove them over time. If you have carbs, fat, protein, and calories now, then decide you want trans and sat fat separate, that means you'd have to alter the structures and that's not a good sign.

You need to figure out sort of data you're storing. If you don't know what nutritional info you want then you need to know what type of info it will be. For a start, you have "a name for the particular piece of information" (carbohydrates, fat, protein, etc.) and "the amount of it in the food" (eg, X mg or Y g). That is storable: use one table to track the sorts of information available and another to store values for each particular piece of food. Two tables means (a) you don't keep repeating names like "carb" all over and (b) you can use the first table as a list to work from when entering data, which probably won't be expanded often.

Same process for ingredients but even more obvious: you can't possibly have a column for each ingredient (what would you even store in it?) but you can have a table containing "name of ingredient". You could also have a second table with the names of ingredients, so the second is the food and which ingredients are contained, but that might not be worth it in this case since it would be expanded often.

Here's an example with a can of soup I have lying around.
Code:
products

id | name                                               | manufacturer | serving size | servings
---+----------------------------------------------------+--------------+--------------+---------
1  | Progresso Chicken Corn Chowder Flavored with Bacon | Progresso    | 1 cup (255g) | about 2

nutritionalinfo_list

id | name                | unit
---+---------------------+-----
1  | Calories            |
2  | Calories from Fat   |
3  | Total Fat           | g
4  | Sodium              | mg
5  | Total Carbohydrates | g
6  | Sugars              | g
7  | Protein             | g

nutritionalinfo

product | info | amount
--------+------+-------
      1 |    1 |  190.0
      1 |    2 |   70.0
      1 |    3 |    8.0
      1 |    4 |  870.0
      1 |    5 |   23.0
      1 |    6 |    5.0
      1 |    7 |    6.0

ingredients

product | name
--------+---------
      1 | chicken broth
      1 | corn
      1 | cooked white chicken meat
      1 | potatoes
      1 | celery
      1 | ...


But searching ingredients may not be as easy as you think. If you want to search for "egg" then what about "eggs"? What about "egg yolk"? And on the other hand, should a search for "chicken" also match "chicken broth"?


Top
 Profile  
 
PostPosted: Tue Aug 08, 2017 7:03 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13421
Location: New York, NY, US
I agree with requinix that a database with relations is the way to go. I am less concerned about adding columns that requinix is, so I would put all the nutritional info in the product record because there is a 1:1 relation between the info and the product. And since products and ingredients are all foods, you could put them all in one table for simplicity. I may simplify editing as well. The difference between products and ingredients is that products have a manufacturer. Ingredients probably need to be in some base serving size that can be multiplied by the amount in the products.

Then all you need a link table that give you the ingredients for each product.
Code:
foods

id | name                                          | manufacturer | serving size | servings | calories | fat | protein | sugars | ...
---+----------------------------------------------------+--------------+--------------+---------
1  | Progresso Chicken Corn Chowder  | Progresso   | 1 cup (255g)  | about 2 | ...
2  | Chicken broth                                 |               |  1g      |
3  | Corn                                          |               |  1g
4  | Cooked white chicken meat
5  | Potatoes
6  | Celery
7  | ...

food_ingredients

productID | ingredientID | amount
--------+------+-------
      1 |    2 |   70.0
      1 |    3 |    8.0
      1 |    4 |   870.0
      1 |    5 |   23.0
      1 |    6 |    5.0
      1 |    7 |    6.0

_________________
(#10850)


Top
 Profile  
 
PostPosted: Wed Aug 09, 2017 11:57 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 107
Thanks guys!

Requinix
I use Mysql (MariaDB) InnoDB

I already thought of searching ingredients and came up with that will probably be very hard so I worry about that later haha.

((I thought of having some ingredient database that contain only the singular form of the word (egg) so if you type eggs you will get a suggestion for egg, egg white or egg yolk to chose from or something like that..))


First I thought I'd go with Cristophers 'idea' to have more columns in the food table (fat, carb..) since that is unlikely to change (this is how i set it up now)..but the more i think of it the more i like the idea of having a table hold the names and one table for the values.. that way if I want to add sodium later on for example it will be pretty easy.

The "bad thing" is the DB will not be very readable for humans if something goes wrong heh

I also wonder what will happen with the performance over time if producers change ingredients so the info becomes defragmented.

Say I have 30 000 food products and the food ID=1 changed a couple of times over say 5 years:

Ingredient Table
(Table row) Product : Name
1 1 Oil
2 1 Pepper
3 1 Salt
4 2 Garlic
5 2 Milk
.
.
.
10000 987 Cheese
10000 1 Tuna
10000 988 Salt
.
.
670000 1 Tomato

Will it matter at all?

Ok I'm off to remodel my tables.. Thanks again :)


Top
 Profile  
 
PostPosted: Wed Aug 09, 2017 9:59 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6483
Location: WA, USA
With proper indexes it doesn't matter. Databases are more powerful than you give them credit for.


Top
 Profile  
 
PostPosted: Thu Aug 10, 2017 9:29 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 107
Cool thanks :)

See edit below______________________________________________
Another designquestion.. If I want to connect this database to an API (JSON) which would be the "best way" to handle insert, update and get requests.

I was thinking of making a product class with all variables I want to keep track of like energy, fat and so on and then in the class make functions like:

AddProduct(){
Adds the product to DB
}

GetProduct(){
Get product from DB
}
and so on..

The other idea is to put the code in the API as an endpoint
Endpoint Product
If Method == 'GET' {
Code to get product from DB
} else if Method == 'POST' {
Code to add product to DB
}


I know I need some API code even if I make product as a class but then it would look more like
If Method == 'GET' {
$p = new Product;
$p->GetProduct();
} else if Method == 'POST' {
$p = new Product;
$p->AddProduct();
}

What would you recommend and why?

EDIT: Well, I was eager to get going so I made a product class where I can hide all DB code not to clutter the API class too much.


Maybe my brain is too tired now but I cant figure out how to best store the nutritional info inside the class... (Ingredients for example I store as a simple array ([0]=>Milk [1]=>Salt [2]=>Corn syrup...)
I figure I need to store it in an array somehow but don't know how to code it in a good way to read from both tables...

from my product class where I store ingredients in the array:
Syntax: [ Download ] [ Hide ]
 if ($stmt = $mysqli->prepare("SELECT
                            Name
                            FROM API_Product_Ingredient_SWE where Product = ?"
)){
                                $stmt->bind_param('i', $this->id);
                                $stmt->execute();
                                $stmt->bind_result($name);
                                $stmt->store_result();
                                while($row = $stmt->fetch()){
                                   $this->ingredients[] = $name;
                                }
                            }
 


For the nutritional I guess I will have a similar code for the table that holds the nutritional amount for each product but how do i get the stuff from nutritional_list (the names (like fat, carbs and so on) and the unit (like gram or mg)) into the mix... Shall i read the nutritional list table from within the while {} loop? (my gut feeling is that it will be slow to do so).. is there a better way?

Thanks :)

/Edit__________________________________________________


Top
 Profile  
 
PostPosted: Thu Aug 17, 2017 9:45 am 
Offline
Forum Contributor

Joined: Wed Sep 25, 2013 4:09 am
Posts: 107
Ok,

after some trial and error, more error,even more error ...... and some more errors I came up with:

Syntax: [ Download ] [ Hide ]
if ($stmt = $mysqli->prepare("SELECT
                            API_Nutrition_List.Name, API_Nutrition_List.Unit, API_Nutrition.Amount
                            FROM API_Nutrition_List, API_Nutrition
                            WHERE API_Nutrition.Product = 1 AND API_Nutrition.List = API_Nutrition_List.id"
)){
                                //$stmt->bind_param('i', $this->id);
                                $stmt->execute();
                                $stmt->bind_result($name, $unit, $value);
                                $stmt->store_result();
                                while($row = $stmt->fetch()){
                                   $this->nutritionlist[$name] = $value.$unit;
                                }
 


the product = 1 is just for testing should be ?

It seems to work but is this the best way to do it?


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 7 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group